please help me, and try to explain especifically because i am real confused

ASSIGNMENT 3 - Bond Valuation 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. 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 AS Bond Valuation B D E H 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, rg, 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 C06 at the bottom of the 7 GRAPH worksheet. B 93. The model is set up so that you can solve for bonds with maturitues up to 20 years. 10 G $1,584 48 -1.31% 6.31% 5.00% Yield (%) Current Yield Total Yield (%) (%) D 14 INPUT DATA: KEY OUTPUTS: 15 16 Years remaining to maturity 18 Current price (P.) 17 Coupon rate of interest 10% 18 Interest payments per year Capital gains yield this year 1 19 Market rate (yield to maturity), T. Current yield this year 5% Total yield this year 20 Maturity value $1,000 21 22 23 MODEL-GENERATED DATA 24 Years to Beginning of Year End of Year Capital Gains Capital Gains 25 Maturity Value (S) Value ($) 26 Interest ($) 20 27 19 28 18 $1,584 48 $1,563.70 ($20 78) -1.31% $100 29 17 $1,563.70 $1,541.89 ($21.81) -1.39% $100 30 16 $1,541.89 $1,518.98 ($22.91) -1.49% $100 31 15 $1,518.98 $1,494.93 ($2405) -1.58% $100 32 14 $1,494.93 $1,469.68 ($25.25) -1.69% $100 33 13 $1,469.68 $1,443.16 ($26.52) -1.80% $100 34 12 $1,443.16 $1,415,32 ($27 84) -1.93% $100 35 11 $1,415.32 $1,386.09 ($29.23) -2.07% $100 36 10 $1,386.09 $1,355,39 ($30.70) -2 21% $100 37 9 $1,355,39 $1,323 16 ($32 23) -2.38% $100 38 8 $1,323.16 $1,289.32 (53384) -2.56% $100 39 7 $1,289.32 $1,253.78 ($35.54) -2.76% $100 40 6 $1,253.78 $1,216 47 ($37.31) -2.98% $100 41 5 $1,216.47 $1,177.30 ($39.17) -3.22% $100 42 4 $1,17730 $1,136 16 ($41.14) 3.49% $100 43 3 $1,136.16 $1,092.97 (S43.19) 3.80% $100 44 2 $1,092.97 $1,047 62 ($45.35) 4.15% $100 45 1 $1,047.62 $1,000.00 (547 62) 4.55% $100 46 0 $1,000.00 47 C06 GRAPH 1 INSTRUCTIONS 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.009 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% N . . n . ASSIGNMENT 3 - Bond Valuation 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. 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 AS Bond Valuation B D E H 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, rg, 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 C06 at the bottom of the 7 GRAPH worksheet. B 93. The model is set up so that you can solve for bonds with maturitues up to 20 years. 10 G $1,584 48 -1.31% 6.31% 5.00% Yield (%) Current Yield Total Yield (%) (%) D 14 INPUT DATA: KEY OUTPUTS: 15 16 Years remaining to maturity 18 Current price (P.) 17 Coupon rate of interest 10% 18 Interest payments per year Capital gains yield this year 1 19 Market rate (yield to maturity), T. Current yield this year 5% Total yield this year 20 Maturity value $1,000 21 22 23 MODEL-GENERATED DATA 24 Years to Beginning of Year End of Year Capital Gains Capital Gains 25 Maturity Value (S) Value ($) 26 Interest ($) 20 27 19 28 18 $1,584 48 $1,563.70 ($20 78) -1.31% $100 29 17 $1,563.70 $1,541.89 ($21.81) -1.39% $100 30 16 $1,541.89 $1,518.98 ($22.91) -1.49% $100 31 15 $1,518.98 $1,494.93 ($2405) -1.58% $100 32 14 $1,494.93 $1,469.68 ($25.25) -1.69% $100 33 13 $1,469.68 $1,443.16 ($26.52) -1.80% $100 34 12 $1,443.16 $1,415,32 ($27 84) -1.93% $100 35 11 $1,415.32 $1,386.09 ($29.23) -2.07% $100 36 10 $1,386.09 $1,355,39 ($30.70) -2 21% $100 37 9 $1,355,39 $1,323 16 ($32 23) -2.38% $100 38 8 $1,323.16 $1,289.32 (53384) -2.56% $100 39 7 $1,289.32 $1,253.78 ($35.54) -2.76% $100 40 6 $1,253.78 $1,216 47 ($37.31) -2.98% $100 41 5 $1,216.47 $1,177.30 ($39.17) -3.22% $100 42 4 $1,17730 $1,136 16 ($41.14) 3.49% $100 43 3 $1,136.16 $1,092.97 (S43.19) 3.80% $100 44 2 $1,092.97 $1,047 62 ($45.35) 4.15% $100 45 1 $1,047.62 $1,000.00 (547 62) 4.55% $100 46 0 $1,000.00 47 C06 GRAPH 1 INSTRUCTIONS 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.009 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% N . . n