Chapter 6 Spreadsheet Problem

Bond Valuation

The problem requires you to use File C06 on the computer problem spreadsheet.

Jenna bought a bond that was issued by Sherlock Watson Industries (SWI) three years ago. The bond has a $1,000 maturity value, a coupon rate equal to 9 percent, and it matures in 17 years. Interest is paid every six months; the next interest payment is scheduled for six months from today.

a. If the yield on similar risk investments is 11 percent, what is the current market value (price) of the bond?

b. Compute the capital gains yield, current yield, and total yield that Jenna will earn if she holds the bond until it matures. Assume that the market rate does not change from now until maturity.

c. Suppose that Jenna decides she wants to sell the bond seven years from today when 10 years remain until maturity. If the market rate is 8 percent at the time she sells the bond in seven years, for what price will Jenna be able to sell the bond? Compute the capital gains yield, current yield, and total yield that the new investor will earn if he or she holds the bond until it matures 10 years later. Explain why the capital gains yield is negative each year to maturity. Assume that the market rate does not change from the time Jenna sells the bond until it matures.

Chapter & Spreadsheet Proven Solutions / CAF] Bond Valuation 1. There are a number of instructions with which you should be familiar to use these computerized models. These instructions appear in a separate worksheet labeled INSTRUCTIONS. If you have not already done so, you should read these instructions now. To read these instructions, click on the worksheet labeled INSTRUCTIONS. 2. A graph that shows the composition of the total yield, m, can be displayed if you click the worksheet labeled GRAPH at the bottom of this spreadsheet. To return to this worksheet, click on the worksheet labeled CO6 at the bottom of the GRAPH worksheet. 3. The model is set up so that you can solve for bonds with maturities up to 20 years. PARTS a - C INPUT DATA: KEY OUTPUTS: Years remaining to maturity 18 Current price (P.) $1,584.48 Coupon rate of interest 10% Capital gains yield this year -1.31% Interest payments per year Current yield this year 6.31% Market rate (yield to maturity), r 5% Total yield this year 5.00% Maturity value $1,000 MODEL-GENERATED DATA: Years to Beginning of End of Year Capital Gains Capital Current Yield Total Meturito Year Value Itl Value Iti ($) Gains Yield Interest :] Yielddzi 20 19 18 $1,584.48 $1,563.70 ($20.78] -1.31% $100 6.31% 5.00% 17 $1,563.70 $1,541.89 16 ($ 21.81) -1.39% $100 6.40% 5.00% 15 $1,541.89 $1,518.98 ($22.91) -1.49% $100 6.49% 5.00% $1,518.98 $1,494.93 ($24.05) -1.58% $100 6.58% 5.00% 14 $1,494.93 $1,469.68 ($25.25) -1.69% $100 6.69% 5.00% 13 $1,469.68 $1,443.16 ($26.52] -1.80% $100 6.80% 5.00% 12 $1,443.16 $1,415.32 ($27.84) -1.93% $100 6.93% 5.00% $1,415.32 $1,386.09 2.07% $100 7.07% 10 5.00% $1,386.09 ($29.23] $1,355.39 ($30.70] -2.21% $100 7.21% 5.00% $1,355.39 $1,323.16 -2.38% $100 7.38% 5.00% $1,323.16 ($32.23) $1,289.32 ($33.84] -2.56% $100 7.56% 5.00% $1,289.32 $1,253.78 ($35.54] -2.76% $100 7.76% 5.00% $1,253.78 $1,216.47 ($37.31) -2.98% $100 7.98% 5.00% $1,216.47 $1,177.30 $1,177.30 ($39.17] -3.22% $100 8.22% 5.00% $1,136.16 ($41.14] -3.49% $100 8.49% 5.00% $1,136.16 $1,092.97 ($43.19) -3.80% $100 8.80% 5.00% $1,092.97 $1,047.62 ($45.35) -4.15% $100 9.15% 5.00% $1,047.62 $1,000.00 ($47.62) -4.55% $100 9.55% 5.00% $1,000.00 PARTS die NPUT DATA: KEY OUTPUTS: Original maturity [years) 10 Yield to maturity, Is 7.00% Coupon rate of interest 6% nterest payments per year 2 Maturity value $1,000 Purchase price $929.00 Number of years held 2