Related Functions:

The YIELDDISC function calculates the annual yield of a discounted security.

Syntax

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

Arguments

Argument Description
settlement The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer
maturity The security’s maturity date. The maturity date is the date when the security expires
pr The security’s price per $100 face value
redemption The security’s redemption value 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 in detail 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

  A B C D
1 Data      
2 16-Feb-2014 Settlement date    
3 01-Mar-2014 Maturity date    
4 $99.795 Price    
5 $100.00 Redemption value    
6 2 Actual/360 basis    
7        
8 Formula Result Notes
9 =YIELDDISC(A2,A3,A4,A5,A6) 5.28% The yield, for the bond with the terms above (0.052823)

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  any of the supplied arguments are non-numeric
  the supplied settlement or maturity dates are not valid dates
#NUM! Occurs if either:

  the settlement date is ≥ maturity date
  invalid numbers are supplied for the pr, redemption or [basis] arguments
#NAME? Occurs when Analysis ToolPak add-in is not enabled