You have been hired as a consultant by Fulton Life Insurance Company. Fulton specializes in the sale of Guaranteed Investment Contracts $($GICs$)$ which guarantee clients a specific dollar amount on a specific date in return for either a single up$-$front premium payment or a series of monthly payments.

Fulton has just sold a GIC which guarantees a payout of $$1\mathrm{.}52$ million on January $1,2033.$ Fulton needs to purchase a high quality bond that will assure them of having the $$1\mathrm{.}52$ million on $1/1/33.$ They are willing to spend up to $$950,000$ on a bond to fund this GIC, and plan to buy it today, January $1,2023.$

Currently, the yield curve is flat so that the YTM of every bond with the credit$-$quality Fulton is considering is currently $5\mathrm{.}00\%.$ You expect the yield curve to remain flat for at least the next $15$ years, even though interest rates could go up or go down during that time.

Your job is to recommend a bond to purchase which will assure Fulton of having the necessary $$1\mathrm{.}52$ million in $10$ years no matter how high or low interest rates might go between now and then. This is the only objective $-$ make sure that the investment will grow to at least $$1\mathrm{.}52$ million on January $1,2033.$

Fulton has asked you to consider the following three corporate bonds that meet their standards for credit quality and liquidity. Each bond makes semi$-$annual coupon payments.

Bond #$1$

Face Value: $$1$ million

Coupon Rate: $4\mathrm{.}25\%$

Maturity: January $1,2033$

Bond #$2$

Face Value: $$1$ million

Coupon Rate: $4\mathrm{.}25\%$

Maturity: January $1,2036$

Bond #$3$

Face Value: $$1$ million

Coupon Rate: $4\mathrm{.}375\%$

Maturity: January $1,2038$

Please answer the following in Excel:

Find the current price for each bond and ensure that it is less than $$950,000$

Calculate the duration of each bond

For each bond, determine how much money you will have on January $1,2033$ under each of the following three scenarios:

Interest rates stay at $5\%(2\mathrm{.}5\%$ semiannually$)$ for at least the next $15$ years

Tomorrow, after you have purchased the bond, interest rates immediately go up to $6\%$ and stay there for the next $15$ years

Tomorrow, after you have purchased the bond, interest rates immediately go down to $4\%$ and stay there for the next $15$ years

Make note of the fact that Bond #$1$ matures when you need the $$1\mathrm{.}52$ million, but Bond #$2$ and Bond #$3$ must be sold prior to maturity $($sold on $1/1/33).$ Of course, they will sell for the present value of their remaining cash flows at that time.

Also note that all coupon payments will be reinvested at the prevailing interest rate when they are received.

So$,$ for each bond, on $1/1/33,$ you will have the future value of the reinvested coupon payments plus either the face value of the bond $($#$1)$ or the sale price of the bond $($#$2$ and #$3).$

Present a recommendation of which of the three bonds Fulton should buy, and why.

Explain how an understanding of duration allows you to pick the correct bond without doing any of these calculations.

The correct bond will provide Fulton with more than the needed $$1\mathrm{.}52$ million if interest rates go up or if they go down. Explain what property of this $($correct$)$ bond causes this and calculate the value of that property for both the bond and the GIC to prove it$.$