Immunization, Duration, and Convexity Project

Please use Excel to complete the problem below. ( file is attached ..fill in the answers in the excel file )

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 $12.4 billion, which your company will need to repay in 10 years.The market interest rate is 7.8%, so the present value of this obligation is $5,851,053,601.You decide to fund the obligation using

five-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 7.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 and Convexity

1.What is the duration of a 7-year, 6.9% semi-annual bond if the market rate on bonds of similar quality is 5.1%?

2.Now suppose that the YTM has changed to 5.11%%.Using Macaulay duration (which is practically the same as using modified duration since you need to divide by (1+y)), what is the approximate percent change in the price of the bond?(You do not need to recalculate Macaulay duration using 5.11%.Use the duration value that you found in Problem 1.)

3.Now include convexity to estimate the percent change in the price of the bond.