Related Functions:

The PRICEDISC function calculates the price, per $100 face value of a discounted security.

Syntax

=PRICEDISC(settlement,maturity,discount,redemption,[basis])

Arguments

Argument Description
settlement Settlement date of the security, i.e., the date the coupon was purchased
maturity Maturity date of the security
discount Discount rate as a percentage
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 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 01-Apr-2010 Settlement date    
3 31-Mar-2015 Maturity date    
4 4.5% Discount rate    
5 $100 Face Value    
6 2 Actual/360 basis    
7        
8 Formula Result Notes
9 =PRICEDISC(A2,A3,A4,A5,A6) $77.50 The bond price, for the bond with the arguments specified in cells A2:A5, [basis] argument is omitted – takes on the default of 0
10 =PRICEDISC(DATE(2010,4,1),DATE(2015,3,31),A4,A5,A6) $77.19 The bond price, for the bond with the arguments specified in cells A2:A6, DATE function used to enter dates

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 recognized as valid dates
#NUM! Occurs if either:

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