please solve the following two questions in Excel step-by-step and also show the embedded functions in excel so I can understand how it was solved.

the partial solution has been attached. please use that to get the correct ansswer!

1. You attempt to finance a 10 -year guaranteed investment contract (GIC). The payment made by policyholder is $100 million. Suppose the promised annual interest rate of the GIC is 5%. The bond you want to use is a 15 -year, 5% annual coupon rate bond (semiannually paid). Its face value is $100 million and you bought at par. Suppose the new interest rate in the life of GIC stay constantly at 6%. Answer the following questions: (1). What is the promised payment of the GIC? (2). Calculate the total return of the investment. Key: bond equivalent yield =4.94% 2. The market value of fund assets is $100,000 and the projected value of liabilities is 200,000 . The asset and liability modified durations are 12 and 10 years. Evaluate the funding gap interest rate risk of the firm. Key: Funding gap interest rate risk =8,000 1. You attempt to finance a 10-year guaranteed investment contract (GIC). The payment made by policyholder is $100 million. Suppose the promised annual interest rate of the GIC is 5%. The bond you want to use is a 15 -year, 5% annual coupon rate bond (semiannually paid). Its face value is $100 million and you bought at par. Suppose the new interest rate in the life of GIC stay constantly at 6%. Answer the following questions: (1). What is the promised payment of the GIC? (2). Calculate the total return of the investment. Key: (1) promised payment =100,000,000(1+?)20=163,861,644 (2) step 1: get total future value of all the coupon payments: N=20;I/Y=3;PV=0;PMT=2,500,000;FV=?Weget?? Step 2: resale value of the bond at the end of year 10 N=10;I/Y=3;PV=2;PMT=2,500,000;FV=100,000,000 We get ?? Step 3: sum up the numbers from steps 1 and 2: 162,910,835 Step 4: get semiannual return =(??/100,000,000)(1/?)1=2.47% Step 5: obtain the bond equivalent yield =2?=?2% 2. The market value of fund assets is $100,000 and the projected value of liabilities is 200,000 . The asset and liability modified durations are 12 and 10 years. Evaluate the funding gap interest rate risk of the firm. Key: Assume interest rate increases by 1%. Change in projected liability =101%200,000= ? Change in assets =121%100,000= ? Funding gap interest rate risk =??=8,000