Today is 1/1/2022. The YTM (yield to maturity) for a 20-year treasury bond is 1.5%. You are an analyst at S bank and it is your job to determine the investment of the deposit the bank just received. One option is to invest $10 billion in 20-year treasury bonds. The bond face value is $1,000. The coupon rate is 1.5% and the coupon is paid twice a year, on 6/30 and 12/31. The maturity date for the 20-year treasury bond is 12/31/2051.

3. Given the possibility of interest rate increases, you are tasked with conducting a sensitivity analysis with potential interest rate changes. Using the average and standard deviation of the daily interest rate for 20-year treasury bonds from the attached file, you are asked to assess the probability that the bank will fail (lose over 30% of bond value).

a. At first, you decide to use the average and standard deviation values from the daily interest rate in 2021. What is the probability of bank failure after you run 1,000 simulations?

b. Then, you decide to use the data since 2000 (1/3/2000-12/31/2021). Does the result change?

c. Between a and b, which one makes more sense?

Please solve in excel