Related Functions:

The PRICEMAT function calculates the price, per $100 face value of a security that pays interest at maturity.

Syntax

=PRICEMAT(settlement,maturity,issue,rate,yld,[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
issue Issue date of the security
rate Security’s interest rate at the date of issue
yld Annual yield of the security
[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, maturity and issue 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-2011 Settlement date    
3 31-Mar-2015 Maturity date    
4 01-Jan-2011 Issue date    
5 4.5% Interest rate – semi-annual    
6 5.5% Annual yield    
7 1 Actual/actual basis    
8        
9   Formula Result Notes
10 =PRICEMAT(A2,A3,A4,A5,A6) $96.53 The price for the bond, using the arguments specified in cells A2:A6, [basis] argument is omitted – defaults to 0
11 =PRICEMAT(A2,A3,A4,A5,A6,A7) $96.52 The price for the bond, using the arguments specified in cells A2:A7

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  any of the supplied arguments are non-numeric
  the supplied settlementmaturity or issue dates are not recognized as valid dates
#NUM! Occurs if either:

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

PRICEMAT is calculated as:  

    \[ \text{PRICEMAT}=\frac{100+( \frac{DIM}{B}\ \text{x}\ rate\ \text{x}\ 100)}{1+( \frac{DSM}{B}\ \text{x}\ yld)} - \left( \frac{A}{B}\ \text{x}\ rate\ \text{x}\ 100 \right) \]

where:

  • A = number of days from issue to settlement
  • B = number of days in year, depending on [basis]
  • DIM = number of days from issue to maturity
  • DSM = number of days from settlement to maturity