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:
- #NUM! error – Occurs if either:
- The issue date is greater than or equal to the settlement date.
- The settlement date is greater than or equal to the maturity date.
- 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.
- #VALUE! error – Occurs if:
- The given settlement, maturity, or issue arguments are invalid Excel dates.
- Any of the given arguments are non-numeric.
- 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.