{"text_format":"3","workbook":"xlsx","n_sheets":2,"sheets":["1FVas","2FVas"],"not_rendered":{"charts":2,"macros":1,"defined_names":55},"read":"whole","date_system":"1900"}

{"sheet":"1FVas","index":0,"n_rows":166,"n_cols":1005,"rows_kept":166}
["Here we consider the dynamic hedging problem of an annuity using the 1-factor Vasicek model.",{"$nulls":6},1.2777777777777775,-0.06481481481481466]
[null,"In this model all risk comes from the uncertain future movement of the factor. "]
[null,"Therefore we want to build an asset that has the same factor duration as our liability (the annuity)."]
[]
["Parameters of the model:",{"$nulls":8},"Annuity:"]
["theta",0.035,"  (long-run mean)",{"$nulls":6},"  Value:",1000000]
["k",0.6,"  (magnitude of the pull to the long-run mean)",{"$nulls":6},"Term (Yrs)",20]
["sigma",0.04,"  (annual standard deviation)"]
["lambda",0.006,"  (risk premium)"]
[]
["Current factor level:",null,null,0.03]
[]
["Term Structure:"]
["Term","spot rate"]
[0.5,{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,A15)","v":0.03198735086137165}]
[1,{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,A16)","v":0.03354611991630639}]
[1.5,0.03478729710587319]
[2,0.03578894719116235]
[2.5,0.03660705451094663]
[3,0.03728250812379776]
[3.5,0.03784567811916604]
[4,0.03831946773300854]
[4.5,0.038721386399987755]
[5,0.039064983667285696]
[5.5,0.03936085874018496]
[6,0.039617383317610395]
[6.5,0.03984122732014648]
[7,0.04003774678495205]
[7.5,0.04021127379676973]
[8,0.04036533572354217]
[8.5,0.04050282271518325]
[9,0.040626116855649846]
[9.5,0.040737192566731154]
[10,0.04083769523886865]
[10.5,0.04092900322176315]
[11,0.041012276994437366]
[11.5,0.04108849838602375]
[12,0.04115850202508796]
[12.5,0.04122300068258055]
[13,0.04128260579062437]
[13.5,0.04133784413085703]
[14,0.041389171466950404]
[14.5,0.041436983728346775]
[15,0.041481626223255415]
[15.5,0.04152340125908691]
[16,0.04156257447078517]
[16.5,0.0415993800967449]
[17,0.04163402539427115]
[17.5,0.04166669434889176]
[18,0.041697550802026605]
[18.5,0.0417267410978283]
[19,0.04175439633111417]
[19.5,0.04178063426318622]
[20,0.041805560960190294]
[20.5,0.04182927219887923]
[21,0.04185185467673335]
[21.5,0.041873387056975395]
[22,0.041893940873797546]
[22.5,0.04191358131885706]
[23,0.041932367926610314]
[23.5,0.04195035517319026]
[24,0.041967593001173524]
[24.5,0.04198412728063396]
[25,0.042000000215264614]
[25.5,0.04201525070100735]
[26,0.04202991464351041]
[26.5,0.0420440252397988]
[27,0.042057613228758034]
[27.5,0.04207070711437255]
[28,0.04208333336510388]
[28.5,0.042095516592324064]
[29,0.04210727971032139]
[29.5,0.04211864408005676]
[30,{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,A74)","v":0.042129629638560796}]
[]
[]
["We compute the value of the coupon bond and annuity as portfolios of 0-coupon bonds:"]
["Coupon Bond",null,0.0425,null,30," Year Term"]
[]
["Term:",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$78","v":2.125},{"$f":"50*$C$78","v":2.125},2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,{"$f":"100+50*$C$78","v":102.125}]
["yld:",{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,B80)","v":0.03198735086137165},{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,C80)","v":0.03354611991630639},0.03478729710587319,0.03578894719116235,0.03660705451094663,0.03728250812379776,0.03784567811916604,0.03831946773300854,0.038721386399987755,0.039064983667285696,0.03936085874018496,0.039617383317610395,0.03984122732014648,0.04003774678495205,0.04021127379676973,0.04036533572354217,0.04050282271518325,0.040626116855649846,0.040737192566731154,0.04083769523886865,0.04092900322176315,0.041012276994437366,0.04108849838602375,0.04115850202508796,0.04122300068258055,0.04128260579062437,0.04133784413085703,0.041389171466950404,0.041436983728346775,0.041481626223255415,0.04152340125908691,0.04156257447078517,0.0415993800967449,0.04163402539427115,0.04166669434889176,0.041697550802026605,0.0417267410978283,0.04175439633111417,0.04178063426318622,0.041805560960190294,0.04182927219887923,0.04185185467673335,0.041873387056975395,0.041893940873797546,0.04191358131885706,0.041932367926610314,0.04195035517319026,0.041967593001173524,0.04198412728063396,0.042000000215264614,0.04201525070100735,0.04202991464351041,0.0420440252397988,0.042057613228758034,0.04207070711437255,0.04208333336510388,0.042095516592324064,0.04210727971032139,0.04211864408005676,0.042129629638560796,"   (Paste Special -- Transpose)"]
["df:",{"$f":"EXP(-B80*B82)","v":0.9841335442564174},{"$f":"EXP(-C80*C82)","v":0.9670103117683029},0.9491571054458597,0.9309237610392248,0.9125452221094454,0.8941805878537858,0.8759380818030913,0.857891305631785,0.8400900094317543,0.8225673474171828,0.8053448380245917,0.7884357971669294,0.7718477390797097,0.7555840689918517,0.7396452841309832,0.7240298299995581,0.708734713037681,0.6937559400660019,0.6790888339754113,0.6647282606841243,0.6506687922996902,0.6369048243230505,0.6234306596966006,0.6102405689081164,0.5973288327919346,0.5846897728224891,0.5723177723659805,0.5602072913971157,0.5483528764953051,0.5367491674339883,0.5253909013144672,0.5142729149333426,0.5033901458826922,0.4927376327445124,0.4823105146412275,0.47210403033179565,0.4621135169905649,0.452334408768062,0.4427622352053763,0.43339261955385205,0.42422127703734525,0.41524401308382125,0.40645672154547763,0.3978553829210737,0.3894360625901692,0.3811949090660832,0.37312815227230545,0.3652321018455768,0.3575031454677717,0.349937747227925,0.3425324460151834,0.3352838539430542,0.32818865480503373,0.3212436025614931,0.3144455198575493,0.3077912965715521,0.3012778883937434,0.29490231543460166,0.28866166086234857,0.28255306956908366]
["PV:",{"$f":"B81*B83","v":2.091283781544887},{"$f":"C81*C83","v":2.0548969125076435},2.016958849072452,1.9782129922083527,1.9391585969825715,1.900133749189295,1.861368423831569,1.823019024467543,1.785191270042478,1.7479556132615135,1.7113577808022573,1.675426068979725,1.6401764455443832,1.6056161466076848,1.5717462287783392,1.538563388749061,1.506061265205072,1.474231372640254,1.443063772197749,1.4125475539537642,1.3826711836368417,1.3534227516864823,1.3247901518552763,1.2967612089297473,1.269323769682861,1.2424657672477895,1.2161752662777086,1.1904404942188709,1.1652498625525232,1.1405919807972251,1.1164556652932427,1.0928299442333531,1.0697040600007208,1.0470674695820887,1.0249098436126085,1.0032210644550659,0.9819912236049504,0.9612106186321318,0.9408697498114246,0.9209593165519356,0.9014702137043586,0.8823935278031202,0.86372053328414,0.8454426887072816,0.8275516330041096,0.8100391817654269,0.7928973235786491,0.7761182164218507,0.7596941841190148,0.7436177128593405,0.7278814477822647,0.7124781896289902,0.6974008914606967,0.6826426554431728,0.6681967296972923,0.6540565052145482,0.6402155128367047,0.6266674202985285,0.6134060293324907,28.855732229742667]
[{"$f":"SUM(B84:BI84)","v":100.59970345591411}]
[]
[]
["We do the same for the annuity:",{"$nulls":3},20,"  Annuity Term"]
[null,"But we will use Solver to compute the payment.",{"$nulls":5},"Payment:",36944.90457180035]
["Term:",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]
["df:",{"$f":"B83","v":0.9841335442564174},{"$f":"C83","v":0.9670103117683029},0.9491571054458597,0.9309237610392248,0.9125452221094454,0.8941805878537858,0.8759380818030913,0.857891305631785,0.8400900094317543,0.8225673474171828,0.8053448380245917,0.7884357971669294,0.7718477390797097,0.7555840689918517,0.7396452841309832,0.7240298299995581,0.708734713037681,0.6937559400660019,0.6790888339754113,0.6647282606841243,0.6506687922996902,0.6369048243230505,0.6234306596966006,0.6102405689081164,0.5973288327919346,0.5846897728224891,0.5723177723659805,0.5602072913971157,0.5483528764953051,0.5367491674339883,0.5253909013144672,0.5142729149333426,0.5033901458826922,0.4927376327445124,0.4823105146412275,0.47210403033179565,0.4621135169905649,0.452334408768062,0.4427622352053763,0.43339261955385205]
["CF:",{"$f":"$I$89","v":36944.90457180035},{"$f":"$I$89","v":36944.90457180035},36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035]
["PV:",{"$f":"B91*B92","v":36358.719878460994},{"$f":"C91*C92","v":35726.10368822685},35066.51868434353,34392.88951521563,33713.896148285814,33035.416488214454,32361.448843021055,31694.7124195435,31037.045230179014,30389.672154206764,29753.388188210545,29128.68528732354,28515.84106425973,27914.98132487657,27326.12443920133,26749.212976470502,26184.13633989943,25630.747001858083,25088.87216699674,24558.322157153794,24038.896439360637,23530.387955934366,23032.586229625445,22545.279584151514,22068.25672548291,21601.307861034486,21144.225484806502,20696.80492109319,20258.844693791267,19830.146769841984,19410.516711955162,18999.76376607394,18597.70090202072,18204.144820680816,17818.915937396065,17441.838348570527,17072.73978655546,16711.451566477783,16357.808527659647,{"$f":"AO91*AO92","v":16011.648971539636}]
[{"$f":"SUM(B93:AO93)","v":999999.9999999998}]
[null,"Criterion:",{"$f":"(K6-A94)^2","v":5.421010862427522e-20}]
[]
["The bank ascertains that the fair cash flow is:",{"$nulls":3},{"$f":"I89","v":{"v":36944.90457180035,"d":"$36,944.90"}}," every 6 months.",null,"(Using the Vasicek model with parameters and state variable above.)"]
[]
["Similarly, we compute the factor durations of securities by computing the factor duration of each cash flow, and treating each cash flow as a component of a portfolio."]
["To get the factor duration, we use the chain rule.  We know the duration which is the percentage change in the value given a change in the spot rate."]
["So we also have to know how the spot rate changes when the factor changes.  "]
[]
[]
[]
[]
["So we can compute the factor duration of the bond:"]
[]
["Term:",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$78","v":2.125},{"$f":"50*$C$78","v":2.125},2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,{"$f":"100+50*$C$78","v":102.125}]
["yld:",{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,B108)","v":0.03198735086137165},{"$f":"vasYield($D$11,$B$7,$B$6,$B$8,$B$9,C108)","v":0.03354611991630639},0.03478729710587319,0.03578894719116235,0.03660705451094663,0.03728250812379776,0.03784567811916604,0.03831946773300854,0.038721386399987755,0.039064983667285696,0.03936085874018496,0.039617383317610395,0.03984122732014648,0.04003774678495205,0.04021127379676973,0.04036533572354217,0.04050282271518325,0.040626116855649846,0.040737192566731154,0.04083769523886865,0.04092900322176315,0.041012276994437366,0.04108849838602375,0.04115850202508796,0.04122300068258055,0.04128260579062437,0.04133784413085703,0.041389171466950404,0.041436983728346775,0.041481626223255415,0.04152340125908691,0.04156257447078517,0.0415993800967449,0.04163402539427115,0.04166669434889176,0.041697550802026605,0.0417267410978283,0.04175439633111417,0.04178063426318622,0.041805560960190294,0.04182927219887923,0.04185185467673335,0.041873387056975395,0.041893940873797546,0.04191358131885706,0.041932367926610314,0.04195035517319026,0.041967593001173524,0.04198412728063396,0.042000000215264614,0.04201525070100735,0.04202991464351041,0.0420440252397988,0.042057613228758034,0.04207070711437255,0.04208333336510388,0.042095516592324064,0.04210727971032139,0.04211864408005676,0.042129629638560796]
["df:",{"$f":"EXP(-B108*B110)","v":0.9841335442564174},{"$f":"EXP(-C108*C110)","v":0.9670103117683029},0.9491571054458597,0.9309237610392248,0.9125452221094454,0.8941805878537858,0.8759380818030913,0.857891305631785,0.8400900094317543,0.8225673474171828,0.8053448380245917,0.7884357971669294,0.7718477390797097,0.7555840689918517,0.7396452841309832,0.7240298299995581,0.708734713037681,0.6937559400660019,0.6790888339754113,0.6647282606841243,0.6506687922996902,0.6369048243230505,0.6234306596966006,0.6102405689081164,0.5973288327919346,0.5846897728224891,0.5723177723659805,0.5602072913971157,0.5483528764953051,0.5367491674339883,0.5253909013144672,0.5142729149333426,0.5033901458826922,0.4927376327445124,0.4823105146412275,0.47210403033179565,0.4621135169905649,0.452334408768062,0.4427622352053763,0.43339261955385205,0.42422127703734525,0.41524401308382125,0.40645672154547763,0.3978553829210737,0.3894360625901692,0.3811949090660832,0.37312815227230545,0.3652321018455768,0.3575031454677717,0.349937747227925,0.3425324460151834,0.3352838539430542,0.32818865480503373,0.3212436025614931,0.3144455198575493,0.3077912965715521,0.3012778883937434,0.29490231543460166,0.28866166086234857,0.28255306956908366]
["PV:",{"$f":"B109*B111","v":2.091283781544887},{"$f":"C109*C111","v":2.0548969125076435},{"$f":"D109*D111","v":2.016958849072452},{"$f":"E109*E111","v":1.9782129922083527},{"$f":"F109*F111","v":1.9391585969825715},{"$f":"G109*G111","v":1.900133749189295},{"$f":"H109*H111","v":1.861368423831569},{"$f":"I109*I111","v":1.823019024467543},{"$f":"J109*J111","v":1.785191270042478},{"$f":"K109*K111","v":1.7479556132615135},{"$f":"L109*L111","v":1.7113577808022573},{"$f":"M109*M111","v":1.675426068979725},{"$f":"N109*N111","v":1.6401764455443832},{"$f":"O109*O111","v":1.6056161466076848},{"$f":"P109*P111","v":1.5717462287783392},{"$f":"Q109*Q111","v":1.538563388749061},{"$f":"R109*R111","v":1.506061265205072},{"$f":"S109*S111","v":1.474231372640254},{"$f":"T109*T111","v":1.443063772197749},{"$f":"U109*U111","v":1.4125475539537642},{"$f":"V109*V111","v":1.3826711836368417},{"$f":"W109*W111","v":1.3534227516864823},{"$f":"X109*X111","v":1.3247901518552763},{"$f":"Y109*Y111","v":1.2967612089297473},{"$f":"Z109*Z111","v":1.269323769682861},{"$f":"AA109*AA111","v":1.2424657672477895},{"$f":"AB109*AB111","v":1.2161752662777086},{"$f":"AC109*AC111","v":1.1904404942188709},{"$f":"AD109*AD111","v":1.1652498625525232},{"$f":"AE109*AE111","v":1.1405919807972251},{"$f":"AF109*AF111","v":1.1164556652932427},{"$f":"AG109*AG111","v":1.0928299442333531},{"$f":"AH109*AH111","v":1.0697040600007208},{"$f":"AI109*AI111","v":1.0470674695820887},{"$f":"AJ109*AJ111","v":1.0249098436126085},{"$f":"AK109*AK111","v":1.0032210644550659},{"$f":"AL109*AL111","v":0.9819912236049504},{"$f":"AM109*AM111","v":0.9612106186321318},{"$f":"AN109*AN111","v":0.9408697498114246},{"$f":"AO109*AO111","v":0.9209593165519356},{"$f":"AP109*AP111","v":0.9014702137043586},{"$f":"AQ109*AQ111","v":0.8823935278031202},{"$f":"AR109*AR111","v":0.86372053328414},{"$f":"AS109*AS111","v":0.8454426887072816},{"$f":"AT109*AT111","v":0.8275516330041096},{"$f":"AU109*AU111","v":0.8100391817654269},{"$f":"AV109*AV111","v":0.7928973235786491},{"$f":"AW109*AW111","v":0.7761182164218507},{"$f":"AX109*AX111","v":0.7596941841190148},{"$f":"AY109*AY111","v":0.7436177128593405},{"$f":"AZ109*AZ111","v":0.7278814477822647},{"$f":"BA109*BA111","v":0.7124781896289902},{"$f":"BB109*BB111","v":0.6974008914606967},{"$f":"BC109*BC111","v":0.6826426554431728},{"$f":"BD109*BD111","v":0.6681967296972923},{"$f":"BE109*BE111","v":0.6540565052145482},{"$f":"BF109*BF111","v":0.6402155128367047},{"$f":"BG109*BG111","v":0.6266674202985285},{"$f":"BH109*BH111","v":0.6134060293324907},{"$f":"BI109*BI111","v":28.855732229742667}]
[{"$f":"SUM(B112:BI112)","v":100.59970345591411}]
["wt:",{"$f":"B112/$A$113","v":0.020788170438905438},{"$f":"C112/$A$113","v":0.020426470873327798},0.020049351834882354,0.019664203017012535,0.019275987208375527,0.01888806511265704,0.018502722770423256,0.018121514893594554,0.017745492369416416,0.017375355525054023,0.017011558901386097,0.01665438377473894,0.016303988870734188,0.015960446119121167,0.01562376602299953,0.01529391574621596,0.014970832054838755,0.014654430599651894,0.014344612584570333,0.014041269560727742,0.013744286873000285,0.013453546135745757,0.013168927008177914,0.012890308463961109,0.012617569695313444,0.012350590752906903,0.01208925299477323,0.011833439397170376,0.011583034765735384,0.011337925874672859,0.011098001554075237,0.010863152739930937,0.010633272497363754,0.010408256024740133,0.01018800064417442,9.972405782435613E-3,0.009761372945152757,0.009554805686414015,0.00935260957527318,0.009154692160256004,0.008960962932653282,0.008771333289166327,0.008585716494310036,8.404027642862591E-3,0.008226183622566724,8.052103077226376E-3,7.881706370298806E-3,0.007714915549049999,7.5516543093184785E-3,0.007391847960915876,7.2354233936807255E-3,7.0823090441933575E-3,6.93243486315364E-3,0.006785732283418985,0.006642134188696855,6.5015748828839825E-3,0.006363990060042939,0.006229316775005738,6.097493414593454E-3,0.2868371499960548,"  Verify that the weights sum to 1: ",{"$nulls":3},{"$f":"SUM(B114:BI114)","v":0.9999999999999999}]
["dr/df",{"$f":"(1-EXP(-$B$7*B108))/($B$7*B108)","v":0.8639392643942738},{"$f":"(1-EXP(-$B$7*C108))/($B$7*C108)","v":0.751980606509956},0.6593670447326676,0.5823381567398316,0.5179132265677134,0.4637228398768964,0.4178778913081039,0.37886751946274483,0.34547943972601863,0.3167376438773787,0.2918535856359879,0.2701878548757521,0.251220022706204,0.234524862661791,0.21975355632483506,0.20661880269812083,0.19488299086950675,0.18434878130692361,0.17485158500746117,0.16625354130388895,0.15843868178142398,0.15130903514582608,0.14478148037260166,0.13878519641550324,0.13325958875064697,0.12815259807961799,0.12341931615579273,0.11902084912807395,0.11492338094126581,0.11109739891065704,0.1075170511579056,0.10415961158994887,0.10100503286039837,0.09803557153738392,0.09523547271910955,0.09259070374968416,0.09008872861947569,0.08771831618554012,0.08546937659665294,0.08333282131563723,0.08130044294760463,0.07936481174482268,0.07751918619764916,0.07575743556051534,0.07407397252154915,0.07246369451945862,0.07092193245402133,0.06944440573677992,0.06802718279422167,0.06666664627317863,0.06535946231254819,0.06410255334084916,0.06289307393895584,0.06172838937432047,0.06060605646933129,0.059523806513969435,0.05847952997311664,0.05747126277294067,0.05649717397975581,0.05555555470944557]
["Fctr Dur:",{"$f":"B108*B115","v":0.4319696321971369},{"$f":"C108*C115","v":0.751980606509956},{"$f":"D108*D115","v":0.9890505670990014},1.1646763134796632,1.2947830664192836,1.391168519630689,1.4625726195783635,1.5154700778509793,1.554657478767084,1.5836882193868935,1.6051947209979334,1.6211271292545124,1.6329301475903262,1.641674038632537,1.648151672436263,1.6529504215849666,1.6565054223908073,1.6591390317623125,1.6610900575708811,1.6625354130388894,1.6636061587049518,1.664399386604087,1.6649870242849192,1.665422356986039,1.6657448593830873,1.6659837750350337,1.6661607681032018,1.6662918877930353,1.6663890236483543,1.6664609836598556,1.6665142929475367,1.6665537854391819,1.666583042196573,1.6666047161355266,1.666620772584417,1.6666326674943148,1.6666414794603002,1.666648007525262,1.6666528436347323,1.6666564263127446,1.666659080425895,1.6666610466412763,1.666662503249457,1.6666635823313376,1.6666643817348559,1.6666649739475483,1.6666654126695013,1.666665737682718,1.6666659784584308,1.6666661568294656,1.6666662889699788,1.6666663868620781,1.6666664593823297,1.6666665131066527,1.6666665529066105,1.6666665823911442,1.6666666042338243,1.6666666204152796,1.6666666324027963,1.666666641283367]
["FD * wt:",{"$f":"B114*B116","v":8.979858338545376E-3},{"$f":"C114*C116","v":0.015360309956182988},0.019829822802257795,0.02290243147736983,0.024958221825919353,0.026276481581463156,0.027061575711670175,0.02746261358657342,0.027588162426517453,0.02751714585268703,0.027306864544450367,0.02699887335824547,0.026623274952999012,0.026201850038754648,0.02575033610055954,0.02528008448039263,0.024799264476542508,0.02431373779613445,0.02382769334393592,0.023344107888734884,0.022865080288930896,0.02239207393598502,0.021926092592371448,0.021467807904327195,0.021017651857876195,0.020575883806440622,0.0201426390555653,0.019717964072195504,0.01930184199415873,0.018894211105769863,0.018494978213020357,0.018104028320535922,0.017721231627161636,0.017346448577577913,0.01697953350468451,0.016620337250516395,0.01626870904687314,0.015924497859552963,0.01558755334403447,0.015257726519805571,0.01493487024106645,0.01461883952016142,0.014309491744596918,0.014006686817264953,0.013710287241342566,0.013420158165428471,0.013136167400193896,0.01285818541471729,0.012586085318420107,0.012319742832887386,0.012059036256672425,0.01180384642532636,0.011554056668270902,0.011309552763681164,0.011070222892218533,0.01083595759021635,0.010606649702749577,0.010382194336895021,0.010162488815398699,0.47806190937921805]
[{"$f":"SUM(B117:BI117)","v":1.5727354309440458}]
[]
[]
["Note that the factor duration is much lower than this bond's duration."]
["  The reason for this is that the factor is mean reverting.  Even if we increase the factor by 100 basis points, this will have a very small effect on long-term rates."]
["Under a 1-factor model, in fact, we would not see much variation in long-term interest rates."]
[]
["And we compute the factor duration of the annuity:"]
[]
["Term:",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]
["df:",{"$f":"B91","v":0.9841335442564174},{"$f":"C91","v":0.9670103117683029},0.9491571054458597,0.9309237610392248,0.9125452221094454,0.8941805878537858,0.8759380818030913,0.857891305631785,0.8400900094317543,0.8225673474171828,0.8053448380245917,0.7884357971669294,0.7718477390797097,0.7555840689918517,0.7396452841309832,0.7240298299995581,0.708734713037681,0.6937559400660019,0.6790888339754113,0.6647282606841243,0.6506687922996902,0.6369048243230505,0.6234306596966006,0.6102405689081164,0.5973288327919346,0.5846897728224891,0.5723177723659805,0.5602072913971157,0.5483528764953051,0.5367491674339883,0.5253909013144672,0.5142729149333426,0.5033901458826922,0.4927376327445124,0.4823105146412275,0.47210403033179565,0.4621135169905649,0.452334408768062,0.4427622352053763,0.43339261955385205]
["CF:",{"$f":"$I$89","v":36944.90457180035},{"$f":"$I$89","v":36944.90457180035},36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035,36944.90457180035]
["PV:",{"$f":"B128*B129","v":36358.719878460994},{"$f":"C128*C129","v":35726.10368822685},35066.51868434353,34392.88951521563,33713.896148285814,33035.416488214454,32361.448843021055,31694.7124195435,31037.045230179014,30389.672154206764,29753.388188210545,29128.68528732354,28515.84106425973,27914.98132487657,27326.12443920133,26749.212976470502,26184.13633989943,25630.747001858083,25088.87216699674,24558.322157153794,24038.896439360637,23530.387955934366,23032.586229625445,22545.279584151514,22068.25672548291,21601.307861034486,21144.225484806502,20696.80492109319,20258.844693791267,19830.146769841984,19410.516711955162,18999.76376607394,18597.70090202072,18204.144820680816,17818.915937396065,17441.838348570527,17072.73978655546,16711.451566477783,16357.808527659647,16011.648971539636]
[{"$f":"SUM(B130:AO130)","v":999999.9999999998}]
["w:",{"$f":"B130/$A$131","v":0.036358719878461},{"$f":"C130/$A$131","v":0.03572610368822686},0.03506651868434354,0.034392889515215644,0.03371389614828582,0.03303541648821446,0.032361448843021064,0.031694712419543504,0.03103704523017902,0.030389672154206772,0.029753388188210553,0.02912868528732355,0.028515841064259737,0.02791498132487658,0.027326124439201335,0.02674921297647051,0.026184136339899435,0.02563074700185809,0.025088872166996748,0.0245583221571538,0.024038896439360642,0.02353038795593437,0.02303258622962545,0.02254527958415152,0.022068256725482915,0.02160130786103449,0.021144225484806507,0.020696804921093194,0.020258844693791273,0.01983014676984199,0.019410516711955168,0.018999763766073943,0.018597700902020724,0.01820414482068082,0.017818915937396068,0.01744183834857053,0.017072739786555463,0.016711451566477787,0.01635780852765965,0.01601164897153964]
["dr/df",{"$f":"(1-EXP(-$B$7*B127))/($B$7*B127)","v":0.8639392643942738},{"$f":"(1-EXP(-$B$7*C127))/($B$7*C127)","v":0.751980606509956},0.6593670447326676,0.5823381567398316,0.5179132265677134,0.4637228398768964,0.4178778913081039,0.37886751946274483,0.34547943972601863,0.3167376438773787,0.2918535856359879,0.2701878548757521,0.251220022706204,0.234524862661791,0.21975355632483506,0.20661880269812083,0.19488299086950675,0.18434878130692361,0.17485158500746117,0.16625354130388895,0.15843868178142398,0.15130903514582608,0.14478148037260166,0.13878519641550324,0.13325958875064697,0.12815259807961799,0.12341931615579273,0.11902084912807395,0.11492338094126581,0.11109739891065704,0.1075170511579056,0.10415961158994887,0.10100503286039837,0.09803557153738392,0.09523547271910955,0.09259070374968416,0.09008872861947569,0.08771831618554012,0.08546937659665294,0.08333282131563723]
["Fctr Dur:",{"$f":"B127*B133","v":0.4319696321971369},{"$f":"C127*C133","v":0.751980606509956},0.9890505670990014,1.1646763134796632,1.2947830664192836,1.391168519630689,1.4625726195783635,1.5154700778509793,1.554657478767084,1.5836882193868935,1.6051947209979334,1.6211271292545124,1.6329301475903262,1.641674038632537,1.648151672436263,1.6529504215849666,1.6565054223908073,1.6591390317623125,1.6610900575708811,1.6625354130388894,1.6636061587049518,1.664399386604087,1.6649870242849192,1.665422356986039,1.6657448593830873,1.6659837750350337,1.6661607681032018,1.6662918877930353,1.6663890236483543,1.6664609836598556,1.6665142929475367,1.6665537854391819,1.666583042196573,1.6666047161355266,1.666620772584417,1.6666326674943148,1.6666414794603002,1.666648007525262,1.6666528436347323,1.6666564263127446]
["FD * w:",{"$f":"B134*B132","v":0.01570586285305753},{"$f":"C134*C132","v":0.02686533711971041},0.034682560190937704,0.040056583770494716,0.043652181835818794,0.04595783145129257,0.04733096900768852,0.04803238829791,0.04825197448593006,0.04812776578164718,0.04775998165151785,0.04722130195879697,0.046564376557723934,0.04582730012996198,0.045037597695671124,0.04421512286652299,0.043374163827663605,0.04252497276400762,0.041674876112265107,0.0408290802710858,0.0399912561649909,0.03916396328041336,0.038348957208049886,0.03754741266394685,0.03676008519601941,0.03598742841602019,0.035229678974712506,0.03448691814325256,0.03375911642953048,0.03304616589219019,0.03234790353397031,0.03166412822680074,0.03099461294715165,0.030339113611360775,0.029697375446199818,0.02906913757288274,0.028454136296305527,0.027852107456125125,0.02726278809825643,0.02668591765428039]
[{"$f":"SUM(B135:AO135)","v":1.5023804318421639}]
[]
[]
["And similarly we compute the factor duration of the 6-month bill:",{"$nulls":5},{"$f":"B127*B133","v":0.4319696321971369}]
[]
["Since there is only one source of risk in the model, if we equate the factor duration of our liability with a portfolio, then this will be a fully hedged position (under the model's assumptions)."]
[]
["weight in bill:",null,{"$f":"(A136-A118)/(G139-A118)","v":0.061673482128552955}]
["weight in bond:",null,{"$f":"1-C143","v":0.938326517871447}]
[]
["Verify:  Portfolio factor duration:",null,null,{"$f":"C143*G139+C144*A118","v":1.5023804318421639},"  checks."]
["So the dollar amounts in each security:"]
["Bond:",{"$f":"C144*K6","v":{"v":938326.517871447,"d":"938,326.52"}}]
["Bill:",{"$f":"C143*K6","v":{"v":61673.482128552954,"d":"61,673.48"}}]
["Portfolio:",{"$f":"B148+B149","v":{"v":1000000,"d":"1,000,000.00"}}]
[]
["And for the purposes of knowing our future cash flows, we need the par amounts in each:"]
["Bond:",{"$f":"B148/(A85/100)","v":{"v":932732.8865165598,"d":"932,732.89"}}]
["Bill:",{"$f":"B149/B91","v":{"v":62667.797971617394,"d":"62,667.80"}}]
[]
[]
[]
[]
[{"$nulls":3},0,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]
[{"$nulls":3},1000000.0000000002,978625.99777165,958135.1344837091,938939.6647404553,920442.1066668386,901947.485127231,883618.1358415375,865350.2175856653,846875.6762044374,827844.3746267303,809028.5485461814,789567.4699049633,769793.438036668,749733.058590936,729366.1164340021,708077.3887031246,686431.2241588217,664418.2481810234,642038.398529936,619339.4286742576,595598.2231660199,571715.1201144235,547280.9930257204,522181.64472545066,496760.31943590497,470554.1627333401,443776.21202408173,416517.2938206546,388563.66574804235,360029.4070884756,330710.3916336115,300880.4368305561,270238.15805393894,239020.87098295632,207040.05147285812,174353.38248002692,140931.20459277765,106776.92165140224,71827.98675460408,36099.83838938852,-431.5121218170534]
[]
[]
[]
[{"$nulls":3},2.963962174060288,{"$nulls":1000},-431.6145385013833]
[{"$nulls":3},-1034.780529761134,{"$nulls":1000},-431.97173495509924]
[{"$nulls":3},1770.6068643913823,{"$nulls":1000},-431.31107249596243]

{"sheet":"2FVas","index":1,"n_rows":1136,"n_cols":61,"rows_kept":1136}
["Here we consider the dynamic hedging problem of an annuity using the 2-factor Vasicek model."]
[null,"In this model all risk comes from the uncertain future movement of the factor. "]
[null,"Therefore we want to build an asset that has the same factor duration on both factors as our liability (the annuity)."]
[]
["Parameters of the model:"]
[null,"Factor 1","Factor 2"]
["theta",0.025,0.008,"  (long-run mean)"]
["k",1,0.1,"  (magnitude of the pull to the long-run mean)"]
["sigma",0.01,2E-3,"  (annual standard deviation)"]
["lambda",0.01,0.0006,"  (risk premium)"]
["rho:",-0.6,null,"  Correlation between the 2 factor shocks"]
["f(t)",0.02,0.01,"Current Factor level"]
[]
["Term Structure:"]
["Term","spot rate","df"]
[0.5,{"$f":"LN(C16)/-A16","v":0.03329202086830443},{"$f":"vas2fZ($B$12,$B$8,$B$7,$B$9,$B$10,$C$12,$C$8,$C$7,$C$9,$C$10,$B$11,A16)","v":0.9834917688464542}]
[1,{"$f":"LN(C17)/-A17","v":0.03570539299245183},{"$f":"vas2fZ($B$12,$B$8,$B$7,$B$9,$B$10,$C$12,$C$8,$C$7,$C$9,$C$10,$B$11,A17)","v":0.9649245251396096}]
[1.5,0.037506636423867416,0.9452933705539934]
[2,0.03887615059876888,0.9251935675021132]
[2.5,0.039937248751428164,0.904979378365244]
[3,0.040775060157324204,0.8848605823321489]
[3.5,0.041448954063050734,0.8649605852956019]
[4,0.04200077388055931,0.8453512178771153]
[4.5,0.04246033673042033,0.8260735091546927]
[5,0.042849134032177084,0.8071500679763302]
[5.5,0.04318284026221482,0.7885924579417095]
[6,0.04347302676520231,0.7704055915118498]
[6.5,0.04372834179835158,0.7525903510777372]
[7,0.043955329751807826,0.7351451558889889]
[7.5,0.0441590047553545,0.7180669022417008]
[8,0.044343255887515316,0.7013515308497716]
[8.5,0.04451113606005143,0.6849943722066085]
[9,0.04466506992018881,0.6689903594932701]
[9.5,0.04480700491762658,0.6533341622232369]
[10,0.04493852214888981,0.6380202722287752]
[10.5,0.04506091849052975,0.6230430607835661]
[11,0.04517526805764384,0.6083968180537596]
[11.5,0.04528246864154846,0.5940757815571938]
[12,0.04538327713580841,0.5800741576313618]
[12.5,0.04547833681670146,0.566386138319105]
[13,0.04556819854393209,0.5530059151345781]
[13.5,0.04565333738302901,0.5399276906083528]
[14,0.045734165749823995,0.5271456881739892]
[14.5,0.045811043890250515,0.5146541607566908]
[15,0.04588428830147914,0.5024473983030412]
[15.5,0.04595417854969943,0.49051973441683283]
[16,0.04602096282939189,0.4788655522203778]
[16.5,0.046084862527330905,0.4674792895320188]
[17,0.04614607599381215,0.456355443432055]
[17.5,0.046204781678031194,0.4454885742769215]
[18,0.04626114075010338,0.43487330921283984]
[18.5,0.04631529930600088,0.4245043452338346]
[19,0.046367390231583774,0.41437645182416327]
[19.5,0.04641753478638688,0.4044844732212955]
[20,0.04646584395576549,0.39482333033231615]
[20.5,0.04651241961057165,0.38538802233380565]
[21,0.046557355506109586,0.37617362798277026]
[21.5,0.04660073814624088,0.3671753066639654]
[22,0.04664264753382997,0.3583882991969416]
[22.5,0.04668315782497198,0.34980792842430075]
[23,0.04672233790142887,0.34142959960095975]
[23.5,0.04676025187325725,0.3332488006026745]
[24,0.04679695952162804,0.3252611019706348]
[24.5,0.04683251669021561,0.31746215680763307]
[25,0.0468669756322043,0.30984770054008015]
[25.5,0.046900385318863765,0.30241355055902125]
[26,0.04693279171473698,0.29515560575226263]
[26.5,0.046964238023731786,0.2880698459387532]
[27,0.04699476490977707,0.2811523312154815]
[27.5,0.0470244106951795,0.2743992012263218]
[28,0.04705321153937414,0.2678066743615003]
[28.5,0.04708120160038943,0.2613710468956574]
[29,0.04710841318103236,0.25508869207182194]
[29.5,0.047134876861531984,0.24895605913802005]
[30,0.047160621620151784,0.24296967234268127]
[]
[]
[]
["30-Year Bond:",null,{"v":0.0425,"d":"4.25%"},null,30]
["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]
["CF:",{"$f":"50*$C$79","v":2.125},{"$f":"50*$C$79","v":2.125},2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,2.125,{"$f":"50*$C$79+100","v":102.125}]
["df:",{"$f":"vas2fZ($B$12,$B$8,$B$7,$B$9,$B$10,$C$12,$C$8,$C$7,$C$9,$C$10,$B$11,B80)","v":0.9834917688464542},{"$f":"vas2fZ($B$12,$B$8,$B$7,$B$9,$B$10,$C$12,$C$8,$C$7,$C$9,$C$10,$B$11,C80)","v":0.9649245251396096},0.9452933705539934,0.9251935675021132,0.904979378365244,0.8848605823321489,0.8649605852956019,0.8453512178771153,0.8260735091546927,0.8071500679763302,0.7885924579417095,0.7704055915118498,0.7525903510777372,0.7351451558889889,0.7180669022417008,0.7013515308497716,0.6849943722066085,0.6689903594932701,0.6533341622232369,0.6380202722287752,0.6230430607835661,0.6083968180537596,0.5940757815571938,0.5800741576313618,0.566386138319105,0.5530059151345781,0.5399276906083528,0.5271456881739892,0.5146541607566908,0.5024473983030412,0.49051973441683283,0.4788655522203778,{"$f":"vas2fZ($B$12,$B$8,$B$7,$B$9,$B$10,$C$12,$C$8,$C$7,$C$9,$C$10,$B$11,AH80)","v":0.4674792895320188},0.456355443432055,0.4454885742769215,0.43487330921283984,0.4245043452338346,0.41437645182416327,0.4044844732212955,0.39482333033231615,0.38538802233380565,0.37617362798277026,0.3671753066639654,0.3583882991969416,0.34980792842430075,0.34142959960095975,0.3332488006026745,0.3252611019706348,0.31746215680763307,0.30984770054008015,0.30241355055902125,0.29515560575226263,0.2880698459387532,0.2811523312154815,0.2743992012263218,0.2678066743615003,0.2613710468956574,0.25508869207182194,0.24895605913802005,0.24296967234268127]
["PV:",{"$f":"B81*B82","v":2.0899200087987153},{"$f":"C81*C82","v":2.0504646159216704},2.008748412427236,1.9660363309419906,1.9230811790261435,1.8803287374558164,1.8380412437531541,1.79637133798887,1.755406206953722,1.7151938944497016,1.6757589731261326,1.6371118819626809,1.5992544960401915,1.5621834562641013,1.525892167263614,1.4903720030557648,1.455613040939043,1.421604513923199,1.3883350947243784,1.3557930784861474,1.3239665041650779,1.292843238364239,1.2624110358090368,1.232657584966644,1.2035705439280981,1.1751375696609785,1.1473463425427497,1.1201845873697271,1.093640091607968,1.0677007213939624,1.0423544356357697,1.0175892984683028,0.9933934902555399,0.9697553172931168,0.9466632203384582,0.9241057820772847,0.9020717336218986,0.8805499601263469,0.8595295055952529,0.8389995769561718,0.818949547459337,0.7993689594633868,0.7802475266609266,0.7615751357935009,0.7433418479016392,0.7255378991520395,0.7081537012806832,0.6911798416875989,0.6746070832162203,0.6584263636476704,0.6426287949379201,0.6272056622235581,0.6121484226198505,0.5974487038328982,0.5830983026059338,0.5690891830181881,0.555413474653272,0.5420634706526216,0.5290316256682926,24.813277787996324]
[{"$f":"SUM(B83:BI83)","v":92.86277454815078}]
["wt:",{"$f":"B83/$A$84","v":0.022505465930431137},{"$f":"C83/$A$84","v":0.02208058746789299},0.021631363290633417,0.021171414923883955,0.020708849034324257,0.020248466046863988,0.0197930898866273,0.019344364270070604,0.018903228074921632,0.0184701986645935,0.0180455406515689,0.017629366448811124,0.017221696248273882,0.01682249387728648,0.01643168831308627,0.016049186666105737,0.01567488208296302,0.01530865861848738,0.01495039429394289,0.01459996306467397,0.0142572361272555,0.013922082822260278,0.013594371285497798,0.013273968939270622,0.012960742878773543,0.0126545601871033,0.01235528819944781,0.012062794729322826,0.01177694826510809,0.011497618142352005,0.011224674695620825,0.010957989392624351,0.010697434952694096,0.010442885451266414,0.01019421641173987,0.009951304885878911,0.009714029523791155,0.009482270634394713,0.00925591023720246,0.009034832106175521,0.0088189218063337,0.008608066723753787,0.008402156089535708,0.008201080998270329,0.008004734421500672,0.007813011216629511,0.007625808131691075,7.4430238063715355E-3,0.007264558769632994,0.007090315434267648,0.006920198088683073,6.7541128861957735E-3,6.5919678320880025E-3,0.006433672768662665,0.006279139358512148,0.00612828106619953,5.9810131385346615E-3,0.005837252583612536,0.005696918148767799,0.2672037090075341]
["f1 dur:",{"$f":"fdur($B$8,B80)","v":0.3934693402873666},{"$f":"fdur($B$8,C80)","v":0.6321205588285577},0.7768698398515701,0.8646647167633873,0.9179150013761012,0.950212931632136,0.9698026165776814,0.9816843611112658,0.9888910034617577,0.9932620530009145,0.995913228561536,0.9975212478233337,0.9984965608070224,0.9990881180344455,0.9994469156298523,0.9996645373720975,0.9997965316309894,0.9998765901959134,0.9999251481701124,0.9999546000702375,0.9999724635506503,0.9999832982992097,0.9999898699064014,0.9999938557876467,0.9999962733468281,0.9999977396705931,0.9999986290409135,0.9999991684712809,0.9999994956523375,0.9999996940976794,0.9999998144608636,0.9999998874648253,0.9999999317439663,0.9999999586006227,0.9999999748900085,0.9999999847700203,0.9999999907625504,0.9999999943972036,0.9999999966017322,0.9999999979388464,0.9999999987498471,0.9999999992417439,0.9999999995400943,0.9999999997210532,0.9999999998308102,0.9999999998973812,0.9999999999377586,0.9999999999622486,0.9999999999771025,0.9999999999861122,0.9999999999915765,0.9999999999948908,0.9999999999969013,0.9999999999981204,0.99999999999886,0.9999999999993084,0.9999999999995807,0.9999999999997456,0.9999999999998457,0.9999999999999063]
["f1dur*wt:",{"$f":"B85*B86","v":0.008855210832506543},{"$f":"C85*C86","v":0.013957593289467364},0.016804753735365514,0.01830617548864027,0.01900896318983922,0.019240354283444398,0.0191953903622084,0.01899005987956786,0.018693232179675723,0.018345747444928888,0.017971792651442425,0.017585667618352886,0.017195804475164674,0.016807153748504132,0.016422600203105164,0.016043802763771025,0.015671692740271165,0.015306769379926444,0.014949275229572446,0.0145993002273763,0.014256843533595015,0.013921850299798604,0.013594233573244262,0.013273887381186688,0.012960694578579983,0.012654531583628779,0.012355271260853187,0.012062784698762576,0.011776942325431762,0.011497614625203934,0.011224672613004376,0.0109579881594651,0.010697434222529615,0.01044288501893746,0.010194216155763183,9.951304734320739E-3,9.714029434058297E-3,0.009482270581267481,0.009255910205748398,0.009034832087553344,0.0088189217953087,8.608066717226668E-3,8.402156085671509E-3,0.008201080995982664,0.008004734420146353,0.00781301121582775,7.625808131216434E-3,0.007443023806090551,7.2645587694666535E-3,0.007090315434169179,0.006920198088624781,0.006754112886161265,0.006591967832067575,6.4336727686505725E-3,6.27913935850499E-3,6.1282810661952925E-3,0.005981013138532153,0.005837252583611051,0.005696918148766919,0.26720370900750906]
[{"$f":"SUM(B87:BI87)","v":0.9663594790457939}]
["f2dur",{"$f":"fdur($C$8,B80)","v":0.48770575499285984},{"$f":"fdur($C$8,C80)","v":0.9516258196404048},1.3929202357494217,1.8126924692201818,2.211992169285951,2.5918177931828206,2.9531191028128654,3.2967995396436067,3.6237184837822665,3.9346934028736658,4.230501896195133,4.511883639059736,4.77954223238984,5.034146962085906,5.276334472589853,5.506710358827784,5.725850680512734,5.934303402594009,6.132589765454988,6.321205588285577,6.500622508888447,6.671289163019204,6.833632306209467,6.988057880877979,7.134952031398098,7.274682069659875,7.407597393541084,7.534030360583935,7.654297119062022,7.768698398515702,7.877520261732569,7.981034820053446,8.079500913792458,8.173164759472654,8.262260565495549,8.347011117784135,8.427628336863723,8.504313807773649,8.577259284134865,8.646647167633873,8.712650964121957,8.775435717470181,8.83515842226503,8.891968416376661,8.946007754381355,8.997411562771962,9.046308377844504,9.092820467105874,9.137064135006295,9.179150013761012,9.219183339988467,9.25726421785666,9.293487869395703,9.327944872602501,9.360721387932925,9.391899373747819,9.421556791251614,9.449767799435927,9.476602940515676,9.50212931632136]
["f1dur*wt:",{"$f":"B85*B89","v":0.010976045253067003},{"$f":"C85*C89","v":0.02101245714727532},0.030130763654370486,0.038377264395260215,0.045807811898850186,0.05248033458492029,0.05845135184789121,0.063774491220267,0.06849997697824539,0.07267456883534203,0.07634169394432858,0.07954165004737955,0.08231182453201469,0.08468690644705049,0.08669908348918888,0.08837822246500521,0.08975203424169066,0.09084622492883976,0.09168463503655082,0.09228936811318011,0.09268091008337466,0.09287824025880081,0.09289893479918408,0.09275926325659958,0.09247427873133371,0.09205790209255209,0.09152300066267852,0.09088146172421004,0.09014426117695934,0.0893215276492351,0.08842260234610991,0.08745609490031125,0.08642993547552733,0.08535142335750033,0.08422727225484586,0.08306365251889085,0.08186623047983316,0.08064020508512955,0.07939034201516373,0.07812100544091015,0.07683618757846947,0.0755395361859955,0.07423438013964682,0.07292375321676654,0.07161041620650836,0.0702968774605694,0.06898541198955171,0.06767807920373137,0.06637673939065908,0.0650830690160278,0.06379857492860702,0.0625246075447447,0.06126237308295654,0.060012944914449244,0.05877727409103609,0.05755619910778999,0.05635045495392637,0.05516068150179591,0.05398743128049004,2.5390041967902914]
[{"$f":"SUM(B90:BI90)","v":6.855304441953882}]
[]
[]
["20-Year Annuity",null,"Semi-annual cash flow:",null,{"v":38336.653801275264,"d":"38,336.65"},"Value",1000000,null,20]
["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]
["CF:",{"$f":"$E$94","v":38336.653801275264},{"$f":"$E$94","v":38336.653801275264},38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264,38336.653801275264]
["df:",{"$f":"B82","v":0.9834917688464542},{"$f":"C82","v":0.9649245251396096},0.9452933705539934,0.9251935675021132,0.904979378365244,0.8848605823321489,0.8649605852956019,0.8453512178771153,0.8260735091546927,0.8071500679763302,0.7885924579417095,0.7704055915118498,0.7525903510777372,0.7351451558889889,0.7180669022417008,0.7013515308497716,0.6849943722066085,0.6689903594932701,0.6533341622232369,0.6380202722287752,0.6230430607835661,0.6083968180537596,0.5940757815571938,0.5800741576313618,0.566386138319105,0.5530059151345781,0.5399276906083528,0.5271456881739892,0.5146541607566908,0.5024473983030412,0.49051973441683283,0.4788655522203778,0.4674792895320188,0.456355443432055,0.4454885742769215,0.43487330921283984,0.4245043452338346,0.41437645182416327,0.4044844732212955,0.39482333033231615]
["PV:",{"$f":"B96*B97","v":37703.783458670354},{"$f":"C96*C97","v":36991.977464637144},36239.38468756906,35468.825496495316,34693.881125681655,33922.59380726242,33159.69451022591,32407.936980241386,31668.894134868046,30943.432721684367,30231.99605040804,29534.772448356478,28851.79574344742,28183.005335000704,27528.28223739425,26887.470831182123,26260.392103106642,25646.85180828418,25046.645593698446,24459.562294629955,23885.386124546476,23323.898187524435,22774.877569280165,22238.102160179893,21713.349302580733,21200.39631857173,20699.020952574483,20209.00175036123,19730.11838831512,19262.15196209515,18804.885241031607,18358.102892829123,17921.59168205513,17495.14064518215,17078.54124447804,16671.58750270757,16274.076120366553,15885.806576983765,15506.581217876,15136.205327616548]
[{"$f":"SUM(B98:BC98)","v":1000000},"Criterion:",{"$f":"(A99-G94)^2","v":0}," (To Use Solver to obtain the payment)"]
["wt:",{"$f":"B98/$A$99","v":0.037703783458670356},{"$f":"C98/$A$99","v":0.036991977464637144},0.03623938468756906,0.03546882549649531,0.034693881125681655,0.03392259380726242,0.033159694510225914,0.032407936980241386,0.03166889413486804,0.03094343272168437,0.03023199605040804,0.029534772448356478,0.028851795743447418,0.028183005335000705,0.02752828223739425,0.026887470831182123,0.026260392103106642,0.02564685180828418,0.025046645593698447,0.024459562294629957,0.023885386124546475,0.023323898187524436,0.022774877569280164,0.022238102160179893,0.021713349302580735,0.021200396318571728,0.020699020952574483,0.02020900175036123,0.01973011838831512,0.019262151962095152,0.018804885241031608,0.018358102892829124,0.017921591682055128,0.01749514064518215,0.01707854124447804,0.01667158750270757,0.016274076120366552,0.015885806576983765,0.015506581217876,0.015136205327616548]
["f1dur:",{"$f":"B86","v":0.3934693402873666},{"$f":"C86","v":0.6321205588285577},0.7768698398515701,0.8646647167633873,0.9179150013761012,0.950212931632136,0.9698026165776814,0.9816843611112658,0.9888910034617577,0.9932620530009145,0.995913228561536,0.9975212478233337,0.9984965608070224,0.9990881180344455,0.9994469156298523,0.9996645373720975,0.9997965316309894,0.9998765901959134,0.9999251481701124,0.9999546000702375,0.9999724635506503,0.9999832982992097,0.9999898699064014,0.9999938557876467,0.9999962733468281,0.9999977396705931,0.9999986290409135,0.9999991684712809,0.9999994956523375,0.9999996940976794,0.9999998144608636,0.9999998874648253,0.9999999317439663,0.9999999586006227,0.9999999748900085,0.9999999847700203,0.9999999907625504,0.9999999943972036,0.9999999966017322,0.9999999979388464]
["f1dur*wt:",{"$f":"B100*B101","v":0.01483528280382075},{"$f":"C100*C101","v":0.023383389467119844},0.028153284978551216,0.030668641951857128,0.03184603394122237,0.03223368731016497,0.03215835850093367,0.03181436490938243,0.03131708449955383,0.030734937512035893,0.030108444792421474,0.029461563066862772,0.028808418822938935,0.028157305760700593,0.027513056774751736,0.026878451089559444,0.026255048943955844,0.025643686735327083,0.025044770806443212,0.024458451832219757,0.02388472840582126,0.023323508638755645,0.02277464685763869,0.022237965524557885,0.02171326838445868,0.021200348398692493,0.020698992575063628,0.02020898494599589,0.01973010843747603,0.019262146069758165,0.018804881751989442,0.018358100826896807,0.01792159045879836,0.017495139920894222,0.017078540815636017,0.016671587248799632,0.016274075970035596,0.015885806487978823,0.015506581165180486,0.015136205296418504]
[{"$f":"SUM(B102:AO102)","v":0.9436414726806689}]
["f2dur:",{"$f":"B89","v":0.48770575499285984},{"$f":"C89","v":0.9516258196404048},1.3929202357494217,1.8126924692201818,2.211992169285951,2.5918177931828206,2.9531191028128654,3.2967995396436067,3.6237184837822665,3.9346934028736658,4.230501896195133,4.511883639059736,4.77954223238984,5.034146962085906,5.276334472589853,5.506710358827784,5.725850680512734,5.934303402594009,6.132589765454988,6.321205588285577,6.500622508888447,6.671289163019204,6.833632306209467,6.988057880877979,7.134952031398098,7.274682069659875,7.407597393541084,7.534030360583935,7.654297119062022,7.768698398515702,7.877520261732569,7.981034820053446,8.079500913792458,8.173164759472654,8.262260565495549,8.347011117784135,8.427628336863723,8.504313807773649,8.577259284134865,8.646647167633873]
["f2dur*wt:",{"$f":"B100*B104","v":0.018388352177798127},{"$f":"C100*C104","v":0.035202520874904705},0.05047857226242267,0.06429407286958183,0.07674259337214548,0.0879211822205761,0.09792452730158704,0.10684247171725882,0.11475915703746514,0.1217529205922766,0.12789651661701498,0.13325745659309185,0.13789837623609236,0.14187739068964467,0.1452484245403462,0.1480615141487505,0.1503630839941044,0.15219619995172512,0.15360080242689336,0.15461392186383408,0.15526987867471861,0.15560046921779502,0.15563513912739826,0.15540114505621472,0.15492370571490488,0.15422614296839696,0.15333001365714302,0.15225523274431538,0.15102018833843306,0.1496418490998947,0.14813586450578223,0.14651665841779313,0.14479751637177973,0.1429906669832204,0.14110735784044018,0.13915792623621115,0.1371518650682784,0.13509788422026447,0.13300396771621825,0.13087742692476037]
[{"$f":"SUM(B105:AO105)","v":5.155460956371478}]
[]
[]
["3-year STRIP",null,3]
[{"$f":"100*G97","v":88.48605823321489}]
["f1 dur:",{"$f":"G101","v":0.950212931632136}]
["f2dur:",{"$f":"G104","v":2.5918177931828206}]
[]
[]
["6-month Bill:"]
[{"$f":"100*B97","v":98.34917688464542}]
["f1 dur:",{"$f":"B101","v":0.3934693402873666}]
["f2dur:",{"$f":"B104","v":0.48770575499285984}]
[]
[]
[]
[{"$nulls":46},{"$f":"AVERAGE(D122:AR122)","v":{"$error":"#DIV/0!"}}]
[{"$nulls":46},{"$f":"AVERAGE(D123:AR123)","v":{"$error":"#DIV/0!"}}]
[{"$nulls":46},{"$f":"AVERAGE(D124:AR124)","v":{"$error":"#DIV/0!"}}]
[]
[]
[null,"   ",null,"wts","Factor durations"]
[null,null,"Bill ",0.02966682477229993," Factor 1:",{"$f":"D128*B117+D129*B111+D130*A88","v":0.9436414726806688},"  We can run the subroutine Chkwts in Module 8 to get the initial weights on the 3 assets."]
[null,null,"Strip ",0.35439000414168714,"Factor 2:",{"$f":"D128*B118+D129*B112+D130*A91","v":5.155460956371477},"     This is our hedge portfolio, which should have the same factor durations as the annuity."]
[null,null,"Bond",0.6159431710860128,null,null,"     Also the weights must sum to 1."]
[{"$nulls":3},{"$f":"SUM(D128:D130)","v":0.9999999999999999},null,null,"Here we verify that both conditions are met.  We obtain the weights by solving 3 equation in 3 unknowns, algebraically."]
[]
["We run the subroutine Monte in Module 7 to simulate NDRAWS paths from the Vasicek model."]
["  In each period we rebalance the hedge portfolio."]
[]
[]
[null,null,0.3749936910159006,null,"  We tell the subroutine to report the average terminal cash in the position"]
[null,null,253.46354789660836,null,"    -- and the maximium across all of the paths"]
[null,null,-110.33112890726625,null,"   -- and the minimum."]
{"$rows_empty":426}
[null,-25163.197333752854]
[-148.71143591604778]
[223.06934265341988]
[451.44131586913136]
[-9.4077314136739]
[173.52106012064178]
[-122.86812136109074]
[135.8879333044315]
[-505.1991068020361]
[-166.90142044069216]
[125.69995550611202]
[-256.380061419768]
[-355.06349715969554]
[475.9834246154278]
[836.2262222700374]
[-2769.0900693892036]
[895.8274758828702]
[960.4735835315005]
[163.2410847577994]
[43.69658797704324]
[-69.52837337893288]
[-129.36958983028308]
[-313.8445817935208]
[-77.11323444994923]
[-154.87533766534762]
[-281.70505563684856]
[-1393.9017059094695]
[-1423.8282233218997]
[869.279482693375]
[115.3764140134881]
[-187.17957462836785]
[-39.05025590506557]
[-221.82038042732893]
[-806.5147261641323]
[548.4451188010571]
[327.7055686482272]
[182.96405618791323]
[-464.0499352416082]
[1712.9025183968624]
[153.34877823814895]
[396.55033505643223]
[-222.70547217643616]
[-500.55493294788903]
[146.05424071707967]
[2342.0256973396026]
[-315.5270870778404]
[-22.05046874639811]
[255.25015050306683]
[982.6272502066349]
[-370.0073617684029]
[292.15277615509694]
[448.334592812178]
[-213.680809999365]
[4.209882539660612]
[-82.23388985096244]
[-1444.171073640071]
[236.6460753145584]
[130.66232733274956]
[-246.24781001218798]
[-159.294130563183]
[-297.1539157831721]
[-248.637225217557]
[436.96851369489013]
[-314.2297706154495]
[645.1768966777599]
[-227.74296573428]
[-2315.8831965689023]
[-21.35702140081412]
[-78.47823932627944]
[-1167.6576225400422]
[129.71448927480378]
[641.8002827597884]
[1165.0140389420994]
[-106.37540943737986]
[-190.70936499607342]
[-773.6380311763642]
[-146.37652303588402]
[1649.721423224677]
[16.550636879343074]
[231.04990555750555]
[776.6505063854493]
[-129.79611205351102]
[-728.6621189695434]
[136.00423227245483]
[-374.8470575542742]
[-61.00141825238097]
[2194.799513052909]
[-128.65186911885394]
[145.01179563283222]
[11.385350480319175]
[272.43332193277456]
[-104.41092217103869]
[672.5214558428124]
[28.427962011868658]
[95.38709733307041]
[189.83508260972303]
[207.10469537267636]
[68.73283875566995]
[164.03448973775085]
[-562.2655031431204]
[49.26217742973677]
[251.05496772121842]
[352.73791956640343]
[-133.64091929700226]
[308.5552809981673]
[2297.467523498366]
[-562.6960410267784]
[138.38987241120049]
[-588.9407891613082]
[-623.8834893172752]
[-74.903381644217]
[-181.50748028309317]
[94.04086893987551]
[-80.09264746114059]
[268.1523819766662]
[-190.8753274523333]
[-774.0820013334305]
[6149.8817233993395]
[294.01347225115023]
[145.65979334037547]
[-4703.452478275387]
[357.93664911932865]
[-480.41369697447226]
[163.91756971450377]
[-482.8898051699143]
[481.0160317326663]
[-356.8007631149667]
[445.86767776056513]
[13.958995189299458]
[-125.13451821333001]
[18.21524691065133]
[207.30970837784844]
[350.3572987863008]
[647.6292315987303]
[-1182.8843214144]
[150.62314401481126]
[-320.37615633664245]
[218.60425716599275]
[-429.5922260937077]
[3714.8800686744507]
[-320.7413441575336]
[12.810833494375402]
[-490.3126077757188]
[-165.29165255874977]
[-117.70962240835797]
[24.93085928272194]
[-77.76470752858586]
[1159.9400699590915]
[306.11791446607094]
[-110.39578380223247]
[-182.93704959096067]
[951.644611158139]
[111.10289607376035]
[827.1351165236774]
[-18.392245907351025]
[141.73121116910625]
[36.71803896580241]
[-154.07787359722715]
[236.66995858441805]
[-106.92178274331673]
[-1510.150973768512]
[656.0653265220317]
[366.18855534480826]
[115.27820640165737]
[-425.87092901038704]
[-157.22585797827924]
[-473.13520009010244]
[-128.44738114227948]
[627.3619840816755]
[-461.8648806630299]
[182.0587115443268]
[833.6204004575193]
[-299.27760164574283]
[8337.266770798036]
[105.63954126978206]
[463.3117340399476]
[-497.00476441864157]
[-124.52446954871266]
[-35.65900243780197]
[467.3559278267203]
[-246.12912796121236]
[7.542557433698676]
[145.39431500002684]
[319.29726778317126]
[-206.56122319852147]
[236.23521328120114]
[-329.62229923631094]
[2.4042332490644185]
[-681.9690351682584]
[76.32484338316135]
[-10.44472590188525]
[-196.43724998125253]
[51.78669821742369]
[176.01736442484253]
[-210.32136872586852]
[438.131366580601]
[-217.07194647066353]
[-426.37602855436126]
[140.63143278763164]
[72.40350723090523]
[41.700957300083246]
[-144.16758752231544]
[-301.0559574512299]
[591.304331427149]
[-405.51845804242475]
[-91.80284829128504]
[110.99018286773207]
[835.8832174746349]
[206.47688952244062]
[-5750.550342244438]
[-561.08277795295]
[-176.05879481841112]
[-411.7437443761519]
[29.87018693590653]
[3.072465656368877]
[-383.52189461767557]
[-163.83921219553304]
[241.213135753831]
[71.26885217822564]
[-140.1114200631273]
[180.46511962347722]
[-248.59771907100367]
[16.340279471820395]
[500.2913917634796]
[-601.2820412828878]
[-5529.195700722768]
[206.95385470127803]
[-14905.134691143667]
[-137.39731456573645]
[53.30839399517572]
[452.09260065862327]
[151.79658099491644]
[-627.2165658430968]
[1405.6307793312371]
[-1378.894597408631]
[85.7891372100421]
[192.73261749874655]
[609.5479239694469]
[207.85950067304657]
[-137.12673918379733]
[-217.32754153048154]
[-233.8214289461539]
[935.0654209311615]
[83.68628495265875]
[-59.75435607485997]
[-253.9114136416465]
[-33.201338625658536]
[-607.0009828256079]
[61.90407902494189]
[367.9743866584831]
[-704.0344123763716]
[24.241778347895888]
[-700.0292019101616]
[174.4328987599365]
[155.36558682667237]
[-355.2470276866443]
[136.64249988133088]
[-534.6032802635455]
[162.12186330260738]
[1522.5266681003632]
[-32.18068980539829]
[427.60525568315643]
[367.3927545702827]
[32.83936081761931]
[265.75961372865277]
[-1150.7919435796284]
[22.958560007900815]
[2328.220630130825]
[854.2728666548282]
[599.7170892149952]
[10.607735642588523]
[357.8071440779895]
[-900.8405503979375]
[-623.8454219880296]
[-205.6069398607433]
[253.03472840656468]
[-2973.8913768810235]
[359.44576413711184]
[110.32456524882582]
[1738.5458102303965]
[143.2887905537209]
[-288.1723357915398]
[-404.5651118257301]
[-121.61316123168945]
[95.15231971209869]
[-25.961897710287303]
[81.71474313063663]
[378.1357357909437]
[-61.51170061574521]
[2745.552684216913]
[-463.07501703770686]
[232.46489476175338]
[173.02623931138078]
[8503.631803462471]
[490.27212950125977]
[38.071675604798656]
[317.40846168485587]
[-713.0915439955861]
[-203.71712712053704]
[-40.21790742000303]
[97.94567986478069]
[655.8516504315485]
[506.9826959747588]
[296.4532669823384]
[-92.6265135304784]
[-42.02831481103931]
[-40.40205508823419]
[188.8678908126676]
[-248.80210922681]
[-70.40330882694252]
[9454.571331349704]
[119.41001824653358]
[17.91289867796877]
[39.61981115589151]
[-285.47168060484546]
[-798.2858769829327]
[576.1986029940526]
[-767.5317014167085]
[-418.92995715550205]
[-924.0965222500308]
[1950.9168980736722]
[158.22216878613108]
[103.86832501459867]
[-265.64148603979265]
[183.92544243832526]
[-1139.4750819490582]
[17109.553016088495]
[-132.2036626703848]
[400.7352911067064]
[-373.6233970229805]
[-192.47303055834345]
[225.4208051693422]
[432.46009464847157]
[311.2687861757149]
[219.47079642489552]
[441.9637759229081]
[-165.92743115717894]
[707.2660377193388]
[-463.375842071604]
[213.88099957627128]
[227.5235027663948]
[218.900078099643]
[186.8976611111866]
[-219.5881504130084]
[160.66661059692706]
[945.6833366269348]
[55.864783189448644]
[221.46097133845615]
[-608.9723735773514]
[-3428.7950594235226]
[188.55791754835082]
[75.76366869400226]
[880.9639336215114]
[48.59890087441454]
[202.24404363711074]
[204.84727859611303]
[8337.628653562526]
[40.956038705924584]
[-152.82520659421425]
[426.4396762619144]
[54.96518917432695]
[-235.0964370547954]
[81.04659689300752]
[-438.2662851526038]
[-1513.1402091959753]
[-504.30695312652824]
[54.745030271136784]
[-1796.0393526564585]
[-38.70193937172007]
[-119.82991010127444]
[13.508997426717542]
[-107.78511547171365]
[1385.9097639764514]
[10.30290519157279]
[482.73027277534857]
[947.8107505212975]
[292.16458519542357]
[292.7066795593928]
[-373.2851573723019]
[123.80474427161971]
[140.54999721948116]
[-149.27001750302588]
[-510.35793169795943]
[1785.5510334251594]
[-921.4272893575035]
[59.48836769582704]
[119.28836018390575]
[3165.260959101208]
[-61.659991342668945]
[36.231612932846474]
[654.7402867762648]
[2531.063014828178]
[912.7851300753246]
[-200.3182217332942]
[-310.9069224050327]
[92.64560822208296]
[-388.93650082617387]
[-386.59217637016263]
[-142.76452865472675]
[201.0898988125482]
[-797.5615405466742]
[37.277210914253374]
[-179.03954744370276]
[-195.10555789852515]
[308.3882641113887]
[220.68641931182356]
[416.51595406203705]
[-141.14291954893997]
[48.45633384442772]
[-1177.5231986637082]
[225.2284518781962]
[287.54053368184395]
[-638.4921672904602]
[321.4809570422731]
[119.81476797525102]
[-7601.886646076364]
[559.952297711272]
[-52.079121417533315]
[-125.34836117564555]
[818.7996396634044]
[948.7168619462027]
[-1967.4657580505736]
[-746.7745002393203]
[-925.7717307677376]
[451.7450388161087]
[-2571.0024965582998]
[446.61887712832686]
[-236.7961818212425]
[-157.83887868619058]
[536.0384678474657]
[-227.28272012197704]
[27.94422109804873]
[131.8776068690131]
[-117.11041221702908]
[67.09574522724142]
[-18.30447170311527]
[204.8216892164346]
[-586.4702388377409]
[1495.5557385691573]
[4291.889197107717]
[626.7679783378844]
[329.97655488104647]
[-298.3761663941332]
[-116.20258398637088]
[60.744044217601186]
[-59.172440803093195]
[4788.238723622104]
[-370.4744287920912]
[67.97639423338842]
[-663.3599220713731]
[90.4045487442927]
[-45.28981237788685]
[-59.551682365075976]
[-302.5015829992408]
[-92.13002698407945]
[-119.19422399245377]
[-193.84891378144675]
[-24118.210900348633]
[101.20156964031776]
[1029.1760156131204]
[-831.7095253034859]
[-3318.874537157324]
[287.83466237322136]
[-738.4849038326574]
[-125.19398575240484]
[1021.3105358308821]
[488.0709999084356]
[-70.5825463374349]
[2555.794335345665]
[11.778388507700583]
[-76.80557938098354]
[835.9834843009958]
[-55.190015278130886]
[-108.13844434558268]
[-183.63143798086094]
[-32.25669122744148]
[-302.07802116865787]
[-564.2237925271984]
[391.6288392510178]
[-12.965486242486804]
[503.0273260349277]
[1246.4064731989565]
[49.27344536528835]
[-326.140026323068]
[-141.065394221514]
[17.266299187431287]
[-3675.93171927207]
[-219.14873970967892]
[-232.14581357920542]
[41.39175943085138]
[69.38008787526633]
[362.9561015361396]
[-344.87190806795115]
[-2549.0154699545164]
[190.2005990791804]
[-190.75915415205236]
[456.11655493172293]
[-366.27367349845736]
[-104.98155237288302]
[456.18909036369587]
[-274.67917467532243]
[-259.6622448440103]
[-65.86955113496515]
[179.59897627287864]
[174.36912282891717]
[23.067886907789216]
[169.213182639789]
[1143.158162319196]
[3827.276834737182]
[386.48977380736324]
[-161.11617716668843]
[-37.57428019739018]
[1030.1575887526778]
[340.5210169332204]
[-1578.0464323907]
[-2848.961381126057]
[106.3257992165818]
[-282.6573016991533]
[17746.045840447645]
[-181.4987262290815]
[null,165064.31037115553]
[775.8839855522892]
[-49.162086341384565]
[235.34549738895294]
[340.5673856409194]
[9.81577311692672]
[-99.18402434833843]
[-137.59793336250732]
[-1109.7845858952714]
[546.9134456609681]
[348.84127134728624]
[623.7206722051851]
[102.93302076543478]
[-260.3277644441114]
[-2.830853221072175]
[448.02365168297547]
[208.862689283058]
[917.9559718363671]
[17.52594465162838]
[235.93282785086194]
[-896.7449275394756]
[-20.292009228207462]
[292.89934101639665]
[644.603565568752]
[-4.86069415917882]
[59710.97987686814]
[54.43495276628528]
[526.201974501957]
[0.911103708887822]
[-3764.4660954618084]
[507.61481351620023]
[-6788.358523084902]
[175.09600150663755]
[8.122497667551215]
[387.4728654994178]
[-298.8975561502957]
[1010.3661091835966]
[1178.7537697133375]
[-81.79127131866699]
[184.40247439230006]
[124.33266103108326]
[-0.3968380592195899]
[149.24153484159615]
[-1.7132781557302224]
[211.60239100947365]
[15.622828279221721]
[]
[]
[{"$f":"AVERAGE(A132:A1131)","v":158.87764897650928}]
[{"$f":"MIN(A132:A1131)","v":-24118.210900348633}]
[{"$f":"MAX(A132:A1131)","v":59710.97987686814}]