Today is $1/1/2022.$ 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 YTM $($yield to maturity$)$ for a $20-$year treasury bond is $1\mathrm{.}5\%.$

The bond face value is $$1,000.$ The coupon rate is $2\%$ 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.$

$1)$What is the price of the bond today? How many bond contracts $($$$1,000$ each contract$)$ can you purchase using the deposit of $$10$ billion?

$2)$The bank cannot afford to lose the value of their investment by $30\%($bond value becomes less than $$7$ billion$).$ At what market interest rate would the bond price lose $30\%$ of its value?

$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?

Between a and b$,$ which one makes more sense?

$4)$ Plot the price and the profit $($price $-$ cost$)$ from buying one share of bond against interest rate from $0\mathrm{.}5\%$ to $5\%,$ at $0\mathrm{.}5\%$ interval.

$5)$ Assume that you can buy a protective put for $$50,$ at the exercise price of $$1,000$ with the exercise date of Dec. $31,2023.$ Should you buy it$?$ Why or why not? If you decide to buy it$,$ how does that affect your profit at different interest rate from question $4?$

Please solve with excel, Thanks!