# The Excel PRICEMAT Function

## Basic Description

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

The syntax of the function is :

PRICEMAT( settlement, maturity, issue, rate, yld, [basis] )

Where the arguments are as follows:

settlement-The settlement date of the security (ie. the date that the coupon is purchased)
maturity-The maturity date of the security (ie. the date that the coupon expires)
issue-The issue date of the security
rate-The security's interest rate at the date of issue
yld-The security's annual yield
[basis]-An optional integer argument which specifies the financial day count basis that is used by the security. Possible values are:
BasisDay Count Basis
0 (or omitted)US (NASD) 30/360
1actual/actual
2actual/360
3actual/365
4European 30/360
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

Note that the date arguments must satisfy the following:

issue   <   settlement   <   maturity

Note also, that the settlement, maturity and issue arguments should be entered into the function as either:

• References to cells containing dates
or
• Dates returned from formulas

Warning:

• If you attempt to input the date arguments as text, these can be interpreted differently, depending on the date system and date interpretation settings on your computer.
• Although you can enter dates directly, as serial numbers, this is not recommended, as date serial numbering varies across different computer systems.

## Excel Pricemat Function Example

In the following example, the Excel Pricemat function is used to calculate the price per \$100 face value of a security that pays interest at maturity. The security's issue date is 01-Jan-2011, the settlement date is 01-Apr-2011, and the maturity date is 31-Mar-2015. The rate of interest at issue is 4.5% and the annual yield is 2.5%. The US (NASD) 30/360 day count basis is used:

AB
1Settlement Date:01-Apr-2011
2Maturity Date:31-Mar-2015
3Issue Date:01-Jan-2011
4=PRICEMAT( B1, B2, B3, 4.5%, 2.5% )

The function calculates the price per \$100 face value to be \$107.17.

Note that, in the above example:

• As recommended, the settlement, maturity and issue dates have been input as references to cells containing dates.
• The rate and yld arguments are input as percentages 4.5% and 2.5%. However, these arguments could, instead, be entered as the simple numerical values 0.045 and 0.025
• The [basis] argument has been omitted, and so the function uses the default value 0 (denoting the US (NADS) 30/360 day count basis).

Further examples of the Excel Pricemat function can be found on the Microsoft Office website.

## Pricemat Function Errors

If you get an error from the Pricemat function, this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:the settlement date is ≥ issue datethe settlement date is ≥ maturity dateInvalid numbers are supplied for the rate, yld or basis arguments(i.e. if either: rate < 0; yld < 0; or basis is any number other than 0, 1, 2, 3 or 4) #VALUE! - Occurs if either:any of the supplied arguments are non-numericthe supplied settlement, maturity or issue arguments are not a valid Excel dates #NAME? - Occurs when Analysis ToolPak add-in is not enabled in your Excel.You will need to enable the add-in if you want to use the Excel Pricemat function.