Please use Excel to complete the problem below.

Immunization

Your company has just built and patented a laser defense system and is planning on installing the equipment in various locations around the country. To finance the installations, you, the CFO, have borrowed $1 billion, which your company will need to repay in five years. The market interest rate is 8%, so the present value of this obligation is $680,583,197. You decide to fund the obligation using three-year zero-coupon bonds and perpetuities that make annual coupon payments.

1. How can you immunize the obligation? (Here, you need to construct an immunized portfolio that consists of the zero-coupon bonds and the perpetuities.)

2. Now suppose that one year has passed and that the market rate is still 8%. You need to ensure that the obligation is still fully-funded and immunized. Is the obligation still fully-funded and immunized? If not, what do you need to do to fully fund and immunized the obligation?

Duration

1. What is the duration of a 10-year, 6% bond if the market rate on bonds of similar quality is 5.8%?

2. Now suppose that the yield to maturity has changed to 5.81%. Using Macaulay duration, what is the approximate percent change in the price of the bond? (You do not need to recalculate Macaulay duration using 5.81%. Use the duration value that you found in Problem 1.)

3. Using convexity, what is the approximate percent change in the price of the bond?