A company wants to fund the severance package for four employees, aged 50, 53, 57, and 60. Under the package, the company pays$7,500 per year, at the end of each year, up to and including at age 65, plus a lump-sum payment of$50,000 at age 65. For simplicity, assume that the severance package begins today and the employees' ages are exact.

To immunize the liabilities, the company purchases an investment portfolio consisting of two zero coupon bonds, due at timest1andt2, as measured from today. The term structure of interest rates is flat and all zero rates are 7%, expressed with annual compounding. For each of the following pairs oft1andt2, determine the par amounts of zero coupon bonds that must be purchased to match both the present value and duration of the liabilities. Afterwards, determine whether the asset-liability portfolio is Redington immunized by checking the immunization conditions.

(a)t1=0,t2=20; (b)t1=5,t2=10.

For each strategy, produce a chart of the surplus/deficit function for interest rates ranging from 0% to 20%, in increments of 50 bps. Calculate the chart data in a simple, efficient manner.

Please answer using excel, thank you!