Hi, I'm doing this coursera course on Financial Engineering and Risk Management part 1 and I'm stuck with quiz 6, can someone plz help. Cheers!

I used the Term_Structure_Lattices_q6.xlsx adapted fromTerm_Structure_Lattices.xlsx to tackle q1 and q2 but I can't seem to get the right answer :( q3-5 are completely beyond me, would someone be so nice to explain me what I'm missing. Thanks heaps!

Interest r Hazard rate Time Survival probabiDefault probabiDiscount rate 0 6 12 18 24 30 36 42 48 54 60 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.98 0.95 0.93 0.91 0.88 0.86 0.84 0.82 0.80 0.78 0.05 1yr bond: c = 5%, R= 10% Coupon+Face 5 105 Recovery Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 10 10 Model Price True Price Error Sum Error 4.88 99.94 104.82 100.92 15.17 1279.89 100.92 2yr bond: c = 8%, R= 25% Coupon+Face Recovery 2 2 2 102 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 25 25 25 25 1.95 1.90 1.86 92.41 Model Price True Price Error 98.12 91.56 43.08 True Price 91.56 d Expected value )*c + (1-q(t))*R) 3yr bond: c = 5%, R= 50% Coupon+FaRecovery 5 5 5 5 5 105 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 50 50 50 50 50 50 4.88 4.76 4.64 4.53 4.42 90.54 Model Price True Price Error 113.77 105.60 66.70 True Price 105.60 4yr bond: c = 5%, R=10% Coupon+Fa 5 5 5 5 5 5 5 105 Recovery Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 10 10 10 10 10 10 10 10 Model Price True Price Error True Price 4.88 4.76 4.64 4.53 4.42 4.31 4.21 86.18 117.93 98.90 361.84 98.90 5yr bond: c=10%, R=20% Coupon+FaRecovery 10 10 10 10 10 10 10 10 10 110 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 20 20 20 20 20 20 20 20 20 20 Model Price True Price Error 9.76 9.52 9.29 9.06 8.84 8.62 8.41 8.21 8.01 85.93 165.64 137.48 793.10 137.48 d Expected value )*c + (1-q(t))*R) Interest rate Hazard rate Survival Default probability probabilty Time 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0 6 12 18 24 30 36 42 48 54 60 1.00 0.98 0.96 0.94 0.92 0.90 0.89 0.87 0.85 0.83 0.82 Discount rate 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 1.00 0.98 0.95 0.93 0.91 0.88 0.86 0.84 0.82 0.80 0.78 0.05 1yr bond: c = 5%, R= 10% Coupon+Face 2yr bond: c = 2%, R= 25% Discounte d Expected value d(0,t)*(q(t)*c + (1-q(t))*R) Recovery 5 105 10 10 Price 4.98 96.17 101.15 Coupon+Face Recovery 2 2 2 102 25 25 25 25 Price 3yr bond: c = 5%, R= 50% Discounte d Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 2.40 2.29 2.19 85.66 92.55 Discounte d Coupon+F Expected ace Recovery value d(0,t)*(q(t)*c + (1-q(t))*R) 5 5 5 5 5 105 50 50 50 50 50 50 Price 5.76 5.50 5.26 5.03 4.81 80.98 107.35 4yr bond: c = 5%, R=10% 5yr bond: c=10%, R=20% Discounte d Coupon+F Expected ace Recovery value d(0,t)*(q(t)*c + (1-q(t))*R) Discounte d Coupon+F Expected ace Recovery value d(0,t)*(q(t)*c + (1-q(t))*R) 5 5 5 5 5 5 5 105 10 10 10 10 10 10 10 10 Price 4.98 4.76 4.55 4.35 4.16 3.98 3.80 73.46 104.02 10 10 10 10 10 10 10 10 10 110 20 20 20 20 20 20 20 20 20 20 Price 9.95 9.51 9.10 8.70 8.32 7.95 7.60 7.27 6.95 70.47 145.82 t)*c + (1-q(t))*R) Interest r Hazard rate 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 Time Survival probabiDefault probabiDiscount rate 0 6 12 18 24 30 36 42 48 54 60 1.00 0.98 0.96 0.94 0.92 0.90 0.89 0.87 0.85 0.83 0.82 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.02 1.00 0.98 0.95 0.93 0.91 0.88 0.86 0.84 0.82 0.80 0.78 0.05 1yr bond: c = 5%, R= 10% Coupon+Face 5 105 Recovery Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 10 10 Model Price True Price Error Sum Error 4.98 96.14 101.12 101.19 0.00 0.01 2yr bond: c = 8%, R= 25% Coupon+Face Recovery 2 2 2 102 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 25 25 25 25 Model Price True Price Error 2.40 2.30 2.18 85.72 92.60 92.56 0.00 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 3yr bond: c = 5%, R= 50% Coupon+FaRecovery 5 5 5 5 5 105 Discounted Expected value d(0,t)*(p(t)*c + (1-p(t))*R) 50 50 50 50 50 50 Model Price True Price Error 5.76 5.51 5.24 5.01 4.81 81.06 107.39 107.36 0.00 4yr bond: c = 5%, R=10% Coupon+FaRecovery 5 5 5 5 5 5 5 105 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 10 10 10 10 10 10 10 10 Model Price True Price Error 4.98 4.76 4.54 4.35 4.16 3.98 3.80 73.51 104.08 104.08 0.00 5yr bond: c=10%, R=20% Coupon+FaRecovery 10 10 10 10 10 10 10 10 10 110 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) 20 20 20 20 20 20 20 20 20 20 Model Price True Price Error 9.95 9.51 9.09 8.69 8.32 7.96 7.61 7.27 6.95 70.59 145.95 145.88 0.00 Discounted Expected value d(0,t)*(q(t)*c + (1-q(t))*R) Spread Principal 218.89 1,000,000 Recovery 0.45 Interest 0.01 Month Expected Survival value of PV of Hazard probability Fixed premium premium Default Prob. Discount rate (%) payment (ExD) (NxFxB) (%) 0 1.00 0.010 100.0 3 1.00 0.010 99.0 54.7 54.17 5,404 1.0 6 1.00 0.010 98.0 54.7 53.63 5,336 1.0 9 0.99 0.010 97.0 54.7 53.09 5,269 1.0 12 0.99 0.010 96.0 54.7 52.55 5,203 1.0 15 0.99 0.010 95.1 54.7 52.04 5,139 0.9 18 0.99 0.010 94.2 54.7 51.53 5,077 0.9 21 0.98 0.010 93.3 54.7 51.03 5,015 0.9 24 0.98 0.010 92.4 54.7 50.54 4,954 0.9 Premium Leg Protection Leg Value 41,603.28 41,603.28 0.00 Accrued interest (E/2xG) 0.28 0.27 0.27 0.27 0.26 0.25 0.25 0.25 PV of accrued interest (NxIxB) 27.48 27.14 26.80 26.46 25.23 24.92 24.62 24.32 PV of Expected expected Protection protection payment (1- payment R)H (NxKxB) 0.0055 0.0055 0.0054 0.0054 0.0051 0.0051 0.0050 0.0050 5,524.85 5,455.57 5,387.16 5,319.61 5,071.00 5,009.18 4,948.11 4,887.79 Questions 1 and 2 should be answered by building and calibrating a 10-period Black-Derman-Toy model for the short-rate, rij. You may assume that the termstructure of interest rates observed in the market place is: Period 1 2 3 4 5 6 7 8 9 10 Spot Rate 3.0% 3.1% 3.2% 3.3% 3.4% 3.5% 3.55% 3.6% 3.65% 3.7% As in the video modules, these interest rates assume per-period compounding so that, for example, the market-price of a zero-coupon bond that matures in period 6 is: Z06 = 100/(1+0.035)6 = 81.35 assuming a face value of 100. __________________________________ Questions 3-5 refer to the material on defaultable bonds and credit-default swaps (CDS). Q1. Assume b=0.05 is a constant for all i in the BDT model as we assumed in the video lectures. Calibrate the ai parameters so that the model term-structure matches the market term-structure. Be sure that the final error returned by Solver is at most 108. (This can be achieved by rerunning Solver multiple times if necessary, starting each time with the solution from the previous call to Solver. Once your model has been calibrated, compute the price of a payer swaption with notional $1M that expires at time t=3 with an option strike of 0. You may assume the underlying swap has a fixed rate of 3.9% and that if the option is exercised then cash-flows take place at times t=4,...,10. (The cash-flow at time t=i is based on the short-rate that prevailed in the previous period, i.e. the payments of the underlying swap are made in arrears.) Q2. Repeat the previous question but now assume a value of b=0.1. Q3. Construct a n=10-period binomial model for the short-rate, ri,j. The lattice parameters are: r0,0=5%, u=1.1, d=0.9 and q=1q=1/2. This is the same lattice that you constructed in Assignment 5. Assume that the 1-step hazard rate in node (i,j) is given by hij=abji/2 where a=0.01 and b=1.01. Compute the price of a zero-coupon bond with face value F=100 and recovery R=20%. Q4. The true price of 5 different defaultable coupon paying bonds with non-zero recovery are specified in worksheet b in the workbook _.. The interest rate is r=5% per annum. Calibrate the six month hazard rates to to by minimizing the ensuring that the term structure of hazard rates are non-decreasing. You can model the non-decreasing hazard rates by adding constraints of the form ,...,. Report the hazard rate at time 0 as a percentage. Q5. Modify the data on the worksheet in the workbook __cd. to compute a par spread in basis points for a 5yr CDS with notional principal N=10 million assuming that the expected recovery rate R=25%, the 3-month hazard rate is a flat 1%, and the interest rate is 5% per annum. Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 6.00% 1 7.50% 5.40% 4-Year Zero-Coupon Bond 0 1 5 4 3 2 1 79.27 0 77.22 84.43 2 3 4 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 2 3 4 83.08 87.35 90.64 89.51 92.22 94.27 95.81 100.00 100.00 100.00 100.00 100.00 American Zero Option Value Expiration 3 Strike 88.00 Option type -1 3 2 1 0 0 1 2 10.78 8.73 3.57 4.92 0.65 0.00 3 0.00 0.00 0.00 0.00 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% European Zero Option Value Expiration 2 Strike 84.00 Option type 1 2 1 0 0 1 2.97 1.56 4.74 2 0.00 3.35 6.64 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 1 6.00% 2 3 4 7.50% 5.40% 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 1 2 3 4 5 115.83 126.14 108.98 118.55 126.27 104.03 112.49 119.27 124.57 101.66 108.44 113.83 118.00 121.16 102.98 107.19 110.46 112.96 114.84 116.24 1 2 3 79.99 89.24 81.53 90.45 97.67 85.08 93.27 99.85 104.99 6-Year 10% Coupon Bond 0 6 5 4 3 2 1 0 124.14 A Bond Forward Coupon 10.0% Maturity 4 0 4 3 2 1 0 Bond Forward Price 79.83 103.38 4 91.66 98.44 103.83 108.00 111.16 6 110.00 110.00 110.00 110.00 110.00 110.00 110.00 4-Year Zero-Coupon Bond 0 5 4 3 2 1 0 77.22 1 2 3 4 79.27 84.43 83.08 87.35 90.64 89.51 92.22 94.27 95.81 100.00 100.00 100.00 100.00 100.00 1 2 3 100.81 105.64 98.09 103.52 107.75 95.05 101.14 105.91 109.58 A Bond Future Coupon 10.0% Maturity 4 0 4 3 2 1 0 103.22 Bond Futures Price 103.22 4 91.66 98.44 103.83 108.00 111.16 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 6.00% Fixed Rate Caplet With Expiration t = 6 7.50% 5.40% 2 3 4 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 2 3 4 0.0637 0.0471 0.0323 0.0800 0.0592 0.0412 0.0264 0.1032 0.0756 0.0528 0.0346 0.0206 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 2.0% 0 5 4 3 2 1 0 1 0.0420 1 0.0515 0.0376 5 0.1379 0.0988 0.0684 0.0453 0.0278 0.0149 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 6.00% Fixed Rate Swap With Expiration t = 6 1 7.50% 5.40% 0.0990 Swaption Strike Swaption: Expiration t = 3 3 2 1 0 3 4 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 2 3 4 0.1686 0.0829 0.0137 0.1793 0.1021 0.0400 -0.0085 0.1648 0.1014 0.0512 0.0122 -0.0174 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 5.0% 0 5 4 3 2 1 0 2 1 0.1403 0.0496 0% 0 1 2 0.0620 0.0908 0.0406 0.1286 0.0665 0.0191 3 0.1793 0.1021 0.0400 0.0000 5 0.1125 0.0723 0.0410 0.0172 -0.0008 -0.0141 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 1 2 3 4 6.00% 7.50% 5.40% 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 0 1 2 3 4 5 0.4717 0.4717 0.2194 0.4432 0.2238 0.1003 0.3079 0.3143 0.1067 0.0449 0.1868 0.2901 0.1992 0.0511 0.0196 0.1041 0.2193 0.2293 0.1190 0.0246 6 0.0083 0.0543 0.1461 0.2075 0.1640 0.0686 0.0119 94.34 6.00% 88.63 6.22% 82.91 6.45% 77.22 6.68% 71.59 6.91% 66.06 7.15% Elementary Prices 6 5 4 3 2 1 0 1.0000 Zero Coupon Bond Prices Spot Rates Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 6.00% 1 7.50% 5.40% 4-Year Zero-Coupon Bond 0 1 5 4 3 2 1 79.27 0 77.22 84.43 2 3 4 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 2 3 4 83.08 87.35 90.64 89.51 92.22 94.27 95.81 100.00 100.00 100.00 100.00 100.00 American Zero Option Value Expiration 3 Strike 88.00 Option type -1 3 2 1 0 0 1 2 10.78 8.73 3.57 4.92 0.65 0.00 3 0.00 0.00 0.00 0.00 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% European Zero Option Value Expiration 2 Strike 84.00 Option type 1 2 1 0 0 1 2.97 1.56 4.74 2 0.00 3.35 6.64 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 1 6.00% 2 3 4 7.50% 5.40% 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 1 2 3 4 5 115.83 126.14 108.98 118.55 126.27 104.03 112.49 119.27 124.57 101.66 108.44 113.83 118.00 121.16 102.98 107.19 110.46 112.96 114.84 116.24 1 2 3 79.99 89.24 81.53 90.45 97.67 85.08 93.27 99.85 104.99 6-Year 10% Coupon Bond 0 6 5 4 3 2 1 0 124.14 A Bond Forward Coupon 10.0% Maturity 4 0 4 3 2 1 0 Bond Forward Price 79.83 103.38 4 91.66 98.44 103.83 108.00 111.16 6 110.00 110.00 110.00 110.00 110.00 110.00 110.00 4-Year Zero-Coupon Bond 0 5 4 3 2 1 0 77.22 1 2 3 4 79.27 84.43 83.08 87.35 90.64 89.51 92.22 94.27 95.81 100.00 100.00 100.00 100.00 100.00 1 2 3 100.81 105.64 98.09 103.52 107.75 95.05 101.14 105.91 109.58 A Bond Future Coupon 10.0% Maturity 4 0 4 3 2 1 0 103.22 Bond Futures Price 103.22 4 91.66 98.44 103.83 108.00 111.16 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 6.00% Fixed Rate Caplet With Expiration t = 6 7.50% 5.40% 2 3 4 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 2 3 4 0.0637 0.0471 0.0323 0.0800 0.0592 0.0412 0.0264 0.1032 0.0756 0.0528 0.0346 0.0206 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 2.0% 0 5 4 3 2 1 0 1 0.0420 1 0.0515 0.0376 5 0.1379 0.0988 0.0684 0.0453 0.0278 0.0149 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 6.00% Fixed Rate Swap With Expiration t = 6 1 7.50% 5.40% 0.0990 Swaption Strike Swaption: Expiration t = 3 3 2 1 0 3 4 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 2 3 4 0.1686 0.0829 0.0137 0.1793 0.1021 0.0400 -0.0085 0.1648 0.1014 0.0512 0.0122 -0.0174 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 5.0% 0 5 4 3 2 1 0 2 1 0.1403 0.0496 0% 0 1 2 0.0620 0.0908 0.0406 0.1286 0.0665 0.0191 3 0.1793 0.1021 0.0400 0.0000 5 0.1125 0.0723 0.0410 0.0172 -0.0008 -0.0141 Term Structure Lattice r(0,0) 6.00% u 1.25 d 0.9 q 0.50 1-q 0.50 Short-Rate Lattice 0 5 4 3 2 1 0 1 2 3 4 6.00% 7.50% 5.40% 9.38% 6.75% 4.86% 11.72% 8.44% 6.08% 4.37% 14.65% 10.55% 7.59% 5.47% 3.94% 5 18.31% 13.18% 9.49% 6.83% 4.92% 3.54% 0 1 2 3 4 5 0.0449 0.1868 0.2901 0.1992 0.0511 0.0196 0.1041 0.2193 0.2293 0.1190 0.0246 6 0.0083 0.0543 0.1461 0.2075 0.1640 0.0686 0.0119 77.22 6.68% 71.59 6.91% 66.06 7.15% Elementary Prices 6 5 4 3 2 1 0 1.0000 Zero Coupon Bond Prices Spot Rates 0.4717 0.4717 0.2194 0.4432 0.2238 0.1003 0.3079 0.3143 0.1067 94.34 6.00% 88.63 6.22% 82.91 6.45%