Financial & Accounting
Interest-at-Maturity Note Clean Price & Yield (PRICEMAT & YIELDMAT Formulas)
Calculate the clean secondary-market price (PRICEMAT), dirty invoice price, and annualized yield to maturity (YIELDMAT) for certificates of deposit and notes paying interest in a single lump sum at maturity.
Separate dirty present value from accrued interest since issue
Short- and medium-term bank certificates of deposit (CDs) and bullet notes pay principal plus simple interest in a single lump sum at maturity rather than periodic coupons. With DIM = maturity - issue total days, A = settlement - issue accrued days, DSM = maturity - settlement remaining days, coupon rate r, yield y, and day-count basis B, the maturity cash flow per $100 par is 100 * (1 + r * DIM / B).
Discounting that lump sum at simple money-market yield y gives dirty invoice price Dirty = 100 * (1 + r * DIM / B) / (1 + y * DSM / B). Subtracting seller accrued interest 100 * r * (A / B) yields Excel’s exact clean PRICEMAT formula, and inverting for y from clean price P yields YIELDMAT.
| Component (Issue Jan 1, Settle Mar 2, Mat Jun 30) | Days / Rate | Formula Value (per $100 Par) |
|---|---|---|
| Total Issue-to-Maturity Span (DIM) | 180 days @ r = 6.0% | Maturity Cash Flow = $103.00 |
| Remaining Settle-to-Maturity (DSM) | 120 days @ y = 4.5% | Dirty Invoice Price = $101.4778 |
| Accrued Interest Since Issue (A) | 60 days @ r = 6.0% | Accrued Interest = $1.0000 |
| Clean Secondary Price (PRICEMAT) | Dirty - Accrued | Clean Price = $100.4778 |
Clean Price of an Interest-at-Maturity Security (PRICEMAT)
Computes clean price per $100 par from Issue (B2), Settlement (B3), Maturity (B4), Coupon Rate (B5), Yield (B6), and Day Basis (B7).
=IF(OR(B3 < B2, B4 <= B3, B7 <= 0), "Invalid Dates", LET(dim, B4 - B2, dsm, B4 - B3, accDays, B3 - B2, dirty, (100 + 100 * B5 * dim / B7) / (1 + B6 * dsm / B7), ROUND(dirty - 100 * B5 * accDays / B7, 4)))Annualized Yield From Clean Market Price (YIELDMAT)
Inverts clean market price B8 back into annualized money-market yield YIELDMAT.
=LET(dim, B4 - B2, dsm, B4 - B3, accDays, B3 - B2, matPay, 100 + 100 * B5 * dim / B7, dirtyIn, B8 + 100 * B5 * accDays / B7, ROUND((matPay / dirtyIn - 1) * (B7 / dsm), 6))Try the example
Use the original values or change them to test your understanding.