Spreadsheet$-$Related Problem$-$Bond Valuation

The problem requires you to use spreadsheet Case Study$3-$Excel File.xlsx to solve this problem.

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.

d$.$ Suppose that Jenna just bought a newly issued $15-$year bond with a coupon rate equal to $7$ percent. If

Jenna sells the bond at the end of the year when its market price is $$917,($i$)$ what would be the

bond's yield to maturity and $($ii$)$ what return would she earn? What portion of the return represents

capital gains and what portion represents the current yield? $($iii$)$ What return would the new investor

earn in the year after James sells the bond? Assume interest is paid semiannually.

e$.$ 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,($i$)$ what would be the bond's yield to maturity when he sells it and $($ii$)$ what return would

he earn during the time he held the bond? What portion of the return represents capital gains and