Percentages need to be entered in decimal format, for instance 3% would be entered as .03.)

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%, a market rate (yield to maturity) of 10%, and it matures in 17 years. Interest is paid every six months; the next interest payment is scheduled six months from today.

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

What is the capital gains yield, current yield, and total yield that will be earned if the bond is held until it matures? Assume that the market rate does not change from now until maturity.

Suppose that Jenna wants to sell her bond seven years from today when 10 years remain until maturity. If the market rate is 8% at the time she sells the bond in seven years, for what price will Jenna be able to sell the bond? What would be the capital gains yield, current yield, and total yield that the new investor will earn if he/she holds the bond until it matures 10 years later?

Suppose that Jenna just bought a newly issued 15-year bond with a coupon rate equal to 7%. If Jenna sells the bond at the end of the year when the market price is $917, what would be the bond's yield to maturity? What return would she earn? What portion of the return represents capital gains and what portion represents the current yield?

Suppose that James just bought the same 15-year bond that Jenna bought and at the same time. If James sells his bond five years from the day he purchased it (with 10 years remaining to maturity) for $1,074, what would be the bond's yield to maturity when he sells it? What return would he earn during the time he held the bond? What portion of the return represents capital gains and what portion represents the current yield?

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, rd, 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 maturitues up to 20 years. 5925.51 INPUT DATA: Years remaining to maturity Coupon rate of interest Interest payments per year Market rate yield to maturity). Maturity value KEY OUTPUTS: Current price (PO) Capital gains yield this year Current yield this year Total yield this year 10% $1,000 10.01% End of Year Capital Gains Capital Gains Value (5) (5) Yield (%) Current Yield Total Yield Interest (5) $90 $90 MODEL-GENERATED DATA: Years to Beginning of Year Maturity Value (5) 20 19 18 17 16 15 14 $925.51 13 $928.12 12 $931.01 11 $934.18 10 $937.69 9 $941.55 8 $945.81 7 $950.51 6 $955. 68 5 $961.39 4 $967 68 3 $974.62 $98227 $990.70 0 $1,000.00 9.72% 9.70% 9.67% 9.63% 9.60% $928.12 $931.01 $934.18 $937.69 $941.55 $945.81 $950.51 5955 68 $961.39 $967.68 $974.62 $982.27 $990.70 $1,000.00 $2.61 $2.89 $3.17 $3.51 $3.86 $4.26 $4.70 55.17 $5.71 $6.29 $6.94 $7.65 $8.43 $9.30 0.28% 0.31% 0.34% 0.38% 0.415 0.45% 0.50% 0.54% 0.60% 0.65% 0.72% 0.78% 0.86% 0.94% $90 590 590 $90 $90 590 $90 $90 $90 $90 9.47% 10.01% 10.015 10.01% 10.01% 10.015 10.01% 10.015 10.015 10.01% 10.02% 10.02% 10.02% 10.02% $90 9.30% 9.23% 9.16% 9.08% $90 INPUT DATA: KEY OUTPUTS: Original maturity years) 15 Yields to new investor werk Coupon rate of interest 9% Yield to maurity, rd 9.00% Interest payments per year Market price one year after sale $1,000.00 Maturity value $1.000 Dollar capital gains next year 50.00 Market rate, YTM 10.00% Capital gains yield next year 0.00% Purchase price $923.14 Current yield next year Selling price $1,000.00 Total yield next year Number of years held 3 Yields to the original investor (Assumes the market rate, YTM, remains the same each year the bond is held as when the bond was purchased, except in the final year) #Years Bond Beginning of Year End of Year Capital Gains Capital Gains Current Yield Total Yield was Held Value (5) Value (5) (5) Yield) Interest (5) $923.14 $925.51 5237 0.26% $90 10.01% $925.51 5928.12 $2.61 0.28% $90 10.01% S928.12 $1,000.00 $71.88 7.74% S90 5 III Capital Gains Yield Current Yield 0 20 19 18 17 16 15 14 13 12 11 10 9 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, rd, 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 maturitues up to 20 years. 5925.51 INPUT DATA: Years remaining to maturity Coupon rate of interest Interest payments per year Market rate yield to maturity). Maturity value KEY OUTPUTS: Current price (PO) Capital gains yield this year Current yield this year Total yield this year 10% $1,000 10.01% End of Year Capital Gains Capital Gains Value (5) (5) Yield (%) Current Yield Total Yield Interest (5) $90 $90 MODEL-GENERATED DATA: Years to Beginning of Year Maturity Value (5) 20 19 18 17 16 15 14 $925.51 13 $928.12 12 $931.01 11 $934.18 10 $937.69 9 $941.55 8 $945.81 7 $950.51 6 $955. 68 5 $961.39 4 $967 68 3 $974.62 $98227 $990.70 0 $1,000.00 9.72% 9.70% 9.67% 9.63% 9.60% $928.12 $931.01 $934.18 $937.69 $941.55 $945.81 $950.51 5955 68 $961.39 $967.68 $974.62 $982.27 $990.70 $1,000.00 $2.61 $2.89 $3.17 $3.51 $3.86 $4.26 $4.70 55.17 $5.71 $6.29 $6.94 $7.65 $8.43 $9.30 0.28% 0.31% 0.34% 0.38% 0.415 0.45% 0.50% 0.54% 0.60% 0.65% 0.72% 0.78% 0.86% 0.94% $90 590 590 $90 $90 590 $90 $90 $90 $90 9.47% 10.01% 10.015 10.01% 10.01% 10.015 10.01% 10.015 10.015 10.01% 10.02% 10.02% 10.02% 10.02% $90 9.30% 9.23% 9.16% 9.08% $90 INPUT DATA: KEY OUTPUTS: Original maturity years) 15 Yields to new investor werk Coupon rate of interest 9% Yield to maurity, rd 9.00% Interest payments per year Market price one year after sale $1,000.00 Maturity value $1.000 Dollar capital gains next year 50.00 Market rate, YTM 10.00% Capital gains yield next year 0.00% Purchase price $923.14 Current yield next year Selling price $1,000.00 Total yield next year Number of years held 3 Yields to the original investor (Assumes the market rate, YTM, remains the same each year the bond is held as when the bond was purchased, except in the final year) #Years Bond Beginning of Year End of Year Capital Gains Capital Gains Current Yield Total Yield was Held Value (5) Value (5) (5) Yield) Interest (5) $923.14 $925.51 5237 0.26% $90 10.01% $925.51 5928.12 $2.61 0.28% $90 10.01% S928.12 $1,000.00 $71.88 7.74% S90 5 III Capital Gains Yield Current Yield 0 20 19 18 17 16 15 14 13 12 11 10 9