{"text_format":"3","workbook":"xlsx","n_sheets":1,"sheets":["Cat1"],"not_rendered":{"macros":1,"defined_names":26},"read":"whole","date_system":"1900"}

{"sheet":"Cat1","index":0,"n_rows":1093,"n_cols":23,"rows_kept":1093}
["Cat Bond:"]
[null,"Definition:","An insurance linked security that is a debt obligation structured just like a US Treasury bond."]
[null,null,"A borrower raises capital in exchange for a security that promises future interest and principal payments according to s schedule."]
[null,null,"If a specified event happens the bond defaults and the bond holders get nothing."]
[null,"Example:","(Stylized)"]
[null,null,"In July 2020, Swiss Re issued ",null,null,{"v":240000000,"d":"240,000,000"}," in 5-year Cat bonds on named hurricane risk in Florida."]
[null,null,"The bonds were sold at par with a coupon of ",{"$nulls":3},0.105]
[]
[null,null,"Let's suppose that the issue date was July 31, 2020."]
[null,null,"In this case the cash flow time line looks like:"]
[null,null,"Date","Jan 31 2021","2021-07-31","Jan 31 2022","2022-07-31","Jan 31 2023","July 31 2023","Jan 31 2024","July 31 2024","Jan 31 2025","July 31 2025"]
[null,null,"Time (Yrs):",0.5,1,1.5,2,2.5,3,3.5,4,4.5,5]
[null,null,"Cash Flow:",{"$f":"50*$G$7","v":5.25},{"$f":"50*$G$7","v":5.25},5.25,5.25,5.25,5.25,5.25,5.25,5.25,{"$f":"100*(1+G7/2)","v":105.25}]
[]
[null,null,"Price:",100," (% of par)"]
[null,null,"ytm:","No calculations needed.  A bond selling at par on its issue date or coupon payment date has a ytm equal to its coupon rate -- on a compounding basis that matches the frequency of payments (semi-annual in this case)."]
[{"$nulls":3},{"$f":"G7","v":0.105}," on a bond equivalent basis"]
[{"$nulls":3},{"$f":"LN((1+G7/2)^2)","v":0.10233657314879886}," on a cc basis."]
[null,null,"Verify:"]
[null,null,"df:",{"$f":"EXP(-D12*$D$18)","v":0.9501187648456056},{"$f":"EXP(-E12*$D$18)","v":0.9027256673117394},0.857696596020655,0.8149136304234251,0.7742647319937531,0.7356434508254186,0.6989486468650058,0.6640842250498868,0.6309588836578497,0.5994858752093584]
[null,null,"PV:",{"$f":"D13*D20","v":4.98812351543943},{"$f":"E13*E20","v":4.739309753386632},4.502907129108438,4.278296559722982,4.064889842967204,3.8621281168334476,3.6694803960412803,3.4864421815119058,3.312534139203711,63.095888365784965]
[null,null,{"$f":"SUM(D21:M21)","v":100}]
[]
[null,null,"At this same time the cc ytm on a 5-year US Treasury note is:",{"$nulls":4},0.0045,"  (45 basis points)"]
[]
[]
["We can use this market information combined with knowledge of arbitrage pricing to ascertain what the world's best models and minds believe to be the probability of a serius named hurricane hitting the east coast of Florida over the next 5 years."]
[]
[null,"First, we assume that the risk is constant over the 5 years, and continuous."]
[null,"We use the hazard rate to model this probability.  For example if the hazard rate is",{"$nulls":5},0.03,"  (roughly a 3% chance of occurring each year), then we can buid the following table describing the probability of the named hurricance damage occurring (for the first time):"]
[null,null,"The survival probability is the probability that the event has not occurred prior to time tis exp(h*T), so the probability that the event has occurred before time T is 1 - exp(-h*T)"]
["Hazard rate:",0.09783657314879896]
[]
[null,null,"Date","Jan 31 2021","2021-07-31","Jan 31 2022","2022-07-31","Jan 31 2023","July 31 2023","Jan 31 2024","July 31 2024","Jan 31 2025","July 31 2025"]
[null,null,"Time (Yrs):",0.5,1,1.5,2,2.5,3,3.5,4,4.5,5]
[null,"Survival Probability:",null,{"$f":"EXP(-$B$32*D35)","v":0.952258938859388},{"$f":"EXP(-$B$32*E35)","v":0.9067970866376075},0.8635056314823126,0.8222809563344528,0.7830243909233287,0.7456419756016675,0.7100442364554614,0.6761459711503022,0.643866045001637,0.6131271967808498]
[null,"Periodic default probability:",null,{"$f":"1-D36","v":0.04774106114061205},{"$f":"D36-E36","v":0.04546185222178045},{"$f":"E36-F36","v":0.04329145515529487},0.04122467514785988,0.0392565654111241,0.0373824153216612,0.03559773914620601,0.03389826530515927,0.03227992614866515,0.030738848220787207,{"$f":"SUM(D37:M37)","v":0.3868728032191502},{"$f":"M36+N37","v":{"v":1,"d":"1.000000000"}}]
[null,"Promised Cash Flow:",null,{"$f":"50*$D$17","v":5.25},{"$f":"50*$D$17","v":5.25},5.25,5.25,5.25,5.25,5.25,5.25,5.25,{"$f":"100*(1+D17/2)","v":105.25}]
[null,"Expected value of CF:",null,{"$f":"D36*D38","v":4.999359429011787},{"$f":"E36*E38","v":4.760684704847439},4.533404565282141,4.316975020755877,4.110878052347475,3.9146203719087542,3.7277322413911724,3.5497663485390865,3.3802967362585945,64.53163746118445]
[null,"df (@ Treasury ytm):",null,{"$f":"EXP(-$H$24*D35)","v":0.9977525293526299},{"$f":"EXP(-$H$24*E35)","v":0.9955101098295706},0.9932727300785684,0.9910403787728836,0.9888130446112331,0.9865907163177327,0.9843733826418407,0.9821610323583008,0.9799536542670847,{"$f":"EXP(-$H$24*M35)","v":0.9777512371933363}]
[null,"PV of Expected CF:",null,{"$f":"D39*D40","v":4.9881235154394306},{"$f":"E39*E40","v":4.739309753386631},4.502907129108438,4.278296559722982,4.064889842967203,3.8621281168334467,3.669480396041279,3.486442181511905,3.3125341392037093,63.095888365784944]
[null,null,{"$f":"SUM(D41:M41)","v":99.99999999999997}]
[]
["We can use Solver to ascertain the hazard rate that reconciles the market valuation with the promised future cash flows and Treasury note ytm."]
["Criterion:",{"$f":"(C42-C22)^2","v":8.077935669463161e-28}," we want to minimize this criterion by changing the hazard rate in Cell B32.",{"$nulls":5},"We find that the hazard rate that explains the market valuation is roughly 9.78%."]
[null,"This means that the probability that there will be a major named hurricane on the east coast of Florida over the next 5 years is around 39%."]
[]
["Next issue:"]
[null,"Why should we discount the expected future cash flows at the risk free rate (i.e., we use the Treasury note's ytm).  These cash flows are risky."]
[]
[null,"We can simulate what happens to use if we buy the bond using Monte Carlo.  "]
[null,"We need a random draw to characterize whether a hurrciane in east Fla occurs that is consistent with the hazard model.."]
[]
["I'll introduce the Monte Carlo and its application here using VBA.  In a sequence of subroutines."]
["The first subroutine in Module 1 simply evaluates one possible outcome for 1 bond.  Default happens in a period ",{"$nulls":7},{"$f":"1-D36","v":0.04774106114061205}," of the time (roughly 4.8% of the time).  This is the probability of default conditional on not having defaulted to that time."]
["This subroutine reads the following input from this worksheet:"]
["Cat Bond Term:",null,5]
["Cat bond coupon rate:",null,{"$f":"G7","v":0.105}]
["continuous hazard:",null,{"$f":"B32","v":0.09783657314879896}]
[]
["and produces a single outcome for the cash flows each period from the bond:"]
[null,null,"Time since issue date (Yrs):",null,0.5,1,1.5,2,2.5,3,3.5,4,4.5,5]
[{"$nulls":4},5.25,5.25,0,0,0,0,0,0,0,0]
[]
[]
["So the next thing to do is to build a portfolio.  ",null,null,"This is in Module 2.  It evauates the period-by-period cash flows on a portfolio containing the number of bonds in Cell C71 ($100 invested in each), as well as the number of bonds that have not yet defaulted at each point in the timeline."]
["We assume that the bonds have identical terms, coupon, and hazard rates."]
[]
[]
[]
["Number of Bonds:",null,{"v":10000000,"d":"10,000,000 "},"  (100 invested in each)",null,"Since all bonds have a bond equivalent ytm = coupon rate (i.e., they're selling at par), the total initial investment is:",{"$nulls":8},{"$f":"C71*100","v":{"v":1000000000,"d":"1,000,000,000"}}]
[]
[]
[null,"Time (Yrs) Relative to Issue Date:",null,null,0.5,1,1.5,2,2.5,3,3.5,4,4.5,5]
[null,null,"Cash Flows received:",null,49996563.75,47609698.5,45334632,43172445.75,41109594.75,39148236.75,37278176.25,35496909,33806220,645339848.75]
[null,"Number of bonds not in default:",null,null,9523155,9068514,8635168,8223323,7830399,7456807,7100605,6761316,6439280,6131495," <--- The value in Cell N76 is the number of bonds from the original portfolio that"]
["Number of bonds that defaulted during the previous 6 months:",{"$nulls":3},{"$f":"C71-E76","v":{"v":476845,"d":"476,845"}},{"$f":"E76-F76","v":{"v":454641,"d":"454,641"}},{"$f":"F76-G76","v":{"v":433346,"d":"433,346"}},{"v":411845,"d":"411,845"},{"v":392924,"d":"392,924"},{"v":373592,"d":"373,592"},{"v":356202,"d":"356,202"},{"v":339289,"d":"339,289"},{"v":322036,"d":"322,036"},{"v":307785,"d":"307,785"},"        did not default over their terms.  We expected this to be:",{"$nulls":4},{"$f":"EXP(-C59*N74)*C71","v":6131271.967808498}]
[null,null,"df (@Treas ytm):",null,{"$f":"EXP(-$H$24*E74)","v":0.9977525293526299},{"$f":"EXP(-$H$24*F74)","v":0.9955101098295706},0.9932727300785684,0.9910403787728836,0.9888130446112331,0.9865907163177327,0.9843733826418407,0.9821610323583008,0.9799536542670847,0.9777512371933363]
[null,"PV of cash flows (@Treas ytm):",null,null,{"$f":"E75*E78","v":49884197.94050251},{"$f":"F75*F78","v":47395936.18268774},45029653.69374723,42785636.98863177,40649703.54748146,38623286.937758684,36695644.45393123,34863680.78896866,33128528.825957004,630981835.525473]
[{"$nulls":3},{"$f":"SUM(E79:N79)","v":{"v":1000038104.8851392,"d":"1,000,038,104.9"}}]
["When we run the subroutine, in Module 2 associated with the workbook, we get one draw from nature playing out over the 5-year horizon, and affecting all of the bonds in this portfolio."]
["The point is that one draw we may get a discounted present value of slightly less than the market value of the portfolio and In another draw we may get slightly more."]
["It takes about 26 seconds on my laptop to get one draw.  (Running on a Mac with no patallel processing)."]
[]
[]
[]
[]
["The final VBA code uses Monte Carlo to run through multiple possible future outcomes, and assess the expected present value and the standard deviation of that value:"]
["We will run many draws, and keep the discounted PV -- using the US Treasury cc ytm to discount--"]
["This subroutine is in Module 3.  One lesson from these subroutines is the value of ``Do Loops'' in programming."]
["NDRAWS",null,5]
["cc Treasury ytm:",null,0.0045]
[]
[]
[]
[null,null,6131467,{"v":1000029254.9431336,"d":"1,000,029,255"}]
[null,null,6131175,{"v":999988871.6391083,"d":"999,988,872"}]
[null,null,6131685,{"v":1000048763.1400571,"d":"1,000,048,763"}]
[null,null,6131516,{"v":1000003772.368477,"d":"1,000,003,772"}]
[null,null,6131008,{"v":999953198.7321104,"d":"999,953,199"}]
{"$rows_empty":988}
[{"$nulls":22},{"$f":"AVERAGE(#REF!)","v":{"$error":"#REF!"}}]
[]
["Average present value:",null,{"$f":"AVERAGE(#REF!)","v":{"$error":"#REF!"}}]
["std dev of present value:",null,{"$f":"STDEV(#REF!)","v":{"$error":"#REF!"}}]
["Median present value:",null,{"$f":"MEDIAN(#REF!)","v":{"$error":"#REF!"}}]