Related Functions:

The PRICE function calculates the price, per $100 face value of a security that pays periodic interest.

Syntax

=PRICE(settlement,maturity,rate,old,redemption,frequency,[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 that the coupon expires
rate Annual coupon rate of the security
yld Annual yield of the security
redemption Redemption value per $100 face value
frequency Number of coupon payments per year. This must be one of the following:

  1 Annually
  2 Semi-Annually
  3 Quarterly
[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-2012 Settlement date    
3 31-Mar-2020 Maturity date    
4 10% Annual coupon rate    
5 12% Annual yield rate    
6 $100 Redemption Value    
7 2 Frequency – Semi-Annually    
8 0 30/360 basis    
9        
10 Formula Result Notes
11 =PRICE(A2,A3,A4,A5,A6,A7) $89.90 The bond price, for the bond with the arguments specified in cells A2:A7, [basis] argument is omitted – takes on the default of 0
12 =PRICE(DATE(2010,4,1),DATE(2015,3,31),A4,A5,A6,A7) $89.90 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 settlement or the maturity arguments are not valid dates
#NUM! Occurs if:

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