4 WorkbookView::show_horizontal_scrollbar TRUE 4 WorkbookView::show_vertical_scrollbar TRUE 4 WorkbookView::show_notebook_tabs TRUE 4 WorkbookView::do_auto_completion TRUE 4 WorkbookView::is_protected FALSE 2009-04-22T22:24:48Z Greg Chicares 0 2009-04-22T22:24:48Z 2009-04-22T22:24:48Z Greg Chicares -535 2009-04-22T19:24:48Z Sheet1 aChgPol Sheet1!$H$9:$H$109 A1 aDt Sheet1!$V$9:$V$109 A1 aDtEnd Sheet1!$V$4 A1 am Sheet1!$T$9:$T$109 A1 aNt Sheet1!$Y$9:$Y$109 A1 Ax Sheet1!$AB$4 A1 curr_int_rate Sheet1!$F$5 A1 DBO Sheet1!$C$3 A1 dGAAV Sheet1!$AW$9:$AW$109 A1 dGPAV Sheet1!$AS$9:$AS$109 A1 Dur Sheet1!$A$9:$A$109 A1 dV Sheet1!$AC$9:$AC$109 A1 EaDt Sheet1!$U$9:$U$109 A1 EndtBft Sheet1!$C$6 A1 EndtDur Sheet1!$C$4 A1 f Sheet1!$L$9:$L$109 A1 g Sheet1!$M$9:$M$109 A1 GAAV Sheet1!$AX$9:$AX$109 A1 GALoad Sheet1!$AV$9:$AV$109 A1 GAt Sheet1!$AU$9:$AU$109 A1 GAx Sheet1!$AU$4 A1 GPAV Sheet1!$AT$9:$AT$109 A1 GPLoad Sheet1!$AR$9:$AR$109 A1 GPx Sheet1!$AQ$4 A1 gtDen Sheet1!$AM$9:$AM$109 A1 gtGAt Sheet1!$AP$9:$AP$109 A1 gtGAx Sheet1!$AP$4 A1 gtGPt Sheet1!$AO$9:$AO$109 A1 gtGPx Sheet1!$AO$4 A1 gtNum Sheet1!$AK$9:$AK$109 A1 guar_int_rate Sheet1!$F$6 A1 i Sheet1!$O$9:$O$109 A1 ic Sheet1!$E$9:$E$109 A1 ig Sheet1!$F$9:$F$109 A1 Int7702 #REF! A1 leDen Sheet1!$AG$9:$AG$109 A1 leGAt Sheet1!$AJ$9:$AJ$109 A1 leGAx Sheet1!$AJ$4 A1 leGPt Sheet1!$AI$9:$AI$109 A1 leGPx Sheet1!$AI$4 A1 leNum Sheet1!$AE$9:$AE$109 A1 LoadExc Sheet1!$K$9:$K$109 A1 LoadTgt Sheet1!$J$9:$J$109 A1 mChgPol Sheet1!$G$9:$G$109 A1 mChgSA Sheet1!$I$9:$I$109 A1 mCt Sheet1!$X$9:$X$109 A1 mDt Sheet1!$W$9:$W$109 A1 mMt Sheet1!$Z$9:$Z$109 A1 p Sheet1!$Q$9:$Q$109 A1 Pt Sheet1!$AA$9:$AA$109 A1 Px Sheet1!$AA$4 A1 q Sheet1!$N$9:$N$109 A1 qc Sheet1!$D$9:$D$109 A1 SgtDen Sheet1!$AN$9:$AN$109 A1 SgtNum Sheet1!$AL$9:$AL$109 A1 SleDen Sheet1!$AH$9:$AH$109 A1 SleNum Sheet1!$AF$9:$AF$109 A1 SpecAmt Sheet1!$C$2 A1 TgtPrem Sheet1!$C$5 A1 tV Sheet1!$AD$9:$AD$109 A1 v Sheet1!$P$9:$P$109 A1 vp Sheet1!$R$9:$R$109 A1 vp12 Sheet1!$S$9:$S$109 A1 Sheet1 56 130 1 Print_Area #REF! A1 Sheet_Title "Sheet1" A1 d_then_r portrait na_letter Input parameters UL transformations Commutation functions Net premiums and values Gross premiums <= target Gross premiums > target Gross annual premium values Gross single premium values SpecAmt 1000000 black Normal le-, gt- <=, > target DBO 2 either 1 or 2 dark red Input aDtEnd Px Ax Num numerator leGPx leGAx gtGPx gtGAx GPx GAx EndtDur 100 # of q's aqua Formula differs from other rows =index(EaDt,EndtDur) =index(Pt,1) =index($AB4,1) Den denominator =index(leGPt,1) =index(leGAt,1) =index(gtGPt,1) =index(gtGAt,1) =if(leGPx<=TgtPrem,leGPx,gtGPx) =if(leGAx<=TgtPrem,leGAx,gtGAx) TgtPrem 1000000 curr_int_rate 0.070000000000000007 light green Conditional: zero relative error S- backward sum as for N and M EndtBft 10000000 guar_int_rate 0.029999999999999999 G- gross worst: =max(BD9:BD108) Eckley symbol Q Q'' 1/(1+Q'') Q' i' 1/(1+i') 1-Q' (1-Q')/(1+i') 1/(1+i'') 12/a'' E(D) D 12*D(12) C(12) N(12) M(12) P(x) A(x) tV Dur q q upper 12 qc ic ig mChgPol aChgPol mChgSA LoadTgt LoadExc f g q i v p vp vp12 am EaDt aDt mDt mCt aNt mMt Pt At dV tV leNum SleNum leDen SleDen leGPt leGAt gtNum SgtNum gtDen SgtDen gtGPt gtGAt GPt GPLoad dGPAV GPAV GAt GALoad dGAAV GAAV gnumeric OOo calc reserve excel reserve lmi reserve absolute error relative error 0 0.0041799999999999997 =-expm1(ln1p(-B9)/12) =min(C9/(1-C9),1/11) =expm1(ln1p(curr_int_rate)/12) =expm1(ln1p(guar_int_rate)/12) 0 0 0 0 0 =qc*(1+ic)/(1+ig) =1/(1+f) =f*g =(ic+ig*f)*g-if(2=DBO,q,0) =1/(1+i) =1-q =v*p =vp^12 =(1-vp12)/(1-vp) =product(index(vp12,1):index(vp12,1+Dur)) 1 =am*aDt =mDt*v*q =sum(index(aDt,1+Dur):index(aDt,EndtDur)) =sum(index(mCt,1+Dur):index(mCt,EndtDur)) =(mMt+aDtEnd*EndtBft/SpecAmt)/aNt =(mMt+aDtEnd)/aDt =Px*aDt-mCt =sum(index(dV,1):index(dV,1+Dur))/EaDt =mChgPol*mDt+aChgPol*aDt+mChgSA*SpecAmt*mDt+SpecAmt*mCt =sum(index(leNum,1+Dur):index(leNum,EndtDur)) =(1-LoadTgt)*aDt =sum(index(leDen,1+Dur):index(leDen,EndtDur)) =(SleNum+EndtBft*aDtEnd)/SleDen =(SleNum+EndtBft*aDtEnd)/leDen =leNum+TgtPrem*(LoadTgt-LoadExc)*aDt =sum(index(gtNum,1+Dur):index(gtNum,EndtDur)) =(1-LoadExc)*aDt =sum(index(gtDen,1+Dur):index(gtDen,EndtDur)) =(SgtNum+EndtBft*aDtEnd)/SgtDen =(SleNum+TgtPrem*(LoadTgt-LoadExc)*aDt+EndtBft*aDtEnd)/gtDen =GPx =if($AQ9<=TgtPrem,$AQ9*LoadTgt,$AQ9*LoadExc+TgtPrem*(LoadTgt-LoadExc)) =($AQ9-GPLoad)*aDt-mChgPol*mDt-aChgPol*aDt-mChgSA*SpecAmt*mDt-SpecAmt*mCt =sum(index(dGPAV,1):index(dGPAV,1+Dur))/EaDt =GAx =if(GAt<=TgtPrem,GAt*LoadTgt,GAt*LoadExc+TgtPrem*(LoadTgt-LoadExc)) =(GAt-GALoad)*aDt-mChgPol*mDt-aChgPol*aDt-mChgSA*SpecAmt*mDt-SpecAmt*mCt =sum(index(dGAAV,1):index(dGAAV,1+Dur))/EaDt -473.7380465701965 -473.73804657023788 -473.7380465702276 -473.73804657019099 =abs(BC9-AT9) =abs(BD9/AT9) 1 0.00107 0 0 0 0 0 =index(EaDt,Dur) 0 2247.2957305030623 2247.2957305025011 2247.2957305025097 2247.29573050307 2 0.00098999999999999999 0 0 0 0 0 0 5241.6987680372276 5241.6987680371694 5241.6987680371776 5241.6987680372404 3 0.00097999999999999997 0 0 0 0 0 0 8456.0783939155081 8456.078393915217 8456.0783939152279 8456.0783939155408 4 0.00095 0 0 0 0 0 0 11926.553664244135 11926.553664244379 11926.553664244393 11926.5536642441 5 0.00089999999999999998 0 0 0 0 0 0 15691.769467938338 15691.769467939022 15691.769467939039 15691.7694679384 6 0.00085999999999999998 0 0 0 0 0 0 19761.995744489806 19761.995744489876 19761.995744489897 19761.995744489901 7 0.00080000000000000004 0 0 0 0 0 0 24179.297721835148 24179.297721835053 24179.297721835079 24179.297721835301 8 0.00076000000000000004 0 0 0 0 0 0 28947.25118959766 28947.251189598177 28947.251189598202 28947.251189597799 9 0.00073999999999999999 0 0 0 0 0 0 34069.685539281098 34069.685539281003 34069.685539281047 34069.685539281301 10 0.00072999999999999996 0 0 0 0 0 0 39561.05727030681 39561.057270306395 39561.057270306439 39561.057270307101 11 0.00076999999999999996 0 0 0 0 0 0 45395.407505165545 45395.407505165385 45395.407505165414 45395.4075051658 12 0.00084999999999999995 0 0 0 0 0 0 51555.313767551917 51555.313767552085 51555.313767552128 51555.313767552303 13 0.00098999999999999999 0 0 0 0 0 0 58001.406638022701 58001.406638023414 58001.406638023487 58001.406638023 14 0.00115 0 0 0 0 0 0 64732.98066624107 64732.980666241237 64732.980666241303 64732.980666241398 15 0.00133 0 0 0 0 0 0 71749.271581903158 71749.271581902649 71749.271581902722 71749.271581903799 16 0.0015100000000000001 0 0 0 0 0 0 79070.180589403331 79070.180589403491 79070.180589403593 79070.180589404103 17 0.00167 0 0 0 0 0 0 86737.735362196647 86737.735362197112 86737.735362197214 86737.735362197505 18 0.0017799999999999999 0 0 0 0 0 0 94828.018387170698 94828.01838717144 94828.018387171556 94828.0183871716 19 0.0018600000000000001 0 0 0 0 0 0 103401.71825648408 103401.71825648531 103401.71825648546 103401.71825648499 20 0.0019 0 0 0 0 0 0 112534.14688037297 112534.14688037413 112534.14688037433 112534.146880374 21 0.00191 0 0 0 0 0 0 122295.52312623152 122295.52312623237 122295.52312623258 122295.523126232 22 0.00189 0 0 0 0 0 0 132760.98042296196 132760.98042296281 132760.98042296307 132760.98042296301 23 0.0018600000000000001 0 0 0 0 0 0 143990.17355349104 143990.17355349241 143990.17355349273 143990.173553492 24 0.00182 0 0 0 0 0 0 156046.93141163106 156046.93141163248 156046.9314116328 156046.93141163199 25 0.0017700000000000001 0 0 0 0 0 0 168999.54872846557 168999.54872846743 168999.54872846775 168999.54872846699 26 0.00173 0 0 0 0 0 0 182900.36914114156 182900.36914114357 182900.36914114395 182900.36914114299 27 0.0017099999999999999 0 0 0 0 0 0 197795.03867539173 197795.03867539391 197795.03867539432 197795.03867539301 28 0.0016999999999999999 0 0 0 0 0 0 213742.7657886681 213742.7657886706 213742.76578867095 213742.76578866999 29 0.0017099999999999999 0 0 0 0 0 0 230796.54205221511 230796.54205221779 230796.54205221814 230796.542052217 30 0.00173 0 0 0 0 0 0 249023.43369061797 249023.43369062088 249023.43369062126 249023.43369062 31 0.0017799999999999999 0 0 0 0 0 0 268474.47675648436 268474.47675648751 268474.47675648791 268474.47675648599 32 0.00183 0 0 0 0 0 0 289235.3697424967 289235.36974250025 289235.36974250077 289235.36974249902 33 0.00191 0 0 0 0 0 0 311366.72094979475 311366.72094979812 311366.72094979871 311366.72094979801 34 0.002 0 0 0 0 0 0 334954.10731375118 334954.10731375532 334954.1073137559 334954.10731375398 35 0.0021099999999999999 0 0 0 0 0 0 360078.73181948776 360078.73181949224 360078.73181949294 360078.73181949102 36 0.0022399999999999998 0 0 0 0 0 0 386827.47969990317 386827.47969990806 386827.4796999087 386827.47969990602 37 0.0023999999999999998 0 0 0 0 0 0 415282.94906869507 415282.94906870031 415282.94906870089 415282.94906869897 38 0.0025799999999999998 0 0 0 0 0 0 445543.88042290777 445543.88042291207 445543.88042291283 445543.88042291102 39 0.0027899999999999999 0 0 0 0 0 0 477705.55232006684 477705.55232007249 477705.55232007313 477705.55232007097 40 0.0030200000000000001 0 0 0 0 0 0 511880.27353523311 511880.27353523736 511880.27353523794 511880.27353523701 41 0.00329 0 0 0 0 0 0 548167.46222161106 548167.46222161525 548167.46222161606 548167.46222161502 42 0.0035599999999999998 0 0 0 0 0 0 586714.97732497286 586714.97732497717 586714.9773249781 586714.97732497705 43 0.0038700000000000002 0 0 0 0 0 0 627639.52370972501 627639.52370972978 627639.52370973071 627639.52370972896 44 0.0041900000000000001 0 0 0 0 0 0 671097.09401227091 671097.09401227674 671097.0940122779 671097.09401227604 45 0.0045500000000000002 0 0 0 0 0 0 717223.45138142747 717223.4513814362 717223.45138143736 717223.45138143306 46 0.0049199999999999999 0 0 0 0 0 0 766194.99536462233 766194.99536463141 766194.99536463269 766194.99536462803 47 0.0053200000000000001 0 0 0 0 0 0 818179.69613681454 818179.696136827 818179.69613682828 818179.69613682199 48 0.0057400000000000003 0 0 0 0 0 0 873367.65704133257 873367.65704134584 873367.65704134689 873367.65704134095 49 0.0062100000000000002 0 0 0 0 0 0 931931.09177375794 931931.0917737718 931931.09177377296 931931.09177376505 50 0.0067099999999999998 0 0 0 0 0 0 994075.02767494426 994075.02767495939 994075.02767496067 994075.02767495206 51 0.0073000000000000001 0 0 0 0 0 0 1059956.3906620615 1059956.3906620771 1059956.390662078 1059956.3906620599 52 0.0079600000000000001 0 0 0 0 0 0 1129763.7981569481 1129763.7981569632 1129763.7981569644 1129763.7981569499 53 0.0087100000000000007 0 0 0 0 0 0 1203678.1367352172 1203678.1367352323 1203678.136735233 1203678.13673521 54 0.0095600000000000008 0 0 0 0 0 0 1281882.3755191553 1281882.3755191718 1281882.3755191725 1281882.3755191499 55 0.01047 0 0 0 0 0 0 1364613.8297885812 1364613.829788598 1364613.8297885987 1364613.8297885801 56 0.01146 0 0 0 0 0 0 1452105.4271924573 1452105.4271924752 1452105.4271924759 1452105.42719245 57 0.012489999999999999 0 0 0 0 0 0 1544648.0360364802 1544648.0360364991 1544648.0360365002 1544648.03603648 58 0.01359 0 0 0 0 0 0 1642521.4301466555 1642521.4301466756 1642521.4301466772 1642521.4301466499 59 0.01477 0 0 0 0 0 0 1746014.332704088 1746014.3327041075 1746014.3327041096 1746014.33270409 60 0.016080000000000001 0 0 0 0 0 0 1855383.0421883364 1855383.0421883552 1855383.0421883578 1855383.0421883301 61 0.01754 0 0 0 0 0 0 1970880.3817607749 1970880.3817607891 1970880.3817607912 1970880.38176077 62 0.019189999999999999 0 0 0 0 0 0 2092734.2534161343 2092734.2534161501 2092734.2534161524 2092734.2534161301 63 0.021059999999999999 0 0 0 0 0 0 2221156.0768116913 2221156.0768117076 2221156.0768117104 2221156.0768116899 64 0.023140000000000001 0 0 0 0 0 0 2356381.5005388455 2356381.5005388563 2356381.5005388591 2356381.50053884 65 0.025420000000000002 0 0 0 0 0 0 2498672.0407087784 2498672.0407087831 2498672.0407087854 2498672.04070877 66 0.02785 0 0 0 0 0 0 2648359.2738531386 2648359.2738531423 2648359.2738531455 2648359.2738531302 67 0.030439999999999998 0 0 0 0 0 0 2805786.3756996673 2805786.3756996752 2805786.3756996794 2805786.37569965 68 0.033189999999999997 0 0 0 0 0 0 2971319.4149728394 2971319.4149728483 2971319.4149728529 2971319.4149728301 69 0.036170000000000001 0 0 0 0 0 0 3145274.0516226515 3145274.0516226613 3145274.051622666 3145274.0516226399 70 0.039510000000000003 0 0 0 0 0 0 3327846.6324216239 3327846.6324216342 3327846.6324216393 3327846.6324216099 71 0.043299999999999998 0 0 0 0 0 0 3519146.6791403396 3519146.6791403503 3519146.6791403573 3519146.67914032 72 0.047649999999999998 0 0 0 0 0 0 3719167.0328992754 3719167.0328992759 3719167.0328992829 3719167.0328992498 73 0.052639999999999999 0 0 0 0 0 0 3927805.1079189153 3927805.1079189111 3927805.1079189181 3927805.1079188902 74 0.058189999999999999 0 0 0 0 0 0 4145027.8670878024 4145027.8670877977 4145027.8670878052 4145027.8670877698 75 0.064189999999999997 0 0 0 0 0 0 4370910.3012963235 4370910.301296331 4370910.3012963403 4370910.3012963003 76 0.070529999999999995 0 0 0 0 0 0 4605645.0060366048 4605645.0060366131 4605645.0060366224 4605645.0060365899 77 0.077119999999999994 0 0 0 0 0 0 4849530.703628853 4849530.7036288558 4849530.703628866 4849530.7036288297 78 0.083900000000000002 0 0 0 0 0 0 5102948.3739129938 5102948.3739129901 5102948.3739129994 5102948.3739129696 79 0.091050000000000006 0 0 0 0 0 0 5366096.8333153743 5366096.8333153706 5366096.8333153799 5366096.8333153501 80 0.098839999999999997 0 0 0 0 0 0 5638870.0881736232 5638870.0881736204 5638870.0881736316 5638870.0881736102 81 0.10748000000000001 0 0 0 0 0 0 5920892.7498921454 5920892.7498921426 5920892.7498921547 5920892.7498921296 82 0.11724999999999999 0 0 0 0 0 0 6211406.5446756352 6211406.5446756333 6211406.5446756454 6211406.5446756296 83 0.12826000000000001 0 0 0 0 0 0 6509423.574587239 6509423.5745872371 6509423.5745872483 6509423.5745872296 84 0.14025000000000001 0 0 0 0 0 0 6814138.5238780752 6814138.5238780817 6814138.5238780919 6814138.5238780696 85 0.15295 0 0 0 0 0 0 7124963.3313962659 7124963.3313962715 7124963.3313962845 7124963.3313962603 86 0.16608999999999999 0 0 0 0 0 0 7441556.7986214105 7441556.7986214068 7441556.7986214189 7441556.7986214096 87 0.17954999999999999 0 0 0 0 0 0 7763670.9875472616 7763670.9875472467 7763670.9875472579 7763670.9875472505 88 0.19327 0 0 0 0 0 0 8091088.5230834456 8091088.5230834298 8091088.5230834419 8091088.5230834298 89 0.20729 0 0 0 0 0 0 8423497.5065672677 8423497.5065672528 8423497.5065672658 8423497.5065672491 90 0.22176999999999999 0 0 0 0 0 0 8760319.1124555394 8760319.1124555245 8760319.1124555357 8760319.1124555301 91 0.23698 0 0 0 0 0 0 9100519.3874404393 9100519.3874404337 9100519.3874404449 9100519.3874404393 92 0.25345000000000001 0 0 0 0 0 0 9442178.7813129779 9442178.7813129723 9442178.7813129853 9442178.7813129704 93 0.27211000000000002 0 0 0 0 0 0 9781775.4525861125 9781775.452586107 9781775.4525861237 9781775.4525861107 94 0.2959 0 0 0 0 0 0 10110922.159784527 10110922.159784524 10110922.159784539 10110922.1597845 95 0.32995999999999998 0 0 0 0 0 0 10411786.134120304 10411786.134120299 10411786.134120317 10411786.1341202 96 0.38455 0 0 0 0 0 0 10645096.296084711 10645096.296084721 10645096.296084739 10645096.2960847 97 0.48020000000000002 0 0 0 0 0 0 10716449.549065411 10716449.549065419 10716449.549065441 10716449.5490654 98 0.65798000000000001 0 0 0 0 0 0 10370818.742353965 10370818.742353974 10370818.742353996 10370818.742353899 99 1 =1/11 0 0 0 0 0 0 10000000 10000000.000000011 10000000.000000032 10000000 Spreadsheet for validating universal-life commutation functions. See Eckley, TSA XXXIX, page 18. See discussion here: http://lists.gnu.org/archive/html/lmi/2010-01/msg00042.html Copyright (C) 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010 Gregory W. Chicares. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License version 2 as published by the Free Software Foundation. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA http://savannah.nongnu.org/projects/lmi email: <gchicares@sbcglobal.net> snail: Chicares, 186 Belle Woods Drive, Glastonbury CT 06033, USA