Related Functions:

The DISC function calculates the Discount Rate for a bond.

Syntax

=DISC(settlement,maturity,pr,redemption,[basis])

Arguments

Argument Description
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

  [basis] Day Count Basis
  0 (or omitted) US (NASD) 30/360
  1 actual/actual
  2 actual/360
  3 actual/365
  4 European 30/360

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.

Examples

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:

  A B C D
1 Data      
2 $95 Purchase Price    
3 $100 Redemption Value    
4 01-Apr-10 Settlement Date    
5 31-Mar-15 Maturity Date    
6        
7   Formula Result Notes
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
#NUM! Occurs if:

  invalid numbers are input for the pr, redemption or [basis] arguments
  the maturity date is ≤ the settlement date

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:

    \[ DISC = \frac{redemption - par}{redemption} \text{x} \frac{B}{DSM} \]