Using excel and create a tree . i tried uploading excel but can only upload pictures of what the tree should look like.

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

Find the price of a 3-year, 8% annual coupon bond issued by that company. (5 points)

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 R1,L node, you can set the coupon to be paid as R1,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)

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)

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)

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)

AutoSave Insert Draw b Page Layout u Formulas 3 Home Chapter 40 - Options XLSTAT365-Freemium Data Review View E E 29 P EI General $ % 78 -98 nert Delete Format .C Conditional Format Formatting as Table Cell Styles -2 son Filer X and Seles Calibri (Body) 11 A A Paste BTUEAA A1 | x f x Assuming Annual Bonds A B C 1 Assuming Annual Bonds D On-the-run Yield Curve Years YTM 3.00 4.00 3.0 5.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 PV 1.0 $60.00 $58.25 2.0 $60.00 $55.45 3.0 $1,060.00 $913.87 Bond Value $1,027.57 2.0 v lig ble with the CAS The Vield Curve Vahing Option.Free with Tre Cabrating the attice 29 Paste 839 Calibri (Body) 11 A A B IUDA E fx 1000 Currency $ % . 8 9 Conditional Format Formatting as Table Cell Styles Delete Format X x On the run Ye Curve V S100.00 F CS Par $1.000.00 41 R26 .000 The Yield Curve Calibrating thegatti Option 12 Tree V aluing Callable with Tree OAS + 11 A A E 19 Percentage $ % 3 3 LU Paste c3 A B Calibri (Body) BLUE x fx 6% co & WEX Conditional Format Formatting Table Delete Format Cell Styles OR CHECKING THAT THE CALIBRATION IS ACCURATE Bond Coupon 6.00% $1,000.00 10.0% 3 Volatility Maturity R3,HHH C R2 HH $972.91 $60.00 8-951% $988.93 V $1, HHL CS R3,HHL $987.62 $60.00 7328% R2,HL $60.00 4.250 V HLLC R3HLL S1. $6 000% V $1.00 $60 LLLC R3L Next, compare the price of a 2 Year 3x Coupon as per the tree and the forward rates, Le., without volatility allowed. $ % F23 A x fx Valuing Option Free with Tree'1013 Conditional Format Formatting Table Cor Styles Format B MN OR THE PAY-OFF Bond Coupon Par 6.00% $1,000.00 10.0% Volatility Maturity Callable in R3,HHH 5972.91 $60.00 8.951% R2,HH $988.93 $60.00 S. 191% Today $1,023.75 $0.00 3.000% $987.62 $60.00 -7.328% $1,000.00 $60.00 4.250% $1,000.00 $60.00 6.000% R2.LL $1.4 The Callable Value is The Non-Callable Value is The value of the Call Option is $1,023.75 $0.00 151,023.75) The Yield Curve Calibrating the Lattice | Voor Option-Free with The valuing gallable with Tree OAS + Insert Draw Page Layout Formulas View XLSTAT365-Freemium 11 AA 123 Calibri (Body) BIU General - $ % 0 Conditional Format Formatting as Table Cell Styles Delet: Forma x L M N x BC Bond Coupon 600N $1,000.00 Volatility 10.0% Maturity Callable in OAS 0.34% DAS in bp 34.16 1 C 22. 5969.87 560.00 9.2013 & 560.00 V 74 598449 560.00 7.670 10 57 15 HUL 1.000.00 $60.00 17 19 $60.00 AutoSave Insert Draw b Page Layout u Formulas 3 Home Chapter 40 - Options XLSTAT365-Freemium Data Review View E E 29 P EI General $ % 78 -98 nert Delete Format .C Conditional Format Formatting as Table Cell Styles -2 son Filer X and Seles Calibri (Body) 11 A A Paste BTUEAA A1 | x f x Assuming Annual Bonds A B C 1 Assuming Annual Bonds D On-the-run Yield Curve Years YTM 3.00 4.00 3.0 5.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 PV 1.0 $60.00 $58.25 2.0 $60.00 $55.45 3.0 $1,060.00 $913.87 Bond Value $1,027.57 2.0 v lig ble with the CAS The Vield Curve Vahing Option.Free with Tre Cabrating the attice 29 Paste 839 Calibri (Body) 11 A A B IUDA E fx 1000 Currency $ % . 8 9 Conditional Format Formatting as Table Cell Styles Delete Format X x On the run Ye Curve V S100.00 F CS Par $1.000.00 41 R26 .000 The Yield Curve Calibrating thegatti Option 12 Tree V aluing Callable with Tree OAS + 11 A A E 19 Percentage $ % 3 3 LU Paste c3 A B Calibri (Body) BLUE x fx 6% co & WEX Conditional Format Formatting Table Delete Format Cell Styles OR CHECKING THAT THE CALIBRATION IS ACCURATE Bond Coupon 6.00% $1,000.00 10.0% 3 Volatility Maturity R3,HHH C R2 HH $972.91 $60.00 8-951% $988.93 V $1, HHL CS R3,HHL $987.62 $60.00 7328% R2,HL $60.00 4.250 V HLLC R3HLL S1. $6 000% V $1.00 $60 LLLC R3L Next, compare the price of a 2 Year 3x Coupon as per the tree and the forward rates, Le., without volatility allowed. $ % F23 A x fx Valuing Option Free with Tree'1013 Conditional Format Formatting Table Cor Styles Format B MN OR THE PAY-OFF Bond Coupon Par 6.00% $1,000.00 10.0% Volatility Maturity Callable in R3,HHH 5972.91 $60.00 8.951% R2,HH $988.93 $60.00 S. 191% Today $1,023.75 $0.00 3.000% $987.62 $60.00 -7.328% $1,000.00 $60.00 4.250% $1,000.00 $60.00 6.000% R2.LL $1.4 The Callable Value is The Non-Callable Value is The value of the Call Option is $1,023.75 $0.00 151,023.75) The Yield Curve Calibrating the Lattice | Voor Option-Free with The valuing gallable with Tree OAS + Insert Draw Page Layout Formulas View XLSTAT365-Freemium 11 AA 123 Calibri (Body) BIU General - $ % 0 Conditional Format Formatting as Table Cell Styles Delet: Forma x L M N x BC Bond Coupon 600N $1,000.00 Volatility 10.0% Maturity Callable in OAS 0.34% DAS in bp 34.16 1 C 22. 5969.87 560.00 9.2013 & 560.00 V 74 598449 560.00 7.670 10 57 15 HUL 1.000.00 $60.00 17 19 $60.00