{"text_format":"3","workbook":"xlsx","n_sheets":3,"sheets":["Basics","Sheet2","Sheet3"],"not_rendered":{"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Basics","index":0,"n_rows":96,"n_cols":17,"rows_kept":96}
["Portfolio value",null,1000000]
["Max Life ",null,30]
["Periods per year",null,2]
["Current interest rate",null,{"v":0.04,"d":"4%"},"  (flat yield curve --simplifying assumption is that this is the BEY)."]
[{"$nulls":4},"T",{"$f":"C2*C3","v":60}]
["Annuity Factor:",null,{"$f":"1/(C4/2)-(1/(C4/2))/(1+C4/C3)^(C3*C2)","v":34.760886677046486},null,"r",{"$f":"C4/C3","v":0.02}]
["Semi-Annual annuity payment:",null,null,{"$f":"C1/C6","v":28767.965825806335}]
[]
[]
[null,12.3480512612905,null,"subroutine duration used to compute the duration of the annuity."]
[]
[]
["30-year bond:",null,"(Assume we are on the dated date)",{"$nulls":3},{"v":0.04,"d":"4%"},"coupon"]
[]
[]
[]
[null,99.99999999999996,"  subroutine BondVal computes the bond value and its duration."]
[null,17.7280522052937]
[]
[]
["The idea of this exercise is to create an asset combining the bond and 6-month bill to match the duration of the annuity each period."]
[]
["Bill duration",null,0.5]
[]
["weight in bond * bond duration + (1-weight in bond) * bill duration = annuity duration"]
[]
[{"$f":"(B10-C23)/(B18-C23)","v":0.6877185604098601},"  this is the weight in the bond in our immunizing portfolio."]
[null,"Verify","Bond","Bill","Portfolio"]
[null,"Duration",{"$f":"B18","v":17.7280522052937},{"$f":"C23","v":0.5},{"$f":"A27*C29+(1-A27)*D29","v":12.3480512612905}]
[]
[]
["And as we move through time, we have to make the payment to the annuity holder and rebalance the portfolio to maintain immunization."]
[]
["Time","Interest","Coupon","Bill ","Payment","Bond","Asset","Annuity","Bond","Annuity",null,"$ in","Bond","$ in "]
[null,"Rate","Received","Matur","to An Hldr","Value","Value","Value","duration","Duration",null,"Bond","Par Units","Bill"]
[0,0.04,{"$nulls":3},{"$f":"B17","v":99.99999999999996},{"$f":"C1","v":1000000},{"$f":"C1","v":1000000},{"$f":"B18","v":17.7280522052937},{"$f":"B10","v":12.3480512612905},null,{"$f":"A27*G36","v":687718.5604098601},{"$f":"L36/F36","v":6877.185604098604},{"$f":"G36-L36","v":312281.4395901399}]
[0.5,0.051168451648474604,13754.371208197208,318527.0683819427,28767.965825806335,83.08996336189014,874938.5736418049,871145.5650787812,16.355032645509734,11.465815526197312,null,605133.7269259705,7282.8739169178625,269804.84671583434,null,null,{"$f":"M36*2","v":13754.371208197208}]
[1,0.051750036242654035,14565.747833835725,276707.59484268597,28767.965825806335,82.45463789709162,863012.1085203509,859136.7157585219,16.17473180238241,11.294898383189025,null,594340.5049854137,7208.090656188276,268671.60353493714]
[1.5,0.017685033335480688,14416.181312376551,275623.4861450896,28767.965825806335,149.78619381658447,1340944.1657069884,1283665.3926888853,19.45190174609921,13.313544610797866,null,906623.9429724867,6052.787108554623,434320.2227345017]
[2,0.033248764789597075,12105.574217109246,438160.7065431682,28767.965825806335,112.2399583626244,1100862.8879764718,1043123.7926437127,17.743775936993874,12.126295032285237,null,742236.3160176441,6612.941833242935,358626.57195882767]
[2.5,0.05615113120305267,13225.88366648587,364588.5172280069,28767.965825806335,77.50920668221133,861610.3103989545,801200.0712855664,15.383149142486143,10.639081335781746,null,586968.317877373,7572.885119105271,274641.9925215816]
[3,0.049187213619765406,15145.770238210542,282352.7217995552,28767.965825806335,86.35165740226044,922661.707563614,854742.405436087,15.907239761940783,10.880401826201663,null,621629.7936648215,7198.817166519713,301031.91389879247]
[3.5,0.04643460478329587,14397.634333039427,308435.3744264458,28767.965825806335,90.24830960718424,943746.1233832627,871961.2327788388,16.007039254749102,10.878630466689568,null,631635.227592914,6998.859373014036,312110.89579034864]
[4,0.030230499531555502,13997.718746028071,319357.2688376413,28767.965825806335,117.50418972251441,1126982.3213657024,1030881.6369316576,17.20322650215311,11.577281846338892,null,747394.5712225081,6360.578060982139,379587.75014319434]
[4.5,0.03487493864882272,12721.156121964277,385325.3137946384,28767.965825806335,108.61010881251136,1060101.579404539,966604.2964261973,16.62191378177902,11.151052572931594,null,700363.3568396121,6448.417780785187,359738.22256492684]
[5,0.031918631074730336,12896.835561570373,366011.14678572095,28767.965825806335,113.84703544575598,1084273.2641795792,985850.4077115776,16.64297327339732,11.118331938494846,null,713200.3030734818,6264.548745437435,371072.96110609744]
[5.5,0.045161880609470684,12529.09749087487,376995.0315797741,28767.965825806335,92.39718702978558,939582.845334234,847433.2525468621,15.45228576875249,10.310363117487078,null,616470.8884145134,6671.965978961946,323111.9569197205]
[6,0.04098821881001092,13343.931957923893,330408.12873067096,28767.965825806335,98.49953843029539,972169.664213174,873596.5501348133,15.573290093779313,10.333399891738312,null,634216.7510443098,6438.778913599909,337952.9131688642]
[6.5,0.0539721622828652,12877.557827199818,344878.95714508725,28767.965825806335,81.51786878664689,853864.0837715468,761075.2127977379,14.485550780235616,9.611875720052387,null,556310.1185931027,6824.3947845239745,297553.96517844417]
[7,0.035285222310199905,13648.789569047949,305583.7806267047,28767.965825806335,107.38487612147485,1023301.3929100924,901199.9201999081,15.54661361397305,10.225180570738958,null,661397.380145231,6159.129702743724,361904.01276486134]
[7.5,0.04381888163340088,12318.259405487448,368288.9445375421,28767.965825806335,94.57121869881823,934315.6402297872,817909.5868394028,14.804148121914679,9.700601942050696,null,600963.1766058549,6354.609625151892,333352.46362393233]
[8,0.024096482977269573,12709.219250303784,340656.02969680214,28767.965825806335,127.03330629961334,1131844.354047691,978014.1470270887,15.731730712782788,10.28885740892715,null,727393.570684255,5726.006760531502,404450.783363436]
[8.5,0.010192350863484644,11452.013521063003,409323.7040716662,28767.965825806335,157.41987922661662,1293395.044460582,1108341.7653958157,16.169025409350912,10.608902476733366,null,834436.3498536098,5300.705056776101,458958.6946069723]
[9,0.001772652213594397,10601.410113552201,461297.6286306128,28767.965825806335,178.76735218656344,1390724.0806401495,1185526.3977785797,16.244279021670366,10.684922300376064,null,899654.8322781887,5032.545491524088,491069.24836196087]
[9.5,2.6831254865761992E-3,10065.090983048176,491504.4958570294,28767.965825806335,174.38522206800576,1350403.1841210404,1146886.7927980495,15.884824664199918,10.406158272614864,null,869513.1706554148,4986.163164194768,480890.0134656256]
[10,0.035517837229936566,9972.326328389536,481535.15759131033,28767.965825806335,106.37860988345994,993160.6241530469,818799.4972834381,14.16021266910656,9.086779414874824,null,624298.5676517574,5868.647544235537,368862.0565012895]
[10.5,0.028889236195798532,11737.295088471074,375412.6477428457,28767.965825806335,116.47608722568293,1041939.0802646787,853196.9945616374,14.201949306947203,9.09642208807251,null,653698.8222158917,5612.300668628155,388240.258048787]
[11,0.038121883155943795,11224.60133725631,393848.2403065316,28767.965825806335,102.52256738524171,951692.3493036487,772786.129484874,13.56280480813896,8.624375702206319,null,591902.4522084829,5773.386945962184,359789.8970951658]
[11.5,0.026768536440902768,11546.773891924367,366647.83130404126,28767.965825806335,119.20603511789557,1037649.2063997271,835158.6496128488,13.748726581754356,8.745198384516337,null,645769.5015069728,5417.25509843778,391879.7048927543]
[12,0.04438147912902348,10834.51019687556,397124.7279731903,28767.965825806335,94.60759325599176,891704.7392612086,708110.5186733935,12.837014812256243,8.077868320537501,null,547719.2982055662,5789.379893890046,343985.4410556423]
[12.5,0.03727435906549882,11578.759787780093,351618.7323920918,28767.965825806335,103.48081944200996,933519.3018349041,734770.9926573007,12.84235461187125,8.064749497519832,null,572163.0832651951,5529.170394575702,361356.218569709]
[13,0.04126965206988597,11058.340789151403,368090.87929046806,28767.965825806335,98.45976770035767,894782.0868794316,697976.2599669794,12.453522804295403,7.774873482133565,null,544561.3467009239,5530.800644971922,350220.74017850775]
[13.5,0.034435606459679004,11061.601289943845,357447.4842259202,28767.965825806335,106.95964539053094,931313.5954019743,719628.6147042707,12.397954165330528,7.7301695385547164,null,565942.2699692477,5291.175638278156,365371.32543272653]
[14,0.06579621167600727,10582.351276556312,371662.2170198529,28767.965825806335,74.70981309080307,748779.3454369744,564072.9281258913,11.22043481795471,6.894371234762376,null,446621.1668604558,5978.079028488907,302158.1785765186]
[14.5,0.0581305584612096,11956.158056977814,312098.6103151473,28767.965825806335,81.64208183207288,783349.6197888087,582574.4017955039,11.218155710692104,6.868769772427079,null,465469.388056787,5701.341484826378,317880.2317320217]
[15,0.0620959316324888,11402.682969652757,327119.5094292023,28767.965825806335,78.63591759539099,758084.4457570398,556302.5834238412,10.87872318103743,6.620447487715312,null,447050.75572175975,5685.070758911857,311033.6900352801]
[15.5,0.08714424769508627,11370.141517823715,320690.6534111958,28767.965825806335,61.60592228665568,653526.8564708757,468570.21569822077,10.028673838165247,6.044032729510584,null,380239.0913388998,6172.119127924533,273287.7651319759]
[16,0.07828296992732289,12344.238255849066,285195.49348032457,28767.965825806335,67.78633055406573,687157.0733349316,484143.0764142699,10.038064420322609,6.020880529610684,null,397744.4416160453,5867.620187801848,289412.6317188863]
[16.5,0.06459642175303551,11735.240375603697,300740.67189160484,28767.965825806335,78.06368622404835,741756.0076638574,513134.0903055224,10.094280045199296,6.047426302431712,null,428884.37355548824,5494.031787386512,312871.6341083692]
[17,0.042083723796253435,10988.063574773025,322976.828124082,28767.965825806335,97.92995794236725,843227.2277458387,571581.5049867238,10.227425689981636,6.167183561152452,null,491262.9133028508,5016.472217745298,351964.3144429879]
[17.5,0.041592007241465556,10032.944435490595,359370.29894056614,28767.965825806335,98.46035101363819,834558.8929596164,556436.7833228566,9.933302649386167,5.967199901165212,null,483680.04999842355,4912.434751846729,350878.84296119283]
[18,0.040018826558635895,9824.869503693459,358175.72064985236,28767.965825806335,99.98219769193125,830388.6468355923,544057.1482461259,9.645851261229838,5.777123024995904,null,479131.2391540077,4792.1655076069055,351257.40768158465]
[18.5,0.027809425302691985,9584.331015213811,358285.8623193074,28767.965825806335,111.92824906101515,875480.9219857466,562978.3170514037,9.470697593254185,5.6967140498714475,null,507165.01814741583,4531.161904185121,368315.90383833076]
[19,0.04666768679240109,9062.323808370242,373437.2306461236,28767.965825806335,94.31397749394347,781083.4904814169,490650.82458709244,8.934040420151904,5.287785510847449,null,443400.7939484982,4701.326417677295,337682.6965329187]
[19.5,0.043303170099945255,9402.65283535459,345562.1316914245,28767.965825806335,97.2366465207871,783338.0337554982,481331.9087378542,8.64460599986851,5.108611526880156,null,443250.50123572693,4558.471698640589,340087.5325197713]
[20,0.04570150437632621,9116.943397281178,347450.96665455843,28767.965825806335,95.46434471624046,762971.4578442846,457709.2907009496,8.29192250639109,4.875656942014515,null,428456.69388228294,4488.133188949587,334514.76396200166]
[20.5,0.05568749496670442,8976.266377899174,342158.6779365792,28767.965825806335,88.54728569208251,719778.9901947083,420043.2185281005,7.880695335886928,4.589915109744076,null,398856.04725613754,4504.441261397145,320922.9429385708]
[21,0.056555320141109755,9008.88252279429,329858.6403233666,28767.965825806335,88.44774810093081,708507.2430438483,401483.9761609775,7.540871424171019,4.376282572585941,null,390061.7002227298,4410.080624976645,318445.5428211185]
[21.5,0.045208628757052935,8820.16124995329,327450.4376319974,28767.965825806335,96.3577102104518,732447.903922372,402337.24874911184,7.261183768462815,4.232414067788781,null,404337.3104683129,4196.211279670435,328110.59345405916]
[22,0.046235068187043045,8392.42255934087,335527.3084594196,28767.965825806335,95.86979401110352,717441.6762020278,381126.9457775263,6.89660664484958,4.0077138477896295,null,393424.23918180994,4103.735104888658,324017.4370202179]
[22.5,0.058633850961278454,8207.470209777315,331507.9211674282,28767.965825806335,88.82236206351996,675450.8708505966,345133.0665229659,6.481892843809497,3.731153959779431,null,364848.68802756426,4107.621994635183,310602.1828230323]
[23,0.03916683052955598,8215.243989270366,319708.083870979,28767.965825806335,100.50580962158142,711995.2362246672,349295.41766009585,6.1767276707149055,3.5926169761870823,null,387886.9451272153,3859.3484952528065,324108.2910974519]
[23.5,0.05875644583346487,7718.696990505613,330455.4383527708,28767.965825806335,89.98652530849876,656695.5305598533,307166.1873260848,5.740083501464504,3.297795175747698,null,350624.10490633256,3896.4067531699434,306071.42565352074]
[24,0.04387560948659245,7792.813506339887,315063.260224812,28767.965825806335,97.9748829847516,675838.1036081667,300641.7302334803,5.385785522997245,3.1208473042834086,null,362535.04449513135,3700.2855573867314,313303.0591130353]
[24.5,0.05045708963890408,7400.571114773463,320176.2404493345,28767.965825806335,95.0318288188538,650453.7494087545,273353.64559607813,4.975180335162271,2.875579177743075,null,345283.15452215617,3633.342205592216,305170.59488659835]
[25,0.03886560897816818,7266.684411184432,312869.60491727374,28767.965825806335,100.51100347508333,656559.194555098,259179.24640002844,4.582419028381979,2.6706580664049904,null,349098.29241566977,3473.234574781767,307460.9021394282]
[25.5,0.05793824172926566,6946.469149563534,313435.7297387411,28767.965825806335,92.98285231853389,614565.4905970572,225071.18338773638,4.147434643638383,2.4049144377578013,null,320963.85278016963,3451.8606901908647,293601.6378168875]
[26,0.06573011955045913,6903.721380381729,302107.0191488591,28767.965825806335,91.07728438729598,594628.8724492756,199523.6573357294,3.7205584859113987,2.165212552759121,null,307457.06589933886,3375.782095038232,287171.8065499367]
[26.5,0.08313688346679737,6751.564190076464,296609.72513796104,28767.965825806335,87.12920623609905,568722.5378689477,171670.51738260197,3.283389809900497,1.9186569889528116,null,289870.35888861207,3326.902325990823,278852.17898033565]
[27,0.06790272708806262,6653.804651981646,290443.62953451095,28767.965825806335,92.54026680999196,576201.8972586598,153821.05756364902,2.8499277644956296,1.701342988052184,null,294569.10098793096,3183.14514472661,281632.79627072887]
[27.5,0.04179052369618574,6366.29028945322,291194.6137228385,28767.965825806335,99.57911618014872,585767.7183714928,135245.02771909066,2.403625567252094,1.479323881390497,null,301349.34380867006,3026.2303519896536,284418.37456282275]
[28,0.04948319570725628,6052.460703979307,290361.3709737219,28767.965825806335,98.2151122141631,564867.419458465,108291.74554853074,1.9413383724272466,1.2347272839652772,null,287943.1456475303,2931.7600841269264,276924.2738109346]
[28.5,0.04086662893578376,5863.520168253853,283775.8228294728,28767.965825806335,99.87514256320318,553681.3335352056,82893.48131724945,1.470760448762896,0.9932580080028928,null,281333.82648211514,2816.855318169693,272347.50705309043]
[29,0.03277696747859898,5633.710636339386,277912.4693092526,28767.965825806335,100.70492727041655,538449.4240673506,56151.82699215859,0.9902301129185754,0.7479680894986928,null,272358.3710163252,2704.5188194712514,266091.0530510254]
[29.5,0.03679930953770444,5409.037638942503,270451.8819471252,28767.965825806335,100.15714314352462,517969.8322963997,28248.208540816762,0.5,0.5,null,517969.8322963997,5171.571552856213,0]
[30,null,10343.143105712426,0,28767.965825806335,100,498732.33256552735,0,0,0,null,498732.33256552735,4987.323325655274,0]

{"sheet":"Sheet2","index":1,"n_rows":0,"n_cols":0,"rows_kept":0}

{"sheet":"Sheet3","index":2,"n_rows":0,"n_cols":0,"rows_kept":0}