Settlement (required argument) – This is the date when the coupon was purchased. It is the security’s settlement date or the date after the issue date when the security is traded to the buyer.
Maturity (required argument) – This is the maturity date of the security or the date on which the security expires.
Pr (required argument) – The security’s price per $100 face value.
Redemption (required argument) – This is the security’s redemption value per $100 face value.
Basis (optional argument) – Defines the day count basis that is used in the calculation. The possible values can be as follows:
Day Count basis
0 or omitted
How to use the DISC Function in Excel?
To understand the uses of the DISC function, let’s consider a few examples:
Suppose we are given the following data:
The formula to use is:
We get a result of 3%. If the result is in number format, we need to convert it into percentage format.
In the example above:
We omitted the basis argument so the function took it as 0, which denotes the US (NADS) 30/360 day count basis).
We gave the date arguments to the DISC function as references to cells containing dates. If the date format is not used, we will get an error as a result.
Let’s take another example. Suppose we are given information about a European bond:
The formula to use is:
We get a result of 13%. In the example:
We used the basis 4 so the function took it as 4, which denotes the European 30/360 count basis.
We gave the date arguments to the DISC function as references to cells containing dates. Remember, based on the settings of your system, text representations of dates in Excel functions may vary.
Things to remember about the DISC Function
#NUM! error – Occurs when:
The given maturity date is less than or equal to the settlement date.
Inputs for the arguments pr, redemption or basis are invalid numeric values. That is, if pr is less than or equal to 0, if redemption is less than or equal to 0, or basis given in the formula is not equal to 1, 2, 3, or 4.
Settlement, maturity, and basis are truncated to integers.
Dates are stored in sequential serial numbers, so they can be used in calculations. By default, January 1, 1900 is serial number 1, and July 16, 2016 is serial number 42567 because it is 42,567 days after January 1, 1900.
#VALUE! error – Occurs when:
The given maturity date or settlement date is not a valid Excel date.
Thanks for reading CFI’s guide to the Excel DISC function. By taking the time to learn and master these Excel functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.