In: Finance
Suppose that a company has a series of liabilities with the due dates as follows:: 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. 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 (semi-annual) coupon payments per year. The yield to maturity on all bonds is 1.45%. How many of each of these bonds the company should buy to be fully funded and be immunized against interest rate risk? please step by step in excel!