1. You graduated from USF and got a job at MetLife pension department. Your supervisor needs your help with some of its liabilities and risk control. The pension fund has a series of liabilities to be paid to the pension plan beneficiaries:
In 6 months: $2,000,000,
In 1 year: $2,200,000,
In 1.5 years: $2,500,000,
In 2 years: $3,200,000,
In 2.5 years: $3,700,000,
In 3 years: $4,300,000,
In 3.5 years: $4,700,000,
In 4 years: $5,100,000.
Your company wishes to construct a portfolio of assets to cover this series of liabilities, such that it is immunized against interest rate risk right now. The company is considering investing in four different bonds: (1) a 1-year Treasury Bill with a face value of $1,000 and no coupon, (2) a 2-year Treasury note with a face value of $1,000 and an annual coupon rate of 1.5%, (3) a 3-year Treasury note with a face value of $1,000 and an annual coupon rate of 1.90%, and (4) a 5-year Treasury note with a face value of $1,000 and an annual coupon rate of 2.30%. All Treasury notes make 2 (semiannual) coupon payments per year. The current yield on all bonds is 1.45%. Your supervisor wants you to find out how many of each of these four Treasury bonds the fund should buy to fully fund the liability and be immunized against interest rate risk right now? please show excel formulas as well