BELOW IS THE PROBLEM I HAVE TO COMPLETE, I WOULD LIKE SOME HELP GETTING STARTED, HAVE TO USE EXCEL TO SOLVE, I AM VERY LOST ON HOW TO DO THIS:

Q1.Immunization of a Portfolio to satisfy a single liability: Bond Analysis.

The concept of immunization was pioneered by F.M. Reddington (1952), who defined immunization as the investment of the assets in such a way that the existing business is immune to a general change in the rate of interest. As an example of the principles underlying the immunization of a portfolio against expected changes in interest rate;

Consider a life insurance company that sells a guaranteed investment contract (GIC) that guarantees an interest rate of 6.25% every 6 months (12.5% on a bond equivalent yield (BEY) basis) for 5.5 years. Suppose that the policyholder (the manager of an employee retirement fund, i.e., a pension fund company) made a payment of $8,820,262. The value that the life insurance company has guaranteed is:

$17,183,033 = $8,820,262 x (1.0625)¹¹

The portfolio manager of the life insurance company must now invest the principal ($8,820,262) with the goal of realizing the promised amount at the agreed time period. S/he, the portfolio manager, has select 4 bonds from a bond universe that could likely fund this liability of

$17, 183,033 at the end of 5.5 years.

Bond Option A: Buy a 12.5% coupon bond that matures in 5.5 years when the markets yield is 12.5%. If market yield changes just after the bond was issued and stays at the new level for the remainder of the 5.5 years.

Bond Option B: Buy a 15-year, 12.5% coupon bond selling at par to yield 12.5%. Assume same condition as option A with respect to market yield.

Bond Option C: Buy a 12.5% coupon bond with 6 months remaining to maturity selling at par. However, the investment horizon remains at 5.5 years. Thus, all proceeds from the bond at maturity will be reinvested at the rate of the new yield. Assume same condition as option A with respect to market yield.

Bond Option D: Buy a 10.125% coupon bond yielding 12.5% with 8 years remaining to maturity and par of $10,000,000. Assume same condition as option A with respect to market yield.

What bond would the portfolio manager purchase to assure that the target value is attained (i.e., fund the liability) regardless of whether market yield rises or falls? Assuming interest rates could rise to 16% from 12.5% or fall to 5% also from 12.5%. Hint: increase/decrease interest rate by 50 basis-points, that is 0.5%.