The DISC function calculates the Discount Rate for a bond.
|settlement||Settlement date of the security, i.e. the date that the coupon is purchased|
|maturity||Maturity date of the security, i.e. the date the coupon expires|
|pr||Price of the security per $100 face value|
|redemption||Redemption value of the security per $100 face value|
|[basis]||Optional. Defines the day count basis to be used in the calculation
The financial day count basis rules are explained further on the Wikipedia Day Count Convention page
Note: The settlement and maturity dates should be input as either:
- References to cells containing dates, or
- Dates returned from formulas
If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.
Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.
The spreadsheet below shows the DISC function used to calculate the discount rate of a coupon purchased on 01-Apr-2010, with Maturity date 31-Mar-2015. The price per $100 face value is $95, the Redemption value is $100, and the US (NASD) 30/360 day count basis is used:
|8||=DISC(B4,B5,95,100)||1%||[basis] argument is omitted – takes on the default of 0|
|9||=DISC(DATE(2010,4,1),DATE(2015,3,31),B2,B3)||1%||DATE function used to enter dates|
Common Function Error(s)
|Problem||What went wrong|
|#VALUE!||Occurs if the supplied settlement or maturity arguments are not valid dates|
The amount by which the market price of a bond is lower than its principal amount due at maturity. This amount, called its par value, is often $1,000. As bond prices are quoted as a percent of face value, a price of 98.00 means that the bond is selling for 98% of its face value of $1,000.00 and the bond discount is 2%. DISC is calculated as: