Excel DISC Function
DISC is an Excel function that returns the discount rate on an investment which is issued and/or traded on discounted basis, such as US treasury bills, commercial paper, etc.
A discounted security pays no periodic interest but pays a specified face value at the maturity date. Return on a discounted security results from the difference in the price at which they are issued and their face value.
The following equation shows the pricing formula of a discounted security:
$$ \text{Price of a discounted security} \\= \text{Face value} \times (\text{1}-\text{discount rate} \times \text{A}/\text{B}) $$
Where A refers to the days between settlement date and the maturity date while B refers to total number of days in a year.
Syntax
DISC function has the following syntax:
DISC(settlement, maturity, pr, redemption, [basis])
Settlement refers to the settlement date, the date on which the transaction occurs i.e. the pricing date.
Maturity refers to the maturity date, the date on which the issuer of the security buys back the security and returns the redemption value of the investment.
Pr means the price of the security as at the settlement date.
Redemption refers to the value the issuer pays to the holder of the security as at the maturity date.
[Basis] is an optional argument specifying the day-counting method to be used. The default value is 0 specifying the US(NASD) 30/360 method. However, you can use other methods too by specifying different values in Excel.
Example
The following screenshot shows calculation of discount rate using DISC function:
Please note that the DISC function always returns the annual discount rate.
The same calculation can be reproduced manually as follows:
$$ \text{Discount Rate}\\=\text{1}&\text{minus};\frac{\text{Price of T}\text{-}\text{bill}}{\text{Face Value of T}\text{-}\text{bill}}\times\frac{\text{Days in a Year}}{\text{T}\text{-}\text{bill Maturity in Days}} $$
$$ \text{Discount Rate}\\=\text{1}&\text{minus};\frac{\text{\$98}}{\text{\$100}}\times\frac{\text{360}}{\text{179}}\\=\text{4.02%} $$
by Obaidullah Jan, ACA, CFA and last modified on