{"text_format":"3","workbook":"xlsx","n_sheets":3,"sheets":["Sheet1","Simple1","Together"],"not_rendered":{"charts":1,"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Sheet1","index":0,"n_rows":96,"n_cols":65,"rows_kept":96}
["Annuity Value:",null,{"v":1000000,"d":"1,000,000"}]
["Bank annuity rate",null,0.03," continuously compounded"]
["Payments per year:",null,2]
["Length in years:",null,20]
[]
["To find the implied payment, we first convert the continuously-compounded rate to relevant frequency."]
["Equiv. annual yield:",null,{"$f":"EXP(C2)-1","v":0.030454533953516938}]
["Equi semi-annual yld:",null,{"$f":"2*(SQRT(C7+1)-1)","v":0.03022612923143786},"  Verify:",{"$f":"(1+C8/2)^2-1","v":0.030454533953516716}]
[]
["Second we compute the 2 relevant perpetuity factors:"]
["Perp 1",{"$f":"1/(C8/2)","v":66.1679166619794}]
["Perp 2",{"$f":"B11/(1+C8/2)^(C4*2)","v":36.31372260019417}]
["Annuity Factor:",null,{"$f":"B11-B12","v":29.854194061785236}]
[]
["Third we have the annuity factor, so we divide the value of the annuity by this to get the periodic payment."]
[]
["Payment:",{"$f":"C1/C13","v":{"v":33496.13116101656,"d":"33,496.13"}}]
[]
[]
["Next we are going to duration-match this annuity, so we have to find its duration."]
["To do this we have to lay out all future cash flows.",{"$nulls":3},"We will assume that all flows are exactly 1/2 year apart."]
[]
["Time:",0.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12,12.5,13,13.5,14,14.5,15,15.5,16,16.5,17,17.5,18,18.5,19,19.5,20]
["Cash Flow:",{"$f":"$B$17","v":{"v":33496.13116101656,"d":"33,496.13"}},{"$f":"$B$17","v":{"v":33496.13116101656,"d":"33,496.13"}},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"},{"v":33496.13116101656,"d":"33,496.13"}]
["Disc Fctr:",{"$f":"EXP(-$C$2*B23)","v":0.9851119396030626},{"$f":"EXP(-$C$2*C23)","v":0.9704455335485082},0.9559974818331,0.9417645335842487,0.9277434863285529,0.9139311852712282,0.9003245225862656,0.8869204367171575,0.8737159116880344,0.8607079764250578,0.8478937040879159,0.835270211411272,0.8228346580560184,0.8105842459701871,0.7985162187593771,0.7866278610665535,0.774916497961081,0.7633794943368531,0.7520142543193826,0.7408182206817179,0.7297888742690568,0.7189237334319262,0.7082203534678,0.697676326071031,0.6872892787909722,0.6770568744981647,0.6669768108584744,0.6570468198150567,0.6472646670780347,0.6376281516217733,0.6281351051896409,0.6187833918061408,0.6095709072963093,0.6004955788122659,0.5915553643668151,0.5827482523739896,0.5740722611964361,0.5655254386995371,0.5571058618121739,0.5488116360940264]
["PV:",{"$f":"B24*B25","v":32997.43873722761},{"$f":"C24*C25","v":32506.17087636352},32022.21704108306,31545.46833973158,31075.817501839978,30613.15885398838,30157.388296029167,29708.403277663994,29266.10277536956,28830.387269666884,28401.15872272899,27978.320556321996,27561.7776300746,27151.436220071093,26747.203997763085,26348.990009195186,25956.70465453999,25570.25966793773,25189.568097636104,24814.54428642573,24445.10385236695,24081.163669803504,23722.641850658936,23369.45772601141,23021.531827942883,22678.78587165845,22341.14273787199,22008.526455453954,21680.862184337566,21358.07619867947,21040.095870271143,20726.84965219719,20418.26706273704,20114.278669506213,19814.81607383378,19519.811895372335,19229.199756937178,18942.914269571123,18660.891017831742,18383.066545297595]
["Value:",{"$f":"SUM(B26:AO26)","v":999999.9999999987},"   (We verify that we computed the payments correctly since their PV equals the annuity's value.)"]
["Wt:",{"$f":"B26/$B$27","v":0.03299743873722765},{"$f":"C26/$B$27","v":0.032506170876363566},0.0320222170410831,0.03154546833973162,0.031075817501840016,0.03061315885398842,0.030157388296029205,0.029708403277664032,0.029266102775369597,0.02883038726966692,0.028401158722729025,0.02797832055632203,0.027561777630074637,0.02715143622007113,0.02674720399776312,0.02634899000919522,0.025956704654540023,0.025570259667937766,0.025189568097636134,0.024814544286425762,0.02444510385236698,0.024081163669803535,0.023722641850658966,0.023369457726011438,0.023021531827942913,0.022678785871658478,0.02234114273787202,0.02200852645545398,0.021680862184337592,0.0213580761986795,0.02104009587027117,0.02072684965219722,0.020418267062737066,0.020114278669506238,0.019814816073833806,0.01951981189537236,0.019229199756937203,0.01894291426957115,0.018660891017831766,0.01838306654529762,"  Verify that the weights sum to 1:",null,null,{"$f":"SUM(B28:AO28)","v":0.9999999999999998}]
["Wt * D:",{"$f":"B28*B23","v":0.016498719368613825},{"$f":"C28*C23","v":0.032506170876363566},{"$f":"D28*D23","v":0.04803332556162465},0.06309093667946324,0.07768954375460004,0.09183947656196526,0.10555085903610222,0.11883361311065613,0.1316974624891632,0.1441519363483346,0.15620637297500964,0.1678699233379322,0.17915155459548515,0.1900600535404979,0.2006040299832234,0.21079192007356176,0.2206319895635902,0.2301323370114399,0.23930089692754328,0.24814544286425763,0.2566735904498533,0.2648928003678389,0.2728103812825781,0.28043349271213724,0.2877691478492864,0.2948242163315602,0.30160542696127224,0.30811937037635573,0.3143725016728951,0.3203711429801925,0.3261214859892031,0.3316295944351555,0.3369014065351616,0.341942737381606,0.3467592812920916,0.3513566141167025,0.35574019550333824,0.3599153711218518,0.36388737484771944,0.3676613309059524]
["D:",{"$f":"SUM(B29:AO29)","v":9.256574027772178}]
[]
[]
["Next we ascertain the portfolio that consists of the 30-year Bond and the 6-month bill, that has this duration."]
["To do this we need the duration of the 30-year bond.  We will also assume that settlement date is isue date, and that cash flows are exactly 1/2 year apart."]
["Bond coupon:",null,{"$f":"0.03","v":0.03}]
[]
["Time:",0.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12,12.5,13,13.5,14,14.5,15,15.5,16,16.5,17,17.5,18,18.5,19,19.5,20,20.5,21,21.5,22,22.5,23,23.5,24,24.5,25,25.5,26,26.5,27,27.5,28,28.5,29,29.5,30]
["Cash Flow:",{"$f":"50*$C$35","v":1.5},{"$f":"50*$C$35","v":1.5},1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,{"$f":"50*$C$35+100","v":101.5}]
["Disct Fctr:",{"$f":"EXP(-B37*$C$2)","v":0.9851119396030626},{"$f":"EXP(-C37*$C$2)","v":0.9704455335485082},0.9559974818331,0.9417645335842487,0.9277434863285529,0.9139311852712282,0.9003245225862656,0.8869204367171575,0.8737159116880344,0.8607079764250578,0.8478937040879159,0.835270211411272,0.8228346580560184,0.8105842459701871,0.7985162187593771,0.7866278610665535,0.774916497961081,0.7633794943368531,0.7520142543193826,0.7408182206817179,0.7297888742690568,0.7189237334319262,0.7082203534678,0.697676326071031,0.6872892787909722,0.6770568744981647,0.6669768108584744,0.6570468198150567,0.6472646670780347,0.6376281516217733,0.6281351051896409,0.6187833918061408,0.6095709072963093,0.6004955788122659,0.5915553643668151,0.5827482523739896,0.5740722611964361,0.5655254386995371,0.5571058618121739,0.5488116360940264,0.5406408953093166,0.5325918010068972,0.5246625421065929,0.5168513344916993,0.5091564206075492,0.5015760690660556,0.4941085742561417,0.4867522559599717,0.4795054589748941,0.4723665527410147,0.4653339309743134,0.4584060113052235,0.4515812349225923,0.44485806622294116,0.43823499246494924,0.43171052342907973,0.42528319108227414,0.418951549247639,0.41271417327904963,0.40656965974059917]
["PV:",{"$f":"B38*B39","v":1.477667909404594},{"$f":"C38*C39","v":1.4556683003227622},1.4339962227496499,1.412646800376373,1.3916152294928292,1.3708967779068422,1.3504867838793984,1.3303806550757362,1.3105738675320517,1.2910619646375867,1.2718405561318737,1.252905317116908,1.2342519870840276,1.2158763689552807,1.1977743281390656,1.1799417915998303,1.1623747469416215,1.1450692415052797,1.128021381479074,1.1112273310225769,1.0946833114035852,1.0783856001478893,1.0623305302017,1.0465144891065465,1.0309339181864583,1.0155853117472469,1.0004652162877115,0.9855702297225851,0.970897000617052,0.95644222743266,0.9422026577844613,0.9281750877092112,0.9143563609444638,0.9007433682183988,0.8873330465502227,0.8741223785609844,0.861108391794654,0.8482881580493056,0.8356587927182608,0.8232174541410395,0.8109613429639748,0.7988877015103457,0.7869938131598894,0.775277001737549,0.7637346309113238,0.7523641035990833,0.7411628613842125,0.7301283839399575,0.7192581884623411,0.708549829111522,0.6980008964614701,0.6876090169578353,0.6773718523838884,0.6672870993344118,0.6573524886974238,0.6475657851436196,0.6379247866234112,0.6284273238714585,0.6190712599185745,41.26682046367082]
[null,{"$f":"SUM(B40:BI40)","v":99.55603992252091}]
[]
["Wt:",{"$f":"B40/$B$41","v":0.014842574197955071},{"$f":"C40/$B$41","v":0.014621597056849893},0.014403909836767832,0.014189463557165992,0.013978209966726763,0.013770101532501062,0.013565091429213229,0.013363133528725124,0.013164182389657128,0.012968193247163614,0.012775122002860686,0.012584925214903852,0.012397560088213425,0.012212984464845445,0.012031156814505967,0.011852036225206579,0.011675582394059015,0.011501755618206844,0.011330516785892168,0.011161827367655294,0.010995649407665453,0.010831945515180581,0.01067067885613424,0.010511813144847788,0.010355312635865976,0.010201142115914033,0.010049266895974563,0.009899652803482352,0.009752266174635399,0.009607073846820418,0.009464043151151118,0.009323141905117557,0.009184338405344949,0.009047601420460263,0.008912900184065035,0.0087802043878128,8.64948417458959E-3,0.008520710131795946,0.008393853284728973,0.008268885090062895,0.008145777429426706,0.008024502603077391,0.007905033323667396,7.7873427101048335E-3,0.007671404281505142,0.00755719195123277,7.4446800210315675E-3,7.333843175242577E-3,0.007224656475107899,0.007117095353159367,0.007011135607690769,0.00690675339731235,6.803925235586413E-3,0.006702627985742757,0.006602838855472815,6.5045353918012925E-3,6.407695476034138E-3,0.006312297318781759,0.00621831945505631,{"$f":"BI40/$B$41","v":0.41450845670224085},"  Verify that the wts sum to 1:",null,null,{"$f":"SUM(B43:BJ43)","v":1.0000000000000002}]
["Wt * D:",{"$f":"B43*B37","v":0.007421287098977536},{"$f":"C43*C37","v":0.014621597056849893},0.02160586475515175,0.028378927114331983,0.034945524916816906,0.041310304597503185,0.047477820002246304,0.053452534114900495,0.05923882075345707,0.06484096623581807,0.07026317101573377,0.0755095512894231,0.08058414057338725,0.08549089125391811,0.09023367610879475,0.09481628980165263,0.09924245034950163,0.1035158005638616,0.1076399094659756,0.11161827367655294,0.11545431878048726,0.1191514006669864,0.12271280684554375,0.12614175773817346,0.1294414079483247,0.13261484750688243,0.1356651030956566,0.13859513924875294,0.1414078595322133,0.14410610770230628,0.14669266884284232,0.14917027048188092,0.15154158368819165,0.15380922414782447,0.15597575322113813,0.1580436789806304,0.16001545722990743,0.161893492504123,0.16368013905221496,0.16537770180125788,0.16698843730324747,0.16851455466462523,0.169958216458849,0.17132153962230634,0.17260659633386569,0.17381541487835372,0.17494998049424185,0.17601223620582185,0.17700408364014353,0.17792738382898415,0.1787839579961146,0.1795755883301211,0.18030401874303995,0.18097095561505444,0.1815780685255024,0.1821269909704362,0.18261932106697293,0.183056622244671,0.18344042392416116,12.435253701067225]
[null,{"$f":"SUM(B44:BI44)","v":19.96050661167393}]
[]
[]
["The duration of the 6-month bill:",null,null,0.5]
[]
["Portfolio wts to match duration of annuity:",{"$nulls":3},"wb*.5 + (1-wb) * 19.96 = 9.2567"]
["weight in the bill:",{"$nulls":3},{"$f":"(B30-B45)/(D48-B45)","v":0.5500336038261563}]
["  weight in the bond:",{"$nulls":3},{"$f":"1-E51","v":0.4499663961738437}]
["Verify:",{"$nulls":3},{"$f":"E51*D48+E52*B45","v":9.256574027772178}]
[]
["SO if we buy:",{"$f":"E51*C1","v":{"v":550033.6038261562,"d":"550,033.60"}}," of the 6-month bills, and ",null,{"$f":"E52*C1","v":{"v":449966.3961738437,"d":"449,966.40"}}," of the bond, we will have a duration-matched portfolio to duration hedge the annuity"]
[]
["We want to track the value of the asset and liability over time.  For example, suppose that in 6 months, the yield curve has shifted up by 30 basis points.",{"$nulls":11},{"$f":"C2+0.003","v":0.033}]
[]
["We have to account for our position at that time",{"$nulls":3},"Therefore we have to document the par value of the 2 securities in our portfolio."]
[null,"Par value of bill:",null,{"$f":"B55*EXP(C2*0.5)","v":{"v":558346.2972215976,"d":"558,346.30"}}]
[null,"Par value of bond:",null,{"$f":"E55/(B41/100)","v":{"v":451972.9757471553,"d":"451,972.98"}}]
[]
["1. The bill will mature, so we will have cash worth:",{"$nulls":3},{"$f":"D60","v":{"v":558346.2972215976,"d":"558,346.30"}}," in cash."]
["2. The bond will pay a coupon,  and we will receive:",{"$nulls":3},{"$f":"D61*0.015","v":{"v":6779.594636207329,"d":"6,779.59"}}]
["3. The bond is now worth:"]
[]
["Time:",0.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12,12.5,13,13.5,14,14.5,15,15.5,16,16.5,17,17.5,18,18.5,19,19.5,20,20.5,21,21.5,22,22.5,23,23.5,24,24.5,25,25.5,26,26.5,27,27.5,28,28.5,29,29.5]
["Cash Flow:",{"$f":"50*$C$35","v":1.5},{"$f":"50*$C$35","v":1.5},1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5,{"$f":"50*$C$35+100","v":101.5}]
["Disct Fctr:",{"$f":"EXP(-B67*$M$57)","v":0.9836353793906724},{"$f":"EXP(-C67*$M$57)","v":0.967538559589032},0.9517051581364622,0.9361308642916188,0.9208114378568045,0.9057427080235485,0.8909205722370781,0.8763409950793732,0.8620000071704987,0.8478937040879159,0.8340182453034796,0.8203698531378311,0.8069448117319006,0.7937394660352427,0.7807502208109257,0.7679735396567061,0.7554059440422217,0.7430440123619398,0.7308843790036041,0.7189237334319262,0.7071588192872713,0.6955864334990951,0.6842034254138871,0.6730066959373864,0.661993196690834,0.6511599291810325,0.6405039439839882,0.6300223399419123,0.619712263373362,0.6095709072963093,0.5995955106639216,0.5897833576128504,0.5801317767238208,0.5706381402943203,0.5612998636231915,0.5521144043069306,0.5430792615475027,0.5341919754714841,0.525450126460346,0.5168513344916992,0.5083932584913179,0.5000735956957676,0.4918900810254641,0.483840486467991,0.4759226204715098,0.4681343273480965,0.4604734866868421,0.4529380127765577,0.44552585403792655,0.4382349924649492,0.4310634430755288,0.4240092533710472,0.4170705028047858,0.410245302259044,0.4035317935308158,0.39692814882588245,0.3904325702611842,0.3840432893753353,0.3777585666471497]
["PV:",{"$f":"B68*B69","v":1.4754530690860086},{"$f":"C68*C69","v":1.451307839383548},{"$f":"D68*D69","v":1.4275577372046933},{"$f":"E68*E69","v":1.4041962964374282},{"$f":"F68*F69","v":1.3812171567852067},{"$f":"G68*G69","v":1.3586140620353229},{"$f":"H68*H69","v":1.3363808583556172},{"$f":"I68*I69","v":1.3145114926190598},{"$f":"J68*J69","v":1.293000010755748},{"$f":"K68*K69","v":1.2718405561318737},{"$f":"L68*L69","v":1.2510273679552193},{"$f":"M68*M69","v":1.2305547797067466},{"$f":"N68*N69","v":1.210417217597851},{"$f":"O68*O69","v":1.190609199052864},{"$f":"P68*P69","v":1.1711253312163885},{"$f":"Q68*Q69","v":1.1519603094850592},{"$f":"R68*R69","v":1.1331089160633325},{"$f":"S68*S69","v":1.1145660185429098},{"$f":"T68*T69","v":1.0963265685054062},{"$f":"U68*U69","v":1.0783856001478893},{"$f":"V68*V69","v":1.0607382289309069},{"$f":"W68*W69","v":1.0433796502486428},{"$f":"X68*X69","v":1.0263051381208306},{"$f":"Y68*Y69","v":1.0095100439060796},{"$f":"Z68*Z69","v":0.992989795036251},{"$f":"AA68*AA69","v":0.9767398937715488},{"$f":"AB68*AB69","v":0.9607559159759824},{"$f":"AC68*AC69","v":0.9450335099128684},{"$f":"AD68*AD69","v":0.9295683950600431},{"$f":"AE68*AE69","v":0.9143563609444638},{"$f":"AF68*AF69","v":0.8993932659958823},{"$f":"AG68*AG69","v":0.8846750364192756},{"$f":"AH68*AH69","v":0.8701976650857312},{"$f":"AI68*AI69","v":0.8559572104414803},{"$f":"AJ68*AJ69","v":0.8419497954347872},{"$f":"AK68*AK69","v":0.8281716064603959},{"$f":"AL68*AL69","v":0.8146188923212541},{"$f":"AM68*AM69","v":0.8012879632072262},{"$f":"AN68*AN69","v":0.7881751896905189},{"$f":"AO68*AO69","v":0.7752770017375488},{"$f":"AP68*AP69","v":0.7625898877369768},{"$f":"AQ68*AQ69","v":0.7501103935436514},{"$f":"AR68*AR69","v":0.7378351215381962},{"$f":"AS68*AS69","v":0.7257607297019865},{"$f":"AT68*AT69","v":0.7138839307072646},{"$f":"AU68*AU69","v":0.7022014910221448},{"$f":"AV68*AV69","v":0.6907102300302632},{"$f":"AW68*AW69","v":0.6794070191648366},{"$f":"AX68*AX69","v":0.6682887810568898},{"$f":"AY68*AY69","v":0.6573524886974238},{"$f":"AZ68*AZ69","v":0.6465951646132932},{"$f":"BA68*BA69","v":0.6360138800565709},{"$f":"BB68*BB69","v":0.6256057542071787},{"$f":"BC68*BC69","v":0.615367953388566},{"$f":"BD68*BD69","v":0.6052976902962237},{"$f":"BE68*BE69","v":0.5953922232388237},{"$f":"BF68*BF69","v":0.5856488553917764},{"$f":"BG68*BG69","v":0.5760649340630029},{"$f":"BH68*BH69","v":38.34249451468569}]
[null,{"$f":"SUM(B70:BI70)","v":93.87786198891065}]
[]
["So our position in the bond is worth:",null,null,{"$f":"D61*B71/100","v":{"v":424302.5663990871,"d":"424,302.57"}}]
[]
["So our asset portfolio is now:"]
[null,"Cash",{"$f":"E63+E64","v":{"v":565125.891857805,"d":"565,125.89"}}]
[null,"Bond:",{"$f":"D73","v":{"v":424302.5663990871,"d":"424,302.57"}}]
[null,"Total:",{"$f":"C76+C77","v":{"v":989428.458256892,"d":"989,428.46"}}]
[]
["We have to make a cash payment on the annuity:",{"$nulls":3},{"$f":"-B17","v":{"v":-33496.13116101656,"d":"-33,496.13"}}]
["Leaving:"]
[null,"Cash:",{"$f":"C76+E80","v":{"v":531629.7606967884,"d":"531,629.76"}}]
[null,"Bond:",{"$f":"C77","v":{"v":424302.5663990871,"d":"424,302.57"}}]
[null,"Total:",{"$f":"C82+C83","v":{"v":955932.3270958755,"d":"955,932.33"}}]
[]
["And our liability is now worth:"]
["semi-ann yld:",{"$f":"2*(SQRT(EXP(M57))-1)","v":0.033273753572111264}]
["   perp 1",{"$f":"1/(B87/2)","v":60.107435599821244}]
["   perp 2",{"$f":"B88/(1+B87/2)^(2*19.5)","v":31.583459637133085}]
["AF:",{"$f":"B88-B89","v":28.52397596268816}]
["Annuity Value:",{"$f":"B90*B17","v":{"v":955442.8400798861,"d":"955,442.84"}}]
[]
["A Monte Carlo analysis of this hedge will look at one scenario over the life of the annuity -- all 40 periods."]
["  And it will do that many times, so that we have scenarios."]
[]
["In order to do this, we need programs that will compute all the above steps for us."]

{"sheet":"Simple1","index":1,"n_rows":147,"n_cols":65,"rows_kept":147}
["The first program that we have is to compute the value of the bond in our stylized situations."]
["The program will need the term in years, the coupon rate, and the level of the (flat) yield curve, as by assumption we will be on a coupon payment (or issue) date, and all payments will occur on exactly 1/2-year increments."]
["VBA interfaces with Excel:"]
["Level of flat yield curve:",null,0.03," (continuously-compounded)",null,0.013627105945228427,6.348483250189307E-3,0.005,0.005,0.01945437418720069,0.02749036545625181,0.028945162264920013,0.033265812779926345,0.025783775420545992,0.0211863521298883,0.00770036707104718,0.014388856177869424,0.019930020541368408,0.025660675215155793,0.02260248635209907,0.021730757772916942,0.022710742947239567,0.028746443940965948,0.02988553087660024,0.024024367478390672,0.01826286684377931,0.015816591288297713,0.017127155815533587,0.014291095966831532,0.014791871847969354,0.013463353391045915,0.01476479208445675,0.005,0.005845699373913798,0.005,0.017605230462645876,0.028204167902029988,0.0454284591297122,0.05244832087541379,0.050827023258037785,0.05691250314496969,0.07321422197179434,0.06863106728637011,0.06200835733907379,0.0656072001417648,0.05061413277649171,0.04768002123525579,0.05123737167687708,0.04257903726985003,0.03389633274853998,0.02999523652168144,0.02818326613078056,0.029233253055436385,0.025746122900429165,0.02194787306653232,0.02810206305552245,0.024588203473462047,0.022350295012792593,0.0205778225131622,0.027651312673710744,0.02603499840636966,0.03025245409290767,0.033145159088840574,0.03147028710857785,0.04312389412228]
["Bond term in years",null,30]
["Coupon rate:",null,0.03]
["Annual std dev:",null,0.01,"   This is the annual standard deviation of interest rate changes."]
["Bond value from subroutine:",null,null,99.5560399225209,"  We can verify that this indeed equals the value that we obtained on the preceding worksheet."]
["Duration from subroutine:",null,null,19.96050661167393,"  We can verify that this indeed equals the value that we obtained on the preceding worksheet."]
["Next, we'll need a procedure to generate a path for interest rates over the term of the annuity.  The first example we use will be very simple and naïve.  "]
["   We will assume that the yield curve is always flat, and that in any 6-month period the yield will change by a draw from a normal distribution with a mean of 0, and standard deviation of 20 basis points."]
["    And we will cap the rate at 12%, and not allow it to fall below 50 basis points."]
[]
[]
[]
[]
["The subroutine IRPath demonstrates simulating the interest where the yield curve is always flat, and interest change from one period to the next is a bounded arithmetic Brownian motion."]
[]
[]
[]
[]
["Spreadheet function Norm.Inv gives the value under a normal distribution with the specified mean and standard deviation with the give probability."]
["  To use this we first get the probability by obtaining a uniform (0,1) random value.",{"$nulls":5},"This is obtained with the VBA function Rnd."]
["For example the value under a normal(0,1) distribution corresponding to 50% is:",{"$nulls":5},"Probability:",0.5,null,"And 95%:",0.95,null,"5%ile:",0.05,null,"92.5%ile:",0.025,null,"97.5%ile",0.975]
[{"$nulls":6},"Mean:",0,null,null,0,null,null,0,null,null,0,null,null,0]
[{"$nulls":6},"Std Dev:",1,null,null,1,null,null,1,null,null,1,null,null,1]
[{"$nulls":5},{"$f":"_xlfn.NORM.INV(H24,H25,H26)","v":0},{"$nulls":3},{"$f":"_xlfn.NORM.INV(K24,K25,K26)","v":1.6448536269514715},null,null,{"$f":"_xlfn.NORM.INV(N24,N25,N26)","v":-1.6448536269514726},null,null,{"$f":"_xlfn.NORM.INV(Q24,Q25,Q26)","v":-1.9599639845400538},null,null,{"$f":"_xlfn.NORM.INV(T24,T25,T26)","v":1.9599639845400536}]
[]
[]
["There are 2 ways to change the mean and standard deviation of a normal distribution:"]
[{"$nulls":6},"1. Change the values in the Norm.Inv function"]
[{"$nulls":6},"2. Use the standard-unit normal: Z and use x = mean + Z * stddev"]
[null,"Normal(),1) CDF","Prob"]
[null,{"$f":"_xlfn.NORM.S.INV(C34)","v":-3.71901648545568},1E-4]
[null,-3.090232306167813,1E-3]
[null,-2.8781617390954826,2E-3]
[null,-2.5758293035489,0.005]
[null,-2.4323790585844463,0.0075]
[null,-3.090232306167813,1E-3]
[null,-2.8781617390954826,2E-3]
[null,-2.5758293035489,0.005]
[null,-2.3263478740408408,0.01]
[null,-2.0537489106318225,0.02]
[null,-1.8807936081512509,0.03]
[null,-1.7506860712521695,0.04]
[null,-1.6448536269514726,0.05]
[null,-1.554773594596853,0.06]
[null,-1.4757910281791702,0.07]
[null,-1.4050715603096353,0.08]
[null,-1.340755033690216,0.09]
[null,-1.2815515655446006,0.1]
[null,-1.2265281200366105,0.11]
[null,-1.1749867920660904,0.12]
[null,-1.1263911290388013,0.13]
[null,-1.0803193408149565,0.14]
[null,-1.0364333894937898,0.15]
[null,-0.9944578832097497,0.16]
[null,-0.9541652531461955,0.17]
[null,-0.915365087842815,0.18]
[null,-0.8778962950512285,0.19]
[null,-0.8416212335729145,0.2]
[null,-0.8064212470182406,0.21]
[null,-0.772193214188685,0.22]
[null,-0.7388468491852139,0.23]
[null,-0.7063025628400873,0.24]
[null,{"$f":"_xlfn.NORM.S.INV(C66)","v":-0.6744897501960819},0.25]
[null,-0.643345405392917,0.26]
[null,-0.6128129910166273,0.27]
[null,-0.5828415072712163,0.28]
[null,-0.553384719555673,0.29]
[null,-0.5244005127080409,0.3]
[null,-0.49585034734745354,0.31]
[null,-0.4676987991145083,0.32]
[null,-0.43991316567323374,0.33]
[null,-0.41246312944140484,0.34]
[null,-0.38532046640756784,0.35]
[null,-0.35845879325119384,0.36]
[null,-0.3318533464368166,0.37]
[null,-0.30548078809939727,0.38]
[null,-0.27931903444745415,0.39]
[null,-0.2533471031357998,0.4]
[null,-0.2275449766411495,0.41]
[null,-0.20189347914185088,0.42]
[null,-0.17637416478086138,0.43]
[null,-0.15096921549677725,0.44]
[null,-0.12566134685507402,0.45]
[null,-0.10043372051146976,0.46]
[null,-0.0752698620998299,0.47]
[null,-0.050153583464733656,0.48]
[null,-0.02506890825871106,0.49]
[null,0,0.5]
[null,0.02506890825871106,0.51]
[null,0.050153583464733656,0.52]
[null,0.0752698620998299,0.53]
[null,0.10043372051146988,0.54]
[null,0.12566134685507416,0.55]
[null,0.15096921549677741,0.56]
[null,{"$f":"_xlfn.NORM.S.INV(C98)","v":0.1763741647808612},0.57]
[null,0.20189347914185077,0.58]
[null,0.22754497664114934,0.59]
[null,0.2533471031357998,0.6]
[null,0.27931903444745415,0.61]
[null,0.30548078809939727,0.62]
[null,0.3318533464368166,0.63]
[null,0.35845879325119384,0.64]
[null,0.38532046640756784,0.65]
[null,0.41246312944140473,0.66]
[null,0.43991316567323396,0.67]
[null,0.46769879911450835,0.68]
[null,0.4958503473474533,0.69]
[null,0.5244005127080408,0.7]
[null,0.5533847195556727,0.71]
[null,0.5828415072712163,0.72]
[null,0.6128129910166273,0.73]
[null,0.643345405392917,0.74]
[null,0.6744897501960819,0.75]
[null,0.7063025628400873,0.76]
[null,0.7388468491852139,0.77]
[null,0.772193214188685,0.78]
[null,0.8064212470182406,0.79]
[null,0.8416212335729147,0.8]
[null,0.8778962950512286,0.81]
[null,0.9153650878428126,0.82]
[null,0.9541652531461955,0.83]
[null,0.9944578832097497,0.84]
[null,1.0364333894937898,0.85]
[null,1.0803193408149565,0.86]
[null,1.1263911290388013,0.87]
[null,1.1749867920660904,0.88]
[null,{"$f":"_xlfn.NORM.S.INV(C130)","v":1.2265281200366105},0.89]
[null,1.2815515655446006,0.9]
[null,1.340755033690216,0.91]
[null,1.4050715603096329,0.92]
[null,1.4757910281791713,0.93]
[null,1.5547735945968528,0.94]
[null,1.6448536269514715,0.95]
[null,1.7506860712521695,0.96]
[null,1.8807936081512504,0.97]
[null,1.9599639845400536,0.975]
[null,2.053748910631822,0.98]
[null,2.17009037758456,0.985]
[null,2.3263478740408408,0.99]
[null,2.432379058584449,0.9925]
[null,2.5758293035489,0.995]
[null,2.8070337683438114,0.9975]
[null,3.090232306167813,0.999]
[null,3.7190164854557084,0.9999]

{"sheet":"Together","index":2,"n_rows":29,"n_cols":9,"rows_kept":29}
["Now that we know the basic ideas of the things we need the computer to do we can pull it all together."]
["A key part of this will be accounting for the values of the positions at all points In time.  So we need to keep track of the par values of our positions, as we saw in the introduction."]
[]
["Annuity Value:",null,{"v":1000000,"d":"1,000,000.00"}]
["Level of flat yield curve:",null,0.03," cc","semi-annual rate:",null,{"$f":"2*(SQRT(EXP(C5))-1)","v":0.03022612923143786}]
["Bond term in years",null,30,null,"periodic rate:",null,{"$f":"0.5*G5","v":0.01511306461571893}]
["Coupon rate:",null,0.03]
["Annuity Term:",null,20,null,"Annual standard deviation of interest rate process:",{"$nulls":3},0.006]
["Pmt:",null,{"$f":"C4/B12","v":{"v":33496.13116101656,"d":"33,496.13"}}]
["   perp 1:",{"$f":"1/G6","v":66.1679166619794}]
["   perp 2:",{"$f":"B10/(1+G6)^(2*C8)","v":36.31372260019417},{"$nulls":3},{"v":1078091.9047504154,"d":"1,078,091.90"}]
["AF:",{"$f":"B10-B11","v":29.854194061785236}]
[]
[{"$nulls":3},{"v":22806.449339468385,"d":"22,806.45"}]
[{"$nulls":3},{"v":5349.677490354028,"d":"5,349.68"}]
[]
["The  main program that does the Monte Carlo analysis comprises Module 8.  This is subroutine Monte."]
["Most of the analysis is done in the subroutine OnePath, which comprises Module 4.  It gets the current interest rate (the level of the flat yield curve), in continuously-compounded terms from this worksheet,"]
["                                          ",{"$nulls":5},"The (original) term of the bond that we use as the long end of the barbell hedge, along with its coupon rate"]
[{"$nulls":6},"The original term of the annuity that we are hedging, the periodic payment on the annuity, and the initial value of the annuity"]
[{"$nulls":6},"The cap and floor on the arithmetic Brownian motion interest rate process"]
[{"$nulls":6},"The annual standard deviation of the interest rate process"]
[]
[null,"OnePath calls the function BondVal(Term, y, coupon) to get the value of the bond.  This bond's term shrinks by 6 months each period."]
[null,"OnePath calls the function AnnuityDur(Aterm, y, payment) to get the annuity's duration each period."]
[null,"OnePath calls the function BondDur(Aterm, y, payment) to get the annuity's duration each period."]
["Monte calls Onepath Ndraws times.  Each call is one draw from a Monte Carlo simulation."]
["    It keeps a running sum of the P&L from each Monte Carlo draw, and also keeps track of the minimum ending cash position from all of the draws.  The average P&L from the lifetime of the hedge is reported in Cell D14"]
["          and the minimum ending position (i.e., the P&L is reported in cell D15."]