Each formula is valuable for addressing specific financial scenarios in Excel's financial functions. Among these, the PRICEDISC function stands out as a precision-driven mechanism, offering insights into discounted securities. As we delve into Excel Financial Functions, this article briefly introduces the PRICEDISC function from the outset. Understanding its intricacies and applications will broaden your financial toolkit and empower you to navigate the complexities of discounted pricing with finesse. Join us on this journey as we shine a spotlight on the Excel Financial Functions landscape, particularly emphasizing the powerful capabilities embodied by the PRICEDISC function.
PRICEDISC Function
The PRICEDISC function in Excel is a financial function designed to calculate a discounted security price. Specifically, it determines the price per $100 face value of a discounted security, where the discount is expressed annually. This function is particularly useful for financial analysts, investors, and professionals involved in fixed-income securities.
Versions supporting PRICEDISC Function
The PRICEDISC function is consistently supported across all versions of Microsoft Excel, from older versions like Excel 2003 to more recent ones like Excel 365. Users can confidently utilize this function to calculate the price of discounted securities by inputting key parameters. Excel's commitment to backward compatibility ensures smooth transitions between versions, allowing for the seamless use of PRICEDISC formulas created in earlier iterations. Regular updates may introduce improvements, emphasizing the function's reliability and ongoing support across Excel versions.
PREDISC Function's Syntax
- Settlement: This refers to the settlement date of the security. The settlement date refers to the date on which a financial transaction involving the security is completed.
- Maturity: The maturity date is when the security reaches the end of its term.
- Discount: The discount rate is the rate used to calculate the discounted price of the security.
- Redemption: Redemption represents the value per $100 face value of the security at maturity.
- [Basis:] This is an optional parameter that specifies the daily basis used for the calculations. If not specified, Excel defaults to a basis of 0 (zero), which is the current basis for calculations.
Arguments of the PREDISC Function
The PRICEDISC function in Excel requires several parameters for accurate calculation:
| settlement | The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. |
| maturity | The security's maturity date. The maturity date is the date when the security expires. |
| discount | The security's discount rate. |
| redemption | The security's redemption value per $100 face value. |
| [basis] |
Optional. The type of day count basis to use.
|
PREDISC Function Usage
The following example of PREDISC function shows how to calculate the price per $100 face value of a discounted security purchased on April 31st, 2019, with a maturity date of February 2nd, 2024, and a discounted rate of 5%. The redemption value is $100, and the payments will be made based on the US (NASD) 30/360 day count.
Tips
- Microsoft recommends using the DATE or other functions which can return a date serial number.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
- Please see Day count convention article in Wikipedia for more information about the [basis] types.
- Settlement, maturity, and basis are truncated to integers.
- Other price-related functions to PREDISC function:
- PRICE returns the price per $100 face value of a security that pays periodic interest.
- PRICEMAT returns the price per $100 face value of a security that pays interest at maturity.
- YIELD returns the yield on a security that pays periodic interest.
- DURATION returns the Macauley duration for an assumed par value of $100.
Common Issues
- If settlement or maturity are not valid dates, the PRICEDISC function returns the #VALUE! error value.
- If discount ≤ 0, PRICEDISC returns the #NUM! error value.
- The PRICEDISC returns the #NUM! error value if redemption ≤ 0.
- If [basis] < 0 or if [basis] > 4, PRICEDISC returns the #NUM! error value.
- If settlement ≥ maturity, PRICEDISC returns the #NUM! error value.
