Do not have an Excel file!

2. Using the Excel file from the Options chapter, answer the following questions. (10 points) Assuming that the current Term Structure of yield to maturity for par bonds of a company is as follows: Year On-the-run 1 4.XX% 2 5.XX% 3 6.XX% where XX is the last 2-digit of your student D. So, if you student ID is for example 033389678, your starting data would be: Year On-the-run 1 4.78% 2 5.78% 3 6.78% a. Find the price of a 3-year, 8% annual coupon bond issued by that company. b. Goldman Sachs uses that fixed-rate coupon bond to create a floating-rate note with the following coupon: Current Spot Rate + 300 basis points. Assuming an interest-rate volatility of 10%, estimate the value of this Floating Rate Note. For this, you must 1) calibrate a binomial tree based on the given yields-to-maturity for the par bonds, 2.a.) use the future expected spot rates found in the tree to compute the actual coupon rates paid at reset at every node, that is, for example, at the Ri L node, you can set the coupon to be paid as RuL + 300 basis points to determine the coupon that will be paid at time 2. 2.b.) for future nodes that can be reached from 2 different prior nodes, the expected coupon paid is the weighted average of the coupon determined at prior reset time, 3) discount the present value of the cash flows of the bond as we did in the callable bond examples. c. The Fed decides to stop intervening and increasing the money supply and tightens interest rate. This causes an upward parallel shift in the term structure by 100 basis points. Establish the new price of the 3-year, 8% annual coupon bond issued by that company. d Compute the new price of the Floating Rate Note. You have to re-calibrate a new tree at the new interest rate level before you can price the bond as you did in part b. e. Using the following approximation of duration: (Price before Shock - Price after Shock) / (Price before + Price after)/2) * Change in Interest Rate), compute the estimated duration for both the fixed rate and the floating rate bond. Which of the two bonds is least sensitive to changes in interest rates? Why? 2. Using the Excel file from the Options chapter, answer the following questions. (10 points) Assuming that the current Term Structure of yield to maturity for par bonds of a company is as follows: Year On-the-run 1 4.XX% 2 5.XX% 3 6.XX% where XX is the last 2-digit of your student D. So, if you student ID is for example 033389678, your starting data would be: Year On-the-run 1 4.78% 2 5.78% 3 6.78% a. Find the price of a 3-year, 8% annual coupon bond issued by that company. b. Goldman Sachs uses that fixed-rate coupon bond to create a floating-rate note with the following coupon: Current Spot Rate + 300 basis points. Assuming an interest-rate volatility of 10%, estimate the value of this Floating Rate Note. For this, you must 1) calibrate a binomial tree based on the given yields-to-maturity for the par bonds, 2.a.) use the future expected spot rates found in the tree to compute the actual coupon rates paid at reset at every node, that is, for example, at the Ri L node, you can set the coupon to be paid as RuL + 300 basis points to determine the coupon that will be paid at time 2. 2.b.) for future nodes that can be reached from 2 different prior nodes, the expected coupon paid is the weighted average of the coupon determined at prior reset time, 3) discount the present value of the cash flows of the bond as we did in the callable bond examples. c. The Fed decides to stop intervening and increasing the money supply and tightens interest rate. This causes an upward parallel shift in the term structure by 100 basis points. Establish the new price of the 3-year, 8% annual coupon bond issued by that company. d Compute the new price of the Floating Rate Note. You have to re-calibrate a new tree at the new interest rate level before you can price the bond as you did in part b. e. Using the following approximation of duration: (Price before Shock - Price after Shock) / (Price before + Price after)/2) * Change in Interest Rate), compute the estimated duration for both the fixed rate and the floating rate bond. Which of the two bonds is least sensitive to changes in interest rates? Why