Bond price as PV of coupons plus par

◈ 9 cards

One PV call prices a semi-annual coupon bond: the coupon annuity and the par lump together, rate ÷ 2, years × 2, coupon ÷ 2 — and Excel’s PRICE function returns the same number per 100 of par.

Two cash-flow shapes, one call

A coupon bond promises two things: a level annuity of coupons and a single lump of par at the end. Module 7 priced each separately; Excel's PV prices both at once because it accepts a payment and a future value:

$P_{0} = coupon annuity C \times \frac{1 - ( 1 + y ) ^{- n}}{y} + par lump \frac{Par}{( 1 + y ) ^{n}}$

where every symbol is per half-year: $C$ = annual coupon ÷ 2, $y$ = yield ÷ 2, $n$ = years × 2 (Lesson 9.1).

Worked example — Prairie Grid 5.2 %, 10 years, at a 6 % market yield

Par $1,000, coupon 5.2 % → 26 per half-year; 20 periods; rate 3 % per period:

=-PV(0.06/2,10*2,0.052*1000/2,1000) = =-PV(0.03,20,26,1000) = 940.49

The minus sign: PV returns the opposite sign to the cash flows you enter — positive coupons and par come back as a negative price, so negate the call. Decomposed, the coupon annuity is =-PV(0.03,20,26,0) = 386.81 and the par is =-PV(0.03,20,0,1000) = 553.68; they sum to 940.49. More than half of this bond's value is the par at the end.

The PRICE function. Excel's bond function takes dates and returns the price per 100 of par: =PRICE(settle,maturity,0.052,0.06,100,2) = 94.049 with settlement on a coupon date ten years before maturity. Multiply by 10 for a $1,000 bond, by 1,000 for $100,000 of par. It is the same number as PV, quoted the way the market quotes (Lesson 9.7).

A second bond

Tamarack Foods 7 %, 8 years, $1,000 par, market yield 5.5 %: pmt 35, nper 16, rate 0.0275 — =-PV(0.0275,16,35,1000) = 1,096.03. Above par, because the 7 % coupon is more than the 5.5 % the market requires; Lesson 10.3 makes that a rule.

The error that costs the mark

=-PV(0.06,10,52,1000) = 941.12 — close enough to look right, and wrong. It left all three inputs annual. Halving the rate alone or doubling the periods alone is worse: the three conversions travel together. Check the sign too: a positive PV result means the price was fed as a negative somewhere it should not have been.

Recompute

Prairie Grid at a 5 % yield: =-PV(0.025,20,26,1000) = 1,015.59 — the same coupons, discounted less, are worth more. A fresh bond cold: 6 % coupon, 12 years, $1,000 par, yield 7 % → =-PV(0.035,24,30,1000) = 919.71.

Twenty coupons of 26 and one lump of 1,000, all discounted at 3 % per half-year. The annuity piece and the lump piece land at t = 0 and add to the price.