please help answer part A,b and c

The problem requires you to use the spreadsheet template attached in the assignment instructions. 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. 2. 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 we can does not change from the time Jenna sells the bond until it matures. B C D E 1 Chapter 6 Spreadsheet Problem Solutions (C06) 2 3 Bond Valuation 4 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 5 these instructions now. To read these instructions, click on the worksheet labeled INSTRUCTIONS 6 2. A graph that shows the composition of the total yield.ro, 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 7 GRAPH worksheet. 8 9 3. The model is set up so that you can solve for bonds with maturitues up to 20 years 10 11 12 PARTS a-c 13 14 INPUT DATA: KEY OUTPUTS: 15 16 Years remaining to maturity 18 Current price (P.) $1,584.48 17 Coupon rate of interest 10% Capital gains yield this year -1.31% 18 Interest payments per year 1 Current yield this year 6.31% 19 Market rate (yield to maturity), 5% Total yield this year 5.00% 20 Maturity value $1,000 21 G Yield (%) Interest ($) Current Yield Total Yield (%) (%) B E 2 3 MODEL-GENERATED DATA: 24 Years to Beginning of End of Year Capital Gains Capital Gains 25 Maturity Year Value ($) Value ($) (S) 26 20 27 19 28 18 $1,584.48 $1,563.70 ($20.78) -1.31% 29 17 $1,563.70 $1,541.89 ($21.81) -1.39% 30 16 $1,541.89 $1,518.98 ($22.91) -1.49% 31 15 $1,518.98 $1,494.93 ($24.05) -1.58% 32 14 $1.494.93 $1,469.68 ($25.25) -1.69% 33 13 $1,469.68 $1,443.16 ($26.52) -1.80% 34 12 $1,443.16 $1,415.32 ($27.84) -1.93% 35 11 $1,415.32 $1,386.09 ($29.23) -2.07% 36 10 $1,386.09 $1,355.39 ($30.70) -2.21% 37 $1,355.39 $1,323.16 ($32.23) -2.38% 38 8 $1,323.16 $1,289.32 ($33.84) -2.56% 39 7 $1,289.32 $1.253.78 ($35.54) -2.76% 40 6 $1,253.78 $1,216.47 ($37.31) -2.98% 41 5 $1,216.47 $1.177.30 ($39.17) -3.22% 42 4 $1,177.30 $1,136.16 ($41.14) -3.49% 43 3 $1,136.16 $1.092.97 ($43.19) -3.80% 44 2 $1,092.97 $1,047.62 ($45.35) 4.15% 45 1 $1,047.62 $1,000.00 ($47.62) -4.55% 46 $1,000.00 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 6.31% 6.40% 6.49% 6.58% 6.69% 6.80% 6.93% 7.07% 7.21% 7,38% 7.56% 7.76% 7.98% 8.22% 8.49% 8.80% 9.15% 9.55% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% AZ C06 GRAPH INSTRUCTIONS