Spreadsheet Problem: Interest Rate Changes and Maturity You have a portfolio of three bonds. The long bond will mature in 19 years and has a 5.5 percent coupon rate. The midterm bond matures in 9 years and has a 6.6 percent coupon rate. The short bond matures in only 2 years and has a 4 percent coupon rate.

(LG7-5)

a. Construct a spreadsheet that shows the value of these three bonds and the portfolio when the discount rate is 5 percent. The spreadsheet can look something like this:

b. Illustrate what happens when the discount rate increases by 0.5 percent. What do you notice about the changes in price between the three bonds?

c. Show the bond prices when the discount rate decreases by 0.5 percent from the discount rate in part

a. What do you notice about the price change between parts b and c?

A Settlement date 1 2 3 Maturity date 4 Coupon Rate 5 6 Interest rate (Yld) Redemption 11/15/2011 DATE(2011,11,15) 11/15/2026 DATE(2026,11,15) 5.50% 6.50% 100 2 E 7 8 Frequency 9 Bond price (per $100 par value) = 10 Bond price (per $1000 par value)= $90.51 PRICE(82,83,84,85,86,87,1) $905.09 -10*89