In this comprehensive guide, we will explore the PRICEDISC function in Excel, which is used to calculate the price per $100 face value of a discounted security. This function is particularly useful for financial analysts and investors who deal with bonds and other fixed-income securities. We will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae for the PRICEDISC function.
PRICEDISC Syntax
The PRICEDISC function has the following syntax:
=PRICEDISC(settlement, maturity, discount, redemption, basis)
Where:
- settlement is the settlement date, which is the date when the security is purchased.
- maturity is the maturity date, which is the date when the security expires.
- discount is the discount rate of the security.
- redemption is the redemption value per $100 face value of the security.
- basis (optional) is the day count basis to be used. If omitted, it defaults to 0, which represents the US (NASD) 30/360 day count basis. Other available options are: 1 for actual/actual, 2 for actual/360, 3 for actual/365, and 4 for European 30/360.
PRICEDISC Examples
Let’s look at some examples of using the PRICEDISC function in Excel:
Example 1: Calculate the price per $100 face value of a discounted security with a settlement date of January 1, 2020, a maturity date of December 31, 2020, a discount rate of 5%, and a redemption value of $100.
=PRICEDISC(“2020-01-01”, “2020-12-31”, 0.05, 100)
This formula will return the price per $100 face value of the security, which is approximately $95.14.
Example 2: Calculate the price per $100 face value of a discounted security with a settlement date of January 1, 2020, a maturity date of December 31, 2020, a discount rate of 5%, a redemption value of $100, and using the actual/actual day count basis.
=PRICEDISC(“2020-01-01”, “2020-12-31”, 0.05, 100, 1)
This formula will return the price per $100 face value of the security, which is approximately $95.08, using the actual/actual day count basis.
PRICEDISC Tips & Tricks
- Ensure that the settlement and maturity dates are entered as valid Excel date values. You can use the DATE function to create a date value, e.g., DATE(2020, 1, 1) for January 1, 2020.
- Enter the discount rate as a decimal value. For example, if the discount rate is 5%, enter it as 0.05.
- When comparing different securities, make sure to use the same day count basis for consistency.
- Use the PRICEDISC function in conjunction with other financial functions, such as YIELDDISC, to analyze the performance of discounted securities.
Common Mistakes When Using PRICEDISC
- Using incorrect date formats for the settlement and maturity dates. Make sure to use valid Excel date values.
- Entering the discount rate as a percentage instead of a decimal value. For example, entering 5 instead of 0.05 for a 5% discount rate.
- Forgetting to specify the day count basis when comparing different securities, which may lead to inconsistent results.
Why Isn’t My PRICEDISC Working?
If you encounter issues with the PRICEDISC function, consider the following troubleshooting steps:
- Check the settlement and maturity dates for correct formatting and valid Excel date values.
- Ensure that the discount rate is entered as a decimal value, not a percentage.
- Verify that the redemption value is entered correctly.
- Make sure that the day count basis, if specified, is within the valid range of 0 to 4.
- Check for any error messages, such as #NUM! or #VALUE!, which may indicate issues with the input values or calculation.
PRICEDISC: Related Formulae
Here are some related Excel functions that can be used in conjunction with the PRICEDISC function for financial analysis:
- YIELDDISC: Calculates the annual yield for a discounted security.
- PRICE: Calculates the price per $100 face value of a security that pays periodic interest.
- YIELD: Calculates the yield on a security that pays periodic interest.
- COUPNUM: Calculates the number of coupons payable between the settlement date and maturity date.
- ACCRINT: Calculates the accrued interest for a security that pays periodic interest.
By mastering the PRICEDISC function and its related formulae, you can effectively analyze and compare the performance of various discounted securities in Excel.
