Yield to maturity and yield to call with RATE

Turning the formula around

Lesson 10.2 took a yield and produced a price. The market does the reverse: it quotes a price, and the yield to maturity is the rate that makes the PV of the coupons and par equal to it — the internal rate of return of buying the bond and holding it to the end. There is no closed form; Excel's RATE solves it by iteration, per period.

Worked example — a 4.5 % 7-year bond priced at 925.60

Pmt 22.50, nper 14, fv 1,000, and the price entered as a negative pv — it is what you pay:

=RATE(14,22.5,-925.60,1000) = 0.029043 per half-year.

That is a six-month rate. The market quotes the bond-equivalent yield, the semi-annual rate doubled: =RATE(7*2,0.045*1000/2,-925.60,1000)*2 = 5.8087 %. Not compounded — doubled; that is the convention (Lesson 9.1). A discount bond, so the YTM (5.81 %) sits above the coupon (4.5 %), as Lesson 10.3 predicts.

Excel's date-based twin agrees: =YIELD(settle,maturity,0.045,92.56,100,2) = 5.8087 %, with the price entered per 100 (925.60 ÷ 10).

The sign trap. =RATE(14,22.5,925.60,1000) — price positive, par positive — returns #NUM!. RATE needs the flows to change sign somewhere: you pay the price, you receive the coupons and par. Enter the price negative, or the coupons and par negative, never all the same.

Yield to call — the premium callable

Cobalt Ridge Mining's 6.5 % 10-year bond is callable in 4 years at 103 and trades at 1,062.00. Two yields:

• YTM, as if held 10 years: =RATE(20,32.5,-1062,1000)*2 = 5.68 %.
• Yield to call (YTC): replace nper with the periods to the call (8) and fv with the call price (1,030): =RATE(8,32.5,-1062,1030)*2 = 5.44 %.

Why quote the lower one? The bond trades at a premium because its 6.5 % coupon beats the market — which is exactly when the issuer will call it to refinance cheaply (Lesson 9.3). An investor who plans on 5.68 % for ten years is planning on coupons the issuer intends to stop paying. The yield to worst — the lower of YTM and YTC — is the honest figure for a premium callable; for a discount callable the call is unlikely and YTM stands.

What YTM assumes

YTM is a promised return under two assumptions: the bond is held to maturity, and every coupon is reinvested at the YTM. Sell early (Lesson 10.7) or reinvest at a different rate and the realised return differs. It is still the market's standard quote because it is the one number that summarises price, coupon and term together.

Recompute

A 6 % 9-year bond priced at 1,041.00: =RATE(18,30,-1041,1000)*2 = 5.42 % — a premium bond, YTM below the coupon. Why the negative price, cold: because the flows must change sign — you pay the price and receive the rest.