Modify the data on theCDSpricingworksheet in the workbookbonds_and_cds.xlsxto compute a par spread in basis points for a 5yr CDS with notional principalN=10million assuming that the expected recovery rateR=25%, the 3-month hazard rate is a flat1%, and the interest rate is5%per annum.

Interest rate 0.05 1yr bond: c = 5%, R= 10% Hazard rate Survival probability 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 Default probabilty 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 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 Discounte d Expected value d(0,t)*(q(t)*c + (1-q(t))*R) Recovery 2 2 2 102 25 25 25 25 Price 2.40 2.29 2.19 85.66 92.55 3yr bond: c = 5%, R= 50% 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) 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 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 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 probabi Default 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 Recovery 5 105 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.16 0.00 6574.07 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.65 0.00 3yr bond: c = 5%, R= 50% Coupon+Fa 5 5 5 5 5 105 Recovery 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 26.33 107.41 6574.07 4yr bond: c = 5%, R=10% Coupon+Fa Recovery 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.03 0.00 5yr bond: c=10%, R=20% Coupon+Fa Recovery 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.91 0.00 Spread Principal 218.89 1,000,000 Recovery 0.45 Interest 0.01 Month Survival Hazard probability Fixed Discount rate (%) payment 1.00 0.010 100.0 0 1.00 0.010 99.0 54.7 3 1.00 0.010 98.0 54.7 6 0.99 0.010 97.0 54.7 9 0.99 0.010 96.0 54.7 12 0.99 0.010 95.1 54.7 15 0.99 0.010 94.2 54.7 18 0.98 0.010 93.3 54.7 21 0.98 0.010 92.4 54.7 24 Expected value of PV of premium premium (ExD) (NxFxB) 54.17 53.63 53.09 52.55 52.04 51.53 51.03 50.54 Premium Leg Protection Leg Value 5,404 5,336 5,269 5,203 5,139 5,077 5,015 4,954 Accrued Default Prob. interest (%) (E/2xG) 1.0 1.0 1.0 1.0 0.9 0.9 0.9 0.9 41,603.28 41,603.28 0.00 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 Expected Protection payment (1R)H 0.0055 0.0055 0.0054 0.0054 0.0051 0.0051 0.0050 0.0050 PV of expected protection payment (NxKxB) 5,524.85 5,455.57 5,387.16 5,319.61 5,071.00 5,009.18 4,948.11 4,887.79