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.

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?

Please Solve in Excel