PRICEDISC Function
An Excel function that returns the price per $100 face value of a discounted security.
What is the PRICEDISC Function?
PRICEDISC is an Excel function that returns the price per $100 face value of a discounted security.
PRICEDISC is a financial formula that calculates the price of short-term securities that are issued at a discount from their face value. These securities do not pay any interest and are often used by corporations and governments to raise short-term capital.
This tool is essential in determining whether the price per $100 FV of a discounted security is a good value relative to its potential returns. It allows investors to quickly and accurately analyze the price value of short-term non-interest-bearing securities.
PRICEDISC has various components in its calculation to compute a bond’s price. An analyst must know the settlement date, discount rate, maturity date, and redemption value before inputting the function on Excel.
In addition, this function is usually used in conjunction with other financial formulas in Excel, such as YIELD and DURATION, to perform more complex calculations on debt instruments.
For example, all of the three functions above can be combined to derive the yield-to-maturity of a discounted bond. This helps investors better understand the risks and future returns associated with such securities.
Overall, PRICEDISC is a necessary Excel function for finance professionals who work with zero-coupon securities. However, combining it with the YIELD or DURATION function can extend its use to other securities.
Key Takeaways
- The PRICEDISC function on Excel finds the price per $100 face value of a discounted security; it focuses on short-term securities that pay no interest.
- Its syntax contains five main arguments, which are written as “=PRICEDISC(settlement, maturity, discount, redemption, [basis])”
- The [basis] component is optional, but it is an essential differentiator as certain companies have their own day count conventions ranging from US 30/360, Actual/Actual, Actual/360, Actual/365, and European 30/360.
- There are certain rules one must follow to avoid #NUM! and #VALUE! errors for inputs: The maturity date must be ahead of the settlement date, the discount rate and redemption value have to be greater than zero, and the day count basis should be between 0 to 4.
- The settlement date is always given as one day after issuance, and the discount rate has to align with the time format used for settlement and maturity.
- A bond price output of higher than $100 can be interpreted as the security trading at a premium; conversely, if the price is lower than $100, it is trading at a discount relative to its face value.
- PRICEDISC has many uses, and the function can be extended to valuation, investment analysis, financial planning, and risk management in the bond market.
How to use the PRICEDISC Function?
Once Excel has been opened, one can navigate to the PRICEDISC function by finding the Formulas tab and clicking the Financial dropdown. Alternatively, entering the function's syntax in an empty cell will also start the function.
There are several inputs that go into the PRICEDISC syntax. It can be shown as follows:
=PRICEDISC(settlement, maturity, discount, redemption, [basis])
where
- settlement: The settlement date refers to the date after issue once a security has been sold to a buyer
- maturity: This looks at the maturity date, the day when the security expires
- discount: It is the discount rate; it quantifies the rate of return required by investors to purchase a security at a discount and hold it until maturity
- redemption: The redemption value, the amount of money a buyer will receive when the security matures
- [basis]: Type of day count basis used
While Day Count Basis is an optional input, one must further understand its different integer arguments because a region could use its own specific financial day count basis.
In order to accurately measure the price per $100 face value of discounted securities, the day count basis input must be adjusted according to the day count convention used by a particular region or company.
The possible values when entering the [basis] argument are represented in the table below:
| [basis] Argument Input | Day Count Convention |
|---|---|
| 0 or left blank | 30/360 US Day Count: An input of 0 automatically uses this convention. It assumes each month has 30 days and a year comprises 360 days. |
| 1 | Actual/Actual Day Count: This takes the actual number of days between two dates and looks at the total days for that specific year. |
| 2 | Actual/360 Day Count: Here, the exact number of days between two dates is taken again, but this time the number of days in a year is fixed at 360. |
| 3 | Actual/365 Day Count: It is similar to the previous convention, but the total days in a year are now fixed at 365. |
| 4 | 30/360 European Day Count: Similar to the 30/360 US convention, the only difference is that if the starting date is the 31st of the month, then the ending date will not be adjusted to the 30th. |
Additionally, it is essential not to conflate the discount rate input for PRICEDISC with interest rates. Colloquially, discount rates are also referred to as the minimum interest rate set by the FED for lending.
However, in this case, the discount rate is for security that bears no interest. Hence, when calculating the PRICEDISC function, discount rates focus on the required rate of return for investors to buy a security on discount and hold it till maturity.
There are some error issues that can turn up if the inputs are not entered correctly. For example, the following mistakes will return a #NUM! error:
- The settlement date is ahead of the maturity date, or they both occur on the same day.
- The discount rate is less than or equal to zero.
- Day Count Basis input is not in the 0-4 range.
- The redemption value is less than or equal to zero.
Similarly, if the dates are not in the valid format, a #VALUE! error will be returned. This error applies to any non-numeric input used for the PRICEDISC function.
NOTE
The discount rate must be consistent with the time period used for settlement and maturity dates. If they do not align, the discount rate should be adjusted accordingly.
PRICEDISC Example in Excel
To illustrate how the price per $100 face value of a discounted security can be calculated, an arbitrary example of a zero-coupon bond is taken.
Let us assume a zero-coupon bond was issued to a buyer on May 1st, 2020, the bond matures in three years on May 1st, 2023, and it follows the 30/360 US day count convention.
NOTE
The settlement date used is one day after issuance; hence the settlement input must be May 2nd, 2020, instead of May 1st, 2020.
It also has a discount rate of 5%, and the redemption value is typically equal to the face value ($100) for zero-coupon bonds. The aforementioned inputs can be entered in the PRICEDISC function to find the price per $100 face value of the security.
The arguments used to compute the discounted price for the zero-coupon bond discussed above are broken down below:
The PRICEDISC function returns a bond price of $85.01, this is less than the redemption value of $100, which suggests the bond is trading at a discount.
It is roughly trading at a 15% discount; the following number is found by simply subtracting the bond price found using the PRICEDISC function from the face value. This difference is then divided by the Face Value to give the exact discount of the bond.
Bond Discount = [(100 - 85.01) / 100] * 100
Bond Discount = 14.99%
To check how the bond price output changes with different day count conventions, one can toggle with the day count basis value tab. For example, if Actual / 365 is the desired day count, then the [basis] component of PRICEDISC is moved from 0 to 3.
The following change in the day count basis is expressed below:
Peculiarly, changing the day count basis did not create any difference in the bond price, as it is still discounted at $85.01. In order to cross-check the bond price output, one can manually calculate PRICEDISC using a formula.
PRICEDISC Formula = Redemption - (Discount Rate * Redemption * DSM / B)
Where
- B = Number of Days in a year for Day Count Convention
- DSM = Days between the settlement date and maturity date
Calculating the DSM for the above example can be done in Excel using the DATEDIF function. To find the number of days between settlement and maturity, one can do =DATEDIF(settlement date, maturity date, “D”), where D is used to get the output in days.
DSM is found to be 1094 because those are the number of days between the settlement date (May 2nd, 2020) and maturity date (May 1st, 2023) in the example used. Redemption value and discount rate stay the same from the previous PRICEDISC function.
B is 365, as that is the number of days in a year for the Actual/ 365-day count basis. It can be seen that manually calculating PRICEDISC gives the same answer as using the PRICEDISC function. This confirms the function output seen earlier.
Therefore, it can be said with certainty that a zero-coupon bond which settles on May 2nd, 2020, and matures on May 1st, 2023, will have a discounted bond price of $85.01 per $100 face value if the discount rate is set at 5% and an Actual/ 365-day count convention is used.
PRICEDISC Interpretation
While the previous example explained the process of calculating a discounted price for zero-coupon security, there are other scenarios that can also be explored.
The PRICEDISC function indicates the price at the time of purchase, making it a present value function. Hence, if the price-output is greater than the $100 face value, it can be interpreted that the security is trading at a premium.
Similarly, if the price returned is lower than $100, one can conclude that the security is trading at a discount relative to its face value. The previous example provided a discounted bond price of $85.01, suggesting the bond is trading at a discount.
The most crucial aspect of PRICEDISC is that the function can be interpreted in a multitude of ways:
1. Investment Analysis
It can help investors evaluate potential returns after investing in zero-coupon security. This provides the basis for determining the implied YTM of a bond.
2. Financial Planning
Financial planners can use the PRICEDISC function to find the discounted bond price if a client has purchased a zero-coupon bond. This shows how much money needs to be invested today to achieve future financial goals for the client.
3. Valuation
The function also accurately calculates the fair value of zero-coupon security by using the prevailing discount rate, redemption value, and time to maturity. This must not be confused with the face value, which is the price paid at maturity.
4. Risk Management
One can assess the sensitivity of a bond’s fair value to its discount rate using PRICEDISC. This will allow risk managers to make informed decisions on the level of exposure to bonds that are highly sensitive to changes in discount rates.
In conclusion, the PRICEDISC function in Excel is a powerful tool in terms of calculating the discounted price of zero-coupon securities.
It makes the process straightforward and efficient by utilizing a few necessary inputs such as settlement date, redemption value, discount rate, day count basis, and maturity date.
Overall, PRICEDISC is a crucial introductory concept for bond valuation and analysis. It is essential for investors, financial planners, and risk managers in the bond market and can also be used as a foundation for more involved bond analysis.
or Want to Sign up with your social account?