Please show Excel Fomula

A B D E F G H 1 1 You manage a pension fund that will provide retired workers with lifetime annuities. You expect a large number of workers to retire in 8 years. You have the following two bonds to invest in and immunize this portfolio. 2 3 Inputs Bond A 01-01-2020 01-01-2025 0.000 $1,000.00 Bond B 01-01-2020 01-01-2040 0.000 $1,000.00 4 5 Settlement Date Maturity Date 7 Coupon Rate Face Value 9 Coupons per Year 10 11 12 Time to Payout 13 Payout 14 15 16 17 18 Required: Obligation $50,000,000 Yield to Maturity 0.07501 A B D E 18 Required: Using any necessary data above, calculate the present value and Macaulay Duration of the obligation. Assume annual payments. Then calculate the Macaulay Duration and the Modified Duration for each bond. Use this 19 information to immunize the portfolio. 20 21 (Use cells A5 to C16 from the given information to complete this question.) 22 23 Obligation 24 Present Value of Obligation 25 Duration of Obligation 26 27 Bond A Bond B 28 Bond Price 29 Macaulay Duration 30 Weight to Immunize 100.00% 31 Dollars Invested 32 Number of Bonds Needed (Round to Nearest Dollar) 33 34 A B D E F G H 1 1 You manage a pension fund that will provide retired workers with lifetime annuities. You expect a large number of workers to retire in 8 years. You have the following two bonds to invest in and immunize this portfolio. 2 3 Inputs Bond A 01-01-2020 01-01-2025 0.000 $1,000.00 Bond B 01-01-2020 01-01-2040 0.000 $1,000.00 4 5 Settlement Date Maturity Date 7 Coupon Rate Face Value 9 Coupons per Year 10 11 12 Time to Payout 13 Payout 14 15 16 17 18 Required: Obligation $50,000,000 Yield to Maturity 0.07501 A B D E 18 Required: Using any necessary data above, calculate the present value and Macaulay Duration of the obligation. Assume annual payments. Then calculate the Macaulay Duration and the Modified Duration for each bond. Use this 19 information to immunize the portfolio. 20 21 (Use cells A5 to C16 from the given information to complete this question.) 22 23 Obligation 24 Present Value of Obligation 25 Duration of Obligation 26 27 Bond A Bond B 28 Bond Price 29 Macaulay Duration 30 Weight to Immunize 100.00% 31 Dollars Invested 32 Number of Bonds Needed (Round to Nearest Dollar) 33 34