In fixed-income portfolio management and treasury analysis, most standard corporate and government bonds distribute regular coupon interest payments semiannually or annually. However, certain debt instruments—such as short-term municipal notes, commercial certificates of deposit (CDs), and specialized zero-to-medium-term debentures—do not distribute periodic coupons throughout their lifecycle. Instead, all accrued interest is calculated and paid in a single lump sum together with the return of principal on the maturity date.
To accurately evaluate and price these non-periodic debt instruments, Microsoft Excel provides two specialized financial functions: PRICEMAT (which calculates the clean price per £100/€100/$100 face value) and YIELDMAT (which calculates the annualized yield). This comprehensive tutorial details their mathematical mechanics, syntax specifications, day-count basis conventions, worked practical examples, and common error resolution.
Periodic Coupon Bonds vs Interest-at-Maturity Securities
Understanding the timing of cash flows is essential when selecting the appropriate financial function in Excel:
- Periodic Coupon Bonds (
PRICE/YIELD): Distribute interest payments on regular schedule dates (e.g., every 6 months) throughout the bond’s duration. - Discount Securities (
PRICEDISC/YIELDDISC): Sold at a deep discount below par value (e.g., Treasury Bills) and pay zero stated interest rate; the entire yield derives from the difference between the discounted purchase price and the redemption value. - Securities Paying Interest at Maturity (
PRICEMAT/YIELDMAT): Carry an explicit stated annual interest coupon rate, but that interest accumulates over the life of the instrument and is paid entirely upon final maturity.
The PRICEMAT Function: Syntax and Mathematics
Mathematical Formulation
The PRICEMAT function calculates the price per 100 face value of a security that pays interest at maturity using the following financial formula:
$$\text{Price} = \frac{100 + \left(\frac{\text{DIM}}{\text{B}} \times \text{rate} \times 100\right)}{1 + \left(\frac{\text{DSM}}{\text{B}} \times \text{yield}\right)} – \left(\frac{\text{A}}{\text{B}} \times \text{rate} \times 100\right)$$
Where:
- B: Number of days in the year, based on the selected day-count basis.
- DSM: Number of days from settlement date to maturity date.
- DIM: Number of days from issue date to maturity date.
- A: Number of days from issue date to settlement date (accrued period).
Excel Syntax
=PRICEMAT(settlement, maturity, issue, rate, yield, [basis])The YIELDMAT Function: Syntax and Mathematics
Mathematical Formulation
The YIELDMAT function solves for the annualized yield when the market settlement price is already known:
$$\text{Yield} = \frac{\frac{100 + \left(\frac{\text{DIM}}{\text{B}} \times \text{rate} \times 100\right)}{\text{Price} + \left(\frac{\text{A}}{\text{B}} \times \text{rate} \times 100\right)} – 1}{\frac{\text{DSM}}{\text{B}}}$$
Excel Syntax
=YIELDMAT(settlement, maturity, issue, rate, pr, [basis])Parameter Rules and Day-Count Basis Conventions
settlement: Required. The security’s settlement date (the date after issuance when the buyer purchases the instrument).maturity: Required. The security’s maturity date when the contract expires and principal plus interest is paid.issue: Required. The security’s original issuance date when interest initially began accruing.rate: Required. The security’s stated annual interest rate (e.g.,0.055or5.5%).yield(PRICEMAT): Required. The security’s annual required market yield.pr(YIELDMAT): Required. The security’s market price per 100 face value.[basis]: Optional. The day-count calendar convention used to count accrued days:
| Basis Value | Day Count Convention | Standard Application |
|---|---|---|
0 (or omitted) |
US (NASD) 30/360 | US corporate and municipal bonds |
1 |
Actual/Actual | UK Gilts, US Treasuries, sovereign debt |
2 |
Actual/360 | Commercial paper, Eurocurrency deposits |
3 |
Actual/365 | UK money market instruments |
4 |
European 30/360 | Eurobonds and continental securities |
Step-by-Step Worked Example: PRICEMAT
Suppose you are evaluating a short-term certificate of deposit issued on 1 January 2025 that matures on 1 July 2026. You plan to purchase the note on the secondary market on 15 October 2025:
| Cell | Financial Parameter | Value |
|---|---|---|
B1 |
Settlement Date | DATE(2025, 10, 15) |
B2 |
Maturity Date | DATE(2026, 7, 1) |
B3 |
Issue Date | DATE(2025, 1, 1) |
B4 |
Coupon Rate | 5.25% (0.0525) |
B5 |
Market Yield Required | 4.80% (0.0480) |
B6 |
Day-Count Basis | 0 (US 30/360) |
Enter the PRICEMAT formula into cell B7:
=PRICEMAT(B1, B2, B3, B4, B5, B6)Excel returns a price of approximately 100.418 per 100 face value. Because the market required yield (4.80%) is lower than the security’s coupon rate (5.25%), the instrument trades at a premium above par.
Step-by-Step Worked Example: YIELDMAT
Now, suppose the same security is offered in the market at a quoted clean price of 100.25. What is your annualized rate of return (yield)?
Enter the YIELDMAT formula into cell B8:
=YIELDMAT(B1, B2, B3, B4, 100.25, B6)Excel returns 0.0504. Format cell B8 as a percentage (Format > Number > Percentage) to reveal an annualized yield of 5.04%.
Chronological Date Hierarchy Verification
For both PRICEMAT and YIELDMAT to calculate valid results, date arguments must strictly observe the following timeline hierarchy:
$$\text{Issue Date} \le \text{Settlement Date} < \text{Maturity Date}$$
If the settlement date occurs on or after the maturity date, or if the settlement date precedes the original issue date, Excel returns an immediate #NUM! error.
Troubleshooting Common Errors
| Error Code | Underlying Cause | Recommended Fix |
|---|---|---|
#NUM! |
Invalid date timeline or rate/yield \(\le 0\) | Verify that Issue <= Settlement < Maturity and that coupon rate and yield are positive values. |
#VALUE! |
Dates entered as unparsed text strings | Never pass dates as manual text (e.g., "15/10/2025"). Always wrap dates in the DATE() function: DATE(2025, 10, 15). |
| Slight discrepancy vs dealer price | Incorrect day-count basis convention | Confirm whether the security uses 30/360 (Basis 0) or Actual/Actual (Basis 1). |