How to use PRICEMAT Function in Excel

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

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

The PRICEMAT function syntax has the following arguments:

• Settlement    Required. 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    Required. The security’s maturity date. The maturity date is the date when the security expires.
• Issue    Required. The security’s issue date, expressed as a serial date number.
• Rate    Required. The security’s interest rate at date of issue.
• Yld    Required. The security’s annual yield.
• Basis    Optional. The type of day count basis to use.

Basis	Day count basis
0 (zero) or omitted	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

Example: Let’s look at some Excel PRICEMAT function examples and explore how to use the PRICEMAT function as a worksheet function in Microsoft Excel:

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-Jul-2019, the settlement date is 01-Jan-2019, and the maturity date is 01-Jan-20215. The rate of interest at issue is 6.55% and the annual yield is 7.15%. The US (NASD) 30/360 day count basis is used:

Syntax:  =PRICEMAT(B1,B2,B3,B4,B5,B6)

Result: 96.70705634

Note:

1. #NUM! error – Occurs if either:
   1. The issue date is greater than or equal to the settlement date.
   2. The settlement date is greater than or equal to the maturity date.
   3. When we provide invalid numbers for the rate, yield, or basis arguments. That is, if we provided a rate that is less than zero, a yield that is less than zero, or basis is a number other than 0,1,2,3,4.
2. #VALUE! error – Occurs if:
   1. The given settlement, maturity, or issue arguments are invalid Excel dates.
   2. Any of the given arguments are non-numeric.
3. PRICEMAT is calculated as follows: where:
   • B = number of days in year, depending on year basis.
   • DSM = number of days from settlement to maturity.
   • DIM = number of days from issue to maturity.

   • A = number of days from issue to settlement.