Using the Excel file from the Options chapter, answer the following questions.

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%

your starting data would be:

Year	On-the-run
1	4.40%
2	5.40%
3	6.40%

1. Find the price of a 3-year, 8% annual coupon bond issued by that company. (5 points)
2. 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 will have to 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 R_1,L node, you can set the coupon to be paid as R_1,L + 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. (30 points)
3. 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. (10 points)
4. 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. (10 points)
5. Using the following approximation of duration: (Price after Shock Price before 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? (10 points)

i believe we yse the tree diagram to do question and change the numbers. i cant upload excel file specifically.

chegg doesnt allow you to upload excel files so i had to take a picture. i just need the tree diagrams of each year to show caliberation of riding yield

Tools Home Data Insert Format Autofave Insert Draw Page Layout Formulas Calibri (Body 11 A A 0 BTUE AA E x f x Assuming Annual Bonds Data Window Help Chapter 40 - Options Review View XLSTAT365-Freemium 19 General 0 3 - $ % 8-8 3. Formatting as Table Styles Conditional Formu Cut delete Format X -Z Sorte A1 1 Assuming Annual Bonds On-the-run Yield Curve Years YTM 1.0 2.0 4.00 5.00 3.00 Theoretical Spot & Forward Rates Years Spot Rate Forward Rate 1.0 3.000 3.000 2.0 4.020 5.051 3.0 5.069 7.198 OPTION FREE Value of a 6% 3-Yr Coupon Bond Years CF PV $60.00 $58.25 $60.00 $55.45 3.0 $1,060.00 $913.87 Bond Value $1,027.57 20 The Yield Curve Calibrating the Lattice Vuing Option Free with Too l ing Calable with Tree OAS + 11 A A 25 Calibri (Budy) BTU L Currency $ % Paste A 53848 Conditional Format Formatting as Table Cell Styles P B39 fx 1000 J K L M N O P On-the-run Yield Curve Value of a 4.00% 2. Coupon Bond Year $110.00 $0.00 Bond Value $961.17 $0.00 $1,000.00 11 2-Year and Characteristic $1.000.00 11 Par 14 Volty 15 RIL 1 Year Bond Chance 100000 19 Par Voy 41 RL 6.000 The Yold Curve Calibrating the Lattice V aluing Option-Free with Tree V aluing Callable with Treo OAS + LOG TU T Data Review View XLSTAT365-Freemium U Page Layout Formulas Calibri (Bodyl 11 AA BTU A fx 6% 2 Paste c Egy Percentage $ -% - Condition Formatting 22-0. form Table Styles Delete Format OR OLECKING THAT THE CALIBRATION IS ACCURATE Nop Bond Coupon 6.00% Par $1.000.00 100% Volatility Maturity C R3,HHH $1.000.00 $60.00 $972.91 560.00 8.951% $1,000.00 $60.00 C R3 HAL Today $0.00 3000 $997.62 $60.00 R2 HL7 328% $1,000.00 $60.00 $60.00 V $1,000.00 $60.00 RALL Next, compare the price of a 2-Year 3x Coupon as per the tree and the forward rates, le, without volatility allowed. The Yield Curve Bernina in the tree that year and is with Calibrating the lattice valuing Option-Free with Tree v aluing Collable with Tree OAS + 1990 300109 - 10IWA Xw G 9 RE Ullu t ter 40 - Options Review View KLSTAT365-Freemium th Draw Page Layout Formulas Data Calibri (Body) 11 -A A = BIUD A f x Valuing Option Free with Tree 1013 D Currency $ % F23 x 848 Conditions Format Formatting Table Styles Cel Format X Sorte Bond Coupon 6.00% Volatility Maturity $1,000.00 100% V $1,000.00 $60.00 Callable in $972.91 $60.00 1R2NH3.9515 V C RIH $988.93 $60.00 5191% C $1,000.00 $60.00 5987 62 1,023.75 $0.00 3.000% V C RIL $1,000.00 $60.00 4,250% $1,000.00 $60.00 $1,000.00 The Callable Value is The Non-Callable value is The value of the Call Option is $1.023.75 $0.00 v Valuing Option-Free with Tree aluing Cotable with Tree O Calibrating the Lattice AS + The Yod Curve Calibri (Body BIU General 111111111 $ 96 8 Insert 2x Delete Format Conditional Format Formatting as Table X V for Cell Styles Coupon $1,000.00 10.08 Maturity Callable in OAS 0.14% 3 4.16 CAS in bp $1,000.00 $1,020.78 $0.00 V $984.49 HLC $60.00 A HL7670% 1,000.00 1111 SCO DONNA 45928 C S60,00 $786.79 C $60.00 R213421 $1,000.00 $60 00 The Yield Curve Vakiing Callable with Tree OAS + Tools Home Data Insert Format Autofave Insert Draw Page Layout Formulas Calibri (Body 11 A A 0 BTUE AA E x f x Assuming Annual Bonds Data Window Help Chapter 40 - Options Review View XLSTAT365-Freemium 19 General 0 3 - $ % 8-8 3. Formatting as Table Styles Conditional Formu Cut delete Format X -Z Sorte A1 1 Assuming Annual Bonds On-the-run Yield Curve Years YTM 1.0 2.0 4.00 5.00 3.00 Theoretical Spot & Forward Rates Years Spot Rate Forward Rate 1.0 3.000 3.000 2.0 4.020 5.051 3.0 5.069 7.198 OPTION FREE Value of a 6% 3-Yr Coupon Bond Years CF PV $60.00 $58.25 $60.00 $55.45 3.0 $1,060.00 $913.87 Bond Value $1,027.57 20 The Yield Curve Calibrating the Lattice Vuing Option Free with Too l ing Calable with Tree OAS + 11 A A 25 Calibri (Budy) BTU L Currency $ % Paste A 53848 Conditional Format Formatting as Table Cell Styles P B39 fx 1000 J K L M N O P On-the-run Yield Curve Value of a 4.00% 2. Coupon Bond Year $110.00 $0.00 Bond Value $961.17 $0.00 $1,000.00 11 2-Year and Characteristic $1.000.00 11 Par 14 Volty 15 RIL 1 Year Bond Chance 100000 19 Par Voy 41 RL 6.000 The Yold Curve Calibrating the Lattice V aluing Option-Free with Tree V aluing Callable with Treo OAS + LOG TU T Data Review View XLSTAT365-Freemium U Page Layout Formulas Calibri (Bodyl 11 AA BTU A fx 6% 2 Paste c Egy Percentage $ -% - Condition Formatting 22-0. form Table Styles Delete Format OR OLECKING THAT THE CALIBRATION IS ACCURATE Nop Bond Coupon 6.00% Par $1.000.00 100% Volatility Maturity C R3,HHH $1.000.00 $60.00 $972.91 560.00 8.951% $1,000.00 $60.00 C R3 HAL Today $0.00 3000 $997.62 $60.00 R2 HL7 328% $1,000.00 $60.00 $60.00 V $1,000.00 $60.00 RALL Next, compare the price of a 2-Year 3x Coupon as per the tree and the forward rates, le, without volatility allowed. The Yield Curve Bernina in the tree that year and is with Calibrating the lattice valuing Option-Free with Tree v aluing Collable with Tree OAS + 1990 300109 - 10IWA Xw G 9 RE Ullu t ter 40 - Options Review View KLSTAT365-Freemium th Draw Page Layout Formulas Data Calibri (Body) 11 -A A = BIUD A f x Valuing Option Free with Tree 1013 D Currency $ % F23 x 848 Conditions Format Formatting Table Styles Cel Format X Sorte Bond Coupon 6.00% Volatility Maturity $1,000.00 100% V $1,000.00 $60.00 Callable in $972.91 $60.00 1R2NH3.9515 V C RIH $988.93 $60.00 5191% C $1,000.00 $60.00 5987 62 1,023.75 $0.00 3.000% V C RIL $1,000.00 $60.00 4,250% $1,000.00 $60.00 $1,000.00 The Callable Value is The Non-Callable value is The value of the Call Option is $1.023.75 $0.00 v Valuing Option-Free with Tree aluing Cotable with Tree O Calibrating the Lattice AS + The Yod Curve Calibri (Body BIU General 111111111 $ 96 8 Insert 2x Delete Format Conditional Format Formatting as Table X V for Cell Styles Coupon $1,000.00 10.08 Maturity Callable in OAS 0.14% 3 4.16 CAS in bp $1,000.00 $1,020.78 $0.00 V $984.49 HLC $60.00 A HL7670% 1,000.00 1111 SCO DONNA 45928 C S60,00 $786.79 C $60.00 R213421 $1,000.00 $60 00 The Yield Curve Vakiing Callable with Tree OAS +