Investments, tenth edition

e

X

c e l

Please visit us at 

www.mhhe.com/bkm

A

B

C

D

E

F

G

=B12+B15

Formula in column B

2.25% coupon bond,

maturing July 31, 2018

6.25% coupon bond,

maturing May 2030

8% coupon bond,

30-year maturity

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Settlement date

Maturity date

Annual coupon rate

Yield to maturity

Redemption value (% of face value)

Coupon payments per year

Flat price (% of par)

Invoice price

Accrued interest

Days in coupon period

Days since last coupon

= DATE (2012, 7, 31)

= DATE (2018, 7, 31)

=PRICE(B4,B5,B6,B7,B8,B9)

=COUPDAYBS(B4,B5,2,1)

=COUPDAYS(B4,B5,2,1)

=(B13/B14)*B6*100/2

7/31/2012

7/31/2018

0.0225

0.0079

100

2

2

2

0

108.5392

108.5392

184

7/31/2012

5/15/2030

0.0625

0.02117

100

161.0020

162.3097

184

1.308

77

1/1/2000

1/1/2030

0.08

0.1

100

81.0707

81.0707

182

0

0

0

 Spreadsheet 14.1 

 Bond Pricing in Excel 

 eXcel APPLICATIONS:    Bond Pricing 

bod61671_ch14_445-486.indd   457

bod61671_ch14_445-486.indd   457

7/17/13   3:51 PM

7/17/13   3:51 PM

Final PDF to printer

---

458 

P A R T   I V

 Fixed-Income 

Securities

 Suppose an 8% coupon, 30-year bond is selling at $1,276.76. What average rate of 

return would be earned by an investor purchasing the bond at this price? We find the 

interest rate at which the present value of the remaining 60 semiannual payments equals 

the bond price. This is the rate consistent with the observed price of the bond. There-

fore, we solve for  r  in the following equation:   

$1,276.76

5 a

60

t

51

$40

(1

1 r)

t

1

$1,000

(1

1 r)

60

  

 or, equivalently,   

1,276.76 5 40 3 Annuity factor(r, 60) 1 1,000 3 PV factor(r, 60)  

 These equations have only one unknown variable, the interest rate,  r.  You can use a 

financial calculator or spreadsheet to confirm that the solution is  r   5  .03, or 3%, per 

half-year.  

8

   This is the bond’s yield to maturity.

   

 The financial press reports yields on an annualized basis, and annualizes the bond’s 

semiannual yield using simple interest techniques, resulting in an annual percentage 

rate, or APR. Yields annualized using simple interest are also called “bond equivalent 

yields.” Therefore, the semiannual yield would be doubled and reported in the newspa-

per as a bond equivalent yield of 6%. The  effective  annual yield of the bond, however, 

accounts for compound interest. If one earns 3% interest every 6 months, then after 

1 year, each dollar invested grows with interest to $1   3   (1.03) 

2

    5   $1.0609, and the 

effective annual interest rate on the bond is 6.09%. 

 Example  14.4 

Yield to Maturity 

   

8

 On your financial calculator, you would enter the following inputs:  n   5  60 periods; PV  5   2 1276.76;  FV  5  1000; 

PMT  5  40; then you would compute the interest rate (COMP  i  or CPT  i ). Notice that we enter the present value, 

or PV, of the bond as  minus  $1,276.76. Again, this is because most calculators treat the initial purchase price 

of the bond as a cash outflow.  Spreadsheet  14.2  shows how to find yield to maturity using Excel. Without a 

financial calculator or spreadsheet, you still could solve the equation, but you would need to use a trial-and-error 

approach.  

    14.3 

Bond Yields 

  Most bonds do not sell for par value. But ultimately, barring default, they will mature to 

par value. Therefore, we would like a measure of rate of return that accounts for both cur-

rent income and the price increase or decrease over the bond’s life. The yield to maturity is 

the standard measure of the total rate of return. However, it is far from perfect, and we will 

explore several variations of this measure.