PArT 2: VALUATION, rISk AND reTUrN
Breaking this calculation into the annuity and lump sum components, we have:PVPV
ofcoupons$91.25
0.10
1
1
1. 10
$592.68
ofprincipal$1, 000
1. 10
$350.49
Priceofbond $592.68 $350.49 $943.171111=
×−
=
=
=
=+=
We could replicate the bond-price calculation in Excel by entering = PV(0.10,11,–91.25–1,000,0) to obtain $943.17.In this case the bonds trade at a discount because each month investors receive a coupon yield of
about 9.7% ($91.25 ÷ $943.17), a little less than the required rate of 10%. Offsetting that, the bond has
a built-in gain at maturity of $56.83 ($1,000 face value – $943.17 purchase price). The net effect of the
below-market coupon payments and the gain at maturity is that investors who buy and hold this bond
earn a yield to maturity of exactly 10%.exampleRotorua Enterprises has an outstanding bond issue
that pays a 6% annual coupon, has a $1,000 face
value, and matures in five years. The current market
value of one Rotorua bond is $1,021.35. What yield
to maturity do these bonds offer investors?
Because the bond sells at a premium, we can
infer that the yield to maturity is less than the bond’s
coupon rate. We can use a financial calculator or
Excel to calculate the answer very quickly, but let’s
try a trial-and-error approach first to strengthen our
intuition about the relationship between a bond’s
price and its YTM. We will start by determining the
bond’s value if it offers a YTM of 5%. At that rate, the
price of the bond would be the following:PVPV
ofcoupons$60
0.05
1
1
(1.05)
$259.77
ofprincipal$1, 000
(1.05)
$783.53
Priceofbond $259.77 $783.53 $1, 043 .3055=
×−
=
==
=+=
Our initial guess of 5% produces a price that
exceeds the market price of Rotorua’s bond. Because
we initially calculated a price that is too high, we need
to try again using a higher YTM. Discounting the
bond’s cash flows at a higher YTM results in a lower
price. Suppose the YTM equals 5.5%. Now we have:PVPV
ofcoupons$60
0.055
1
1
(1.055)
$256.2 2
ofprincipal$1, 000
(1.055)
$765.13
Priceofbond $256.22 $765.13 $1, 021 .3555=
×−
=
==
=+=
The YTM equals 5.5%, because that is the
discount rate that equates the present value of the
bond’s cash flows with its current market price. In
Excel, you can find a bond’s yield to maturity by
using the IRR (internal rate of return) function. The
spreadsheet information is given below, with the
coupon payments identified for each period they
occur, as well as the return of principal in period 6.
To calculate the yield to maturity using this
function, simply enter the bond’s market price as
a negative number and its cash flows as positive
numbers, as shown below. Then type the IRR function
and highlight the cells containing the values. Be
sure that the cell in which you type the IRR function
is formatted to show the answer to several decimal
places.
Spreadsheet
Column
Row A B
1 Price –1,021.35
2 Coupon 60
3 Coupon 60
4 Coupon 60
5 Coupon 60
6 Coupon+Principal 1,060
7 IRR 5.50%
8 Formula B7: IRR(B1:B6)example