Using Excel Spreadsheet

6. An FI has a $100 million portfolio of six-year Eurodollar bonds that have an 8 percent coupon. The bonds are trading at par and have a duration of five years. The FI wishes to hedge the portfolio with T-bond options that have a delta of 0.625. The underlying long-term Treasury bonds for the option have a duration of 10.1 years and trade at a market value of $96,157 per $100,000 of par value. Each put option has a premium of 3.25 (percent of $100,000). (LG 23-4)

   1. How many bond put options are necessary to hedge the bond portfolio?

   2. If interest rates increase 100 basis points, what is the expected gain or loss on the put option hedge?

   3. What is the expected change in market value on the bond portfolio?

   4. How far must interest rates move before the payoff on the hedge will exactly offset the cost of placing the hedge?

   5. How far must interest rates move before the gain on the bond portfolio will exactly offset the cost of placing the hedge?

Please show formulas in excel