Financial
Coupon-date bond valuation with PV: par, discount and premium
Price a simplified fixed-coupon bond on a coupon date and compare its current yield with yield to maturity.
Discount a regular coupon stream and principal repayment
This example values a plain fixed-coupon bond immediately after a coupon payment, with regular equal remaining periods and principal repaid at maturity. The nominal annual yield is compounded at coupon frequency m.
PV(YTM/m,years*m,-coupon,-par) sums the remaining coupons and principal. The model excludes options, default, fees, odd coupon periods and accrued interest.
For this cash-flow pattern, yields above the coupon rate give a price below par; equal rates give par; lower yields give a premium. Current yield is annual coupon divided by price and is not total return or YTM.
Between coupon dates, use the settlement schedule and applicable day-count convention. Do not add accrued interest to this coupon-date PV and assume that gives a correct mid-period settlement price.
| Market YTM ($1,000 Par, 5% Semiannual, 5 Yrs) | Formula | Bond Price |
|---|---|---|
| 6.0% YTM (> 5% Coupon -> Discount) | =PV(6%/2, 5*2, -25, -1000) | $957.35 |
| 5.0% YTM (= 5% Coupon -> Par) | =PV(5%/2, 5*2, -25, -1000) | $1,000.00 |
| 4.0% YTM (< 5% Coupon -> Premium) | =PV(4%/2, 5*2, -25, -1000) | $1,044.91 |
Coupon bond PV price and Current Yield formulas
Both -CouponPmt and -Par carry negative outflow signs inside PV so the bond price returns positive.
=ROUND(PV(C2 / E2, D2 * E2, -(A2 * B2 / E2), -A2), 2)Current Yield (Annual Coupon $ / Bond Market Price)
Compares annual cash coupon income against current trading price.
=ROUND((A2 * B2) / PV(C2 / E2, D2 * E2, -(A2 * B2 / E2), -A2), 4)Try the example
Use the original values or change them to test your understanding.