EXCEL VBA Coding:

Overview:

Column D, compares a random number to mortality column B. If rand is greater than mortality then "T" (survives), if less than the mortality rate then "F" (dies).

A death benefit rate (Cell B3) of $100,000 will be paid out at death age +1 (i.e. x+1, in column G). Simulated 1000 policyholders (column G13 and on) and found the ages of death benefit payout (column H13 and on), used lookup to find the first "F", used data table to simulated 1000 times. Column I13 and on compute the present value of the death benefit paid out given that policy holder had died within 20 years of current age, 50 (B2). Cell I11 totals the 1000 present value of payouts.

VBA Coding: Wish to write a VBA code to print out a new simulated cell I11 1000 times by processing a loop of overview process silently over and over. With allowability to change variables colored in yellow.

TARGET Profitability Rate 0.975 2 Ace, so O 3 D8 100000 4 io-/1.06 I 5 Policyholders 1000 I Simulated Profitability Rate Gross Premium Net Single Gross Premium -F6BS 10 11 age Total PV Loss -SUMI13:1012) mortality survival X+1 Policyholder 13 15 2 16 78 65 67 69 0.006593 =1-813 0.000461 =1-814 0.000281 =1-815 0.000219 =1-816 0.01247 1-877) FIF(RAND) >877, "T", "F) 0.013556 1-878 -IF(RAND) >878, "T", "FI 0.014791 1-879 (RAND) >879, "T", "F) 67 0.016128 1-880 (RAND) >B80, "T", "F") 0.017526 1-881 -F(RAND) >B81, "T, "F" 0.019016 1-882 -IFRAND) >B82, "T, "F") 0.020614 (1-883 ) -IF(RAND() >B83, "T", "F") 71 0.02247 1-884 -IF(RANDV>884, "T", "F") 72 0.024658 1-885 -F(RAND) >B85, "T", "F") 0.027108 =1-886) =F(RAND) >B86, "T", "F") 74 0.029742 =1-87) =F(RAND) >B87,"T", "F") 0.03255 =1-888) =F(RAND)B88, "T", "F") 0.035608 =1-889 ) =F(RAND) >B89, "T", "F") 77 0.039071 1-890 =F(RAND) >890, "T", "F") 0.043101 =1-89: FIF(RAND) >891, "T", "F) age of payout PV DB =FNA(VLOOKUP("F",D63:E112, 2, 0), 100) =IF(H13>70,0, $8$6 +$B$7*(1-Scs4A (H13-1-$B$2DSB$4 +SBS3*scs4H13) TABLELG11) FIF(H14>70,0, $8$6+SBS7*(1-Scs4 % (H14-1-$B$2])/SB$4 +$B$3*SC$4H14) -TABLELG11) FIF(H15>70,0, $8$6+SBS7*(1-Scs4 (H15-1-$BS 2))/$8$4 +$B$3*SC$4H15) -TABLELG11) IF(H16>70,0, $8$6+$7(1-Scs4m (H16-1-$832/SD$4 +SBS3'SC$49H16 -TABLELG11) FIF(H7770,0, SBS6 +SD$7(1-Scs4 (H77-1-$652)/SD$4 +SBS3's C$4NH77) -TABLELG11) -IF(H7870,0, SBS6 +$8$7(1-Scs4m (H78-1-$62DSD$4 +SBS3's CS4MH78 ) -TABLELG11) -IF(H79>70,0, SBS6 +SBS71Scs4m (H79-1-$8522IVSD$4 +SBS3'SCS44WH79) TABLELG11) -IF (80-70,0, SBS6 + SBS7(1-Scs4m (H80-1-SBS2/SBS4 +SBS3'SC$4H80) TABLELG11) -IF (H8170,0, SBS6 + SBS7(1-SCS4m (H81-1-SBS2/SD$4 +SBS3'SC$4H81) -TABLELG11) -IF (H8270,0, SBS6 + SBS7(1-SCS4m (H82-1-SBS2/SD$4 +SBS3'SC$4H82) TABLELG11) -F (H8370,0, SBS6 +SBS7(1-Scs4m (H83-1-SBS2/SB$4 +SBS3'SC$4H83) -TABLELG11 ) F(H8470,0, $8$6 + $8$7*(1-$C$4W (H84-1-$B$2/$8$4 + SB$3*SC$4H84) -TABLELG11 ) -F(H8570,0, $8$6 + $8$7*(1-$C$4A (H85-1-$B$2)/SB4 +$B$3*SC$4H8S) -TABLELG 11 ) =F(H86>70,0, $8$6 + $B$7*(1-$C$4(H86-1-$B$2)/SB4 + $B$3*SC$4H86) -TABLELG11 =F(H8770,0, $866 + $B$7*(1-$C$4A (H87-1-$B$2))/SB$4 + $B$3*SC$4H87) -TABLELG11 =F(H88>70,0, $8$6 +$B$7*(1-$C$4% (H88-1-$B$2)/SB$4 + $B$3*$C$4H88) TABLELG11) FIF(H89>70,0, $8$6 +$B$7*(1-$C$4 % (H89-1-$B$2)V/SB$4 + $B$3*SC$4H89) TABLELG11) FIF(H90>70,0, $8$6 +$8$7*(1-Scs 4% (H90-1-$B$2])/SB$4 +$B$3*SC$4NH90) TABLE-G11) FIF(H9170,0, $8$6+$8$7*(1-Scs 4 (H91-1-$B$2])/SB$4 +SBS3*SC$4H91) 83 70 84 7 86 73 87 74 72 73 74 75 76 91 TARGET Profitability Rate 0.975 2 Ace, so O 3 D8 100000 4 io-/1.06 I 5 Policyholders 1000 I Simulated Profitability Rate Gross Premium Net Single Gross Premium -F6BS 10 11 age Total PV Loss -SUMI13:1012) mortality survival X+1 Policyholder 13 15 2 16 78 65 67 69 0.006593 =1-813 0.000461 =1-814 0.000281 =1-815 0.000219 =1-816 0.01247 1-877) FIF(RAND) >877, "T", "F) 0.013556 1-878 -IF(RAND) >878, "T", "FI 0.014791 1-879 (RAND) >879, "T", "F) 67 0.016128 1-880 (RAND) >B80, "T", "F") 0.017526 1-881 -F(RAND) >B81, "T, "F" 0.019016 1-882 -IFRAND) >B82, "T, "F") 0.020614 (1-883 ) -IF(RAND() >B83, "T", "F") 71 0.02247 1-884 -IF(RANDV>884, "T", "F") 72 0.024658 1-885 -F(RAND) >B85, "T", "F") 0.027108 =1-886) =F(RAND) >B86, "T", "F") 74 0.029742 =1-87) =F(RAND) >B87,"T", "F") 0.03255 =1-888) =F(RAND)B88, "T", "F") 0.035608 =1-889 ) =F(RAND) >B89, "T", "F") 77 0.039071 1-890 =F(RAND) >890, "T", "F") 0.043101 =1-89: FIF(RAND) >891, "T", "F) age of payout PV DB =FNA(VLOOKUP("F",D63:E112, 2, 0), 100) =IF(H13>70,0, $8$6 +$B$7*(1-Scs4A (H13-1-$B$2DSB$4 +SBS3*scs4H13) TABLELG11) FIF(H14>70,0, $8$6+SBS7*(1-Scs4 % (H14-1-$B$2])/SB$4 +$B$3*SC$4H14) -TABLELG11) FIF(H15>70,0, $8$6+SBS7*(1-Scs4 (H15-1-$BS 2))/$8$4 +$B$3*SC$4H15) -TABLELG11) IF(H16>70,0, $8$6+$7(1-Scs4m (H16-1-$832/SD$4 +SBS3'SC$49H16 -TABLELG11) FIF(H7770,0, SBS6 +SD$7(1-Scs4 (H77-1-$652)/SD$4 +SBS3's C$4NH77) -TABLELG11) -IF(H7870,0, SBS6 +$8$7(1-Scs4m (H78-1-$62DSD$4 +SBS3's CS4MH78 ) -TABLELG11) -IF(H79>70,0, SBS6 +SBS71Scs4m (H79-1-$8522IVSD$4 +SBS3'SCS44WH79) TABLELG11) -IF (80-70,0, SBS6 + SBS7(1-Scs4m (H80-1-SBS2/SBS4 +SBS3'SC$4H80) TABLELG11) -IF (H8170,0, SBS6 + SBS7(1-SCS4m (H81-1-SBS2/SD$4 +SBS3'SC$4H81) -TABLELG11) -IF (H8270,0, SBS6 + SBS7(1-SCS4m (H82-1-SBS2/SD$4 +SBS3'SC$4H82) TABLELG11) -F (H8370,0, SBS6 +SBS7(1-Scs4m (H83-1-SBS2/SB$4 +SBS3'SC$4H83) -TABLELG11 ) F(H8470,0, $8$6 + $8$7*(1-$C$4W (H84-1-$B$2/$8$4 + SB$3*SC$4H84) -TABLELG11 ) -F(H8570,0, $8$6 + $8$7*(1-$C$4A (H85-1-$B$2)/SB4 +$B$3*SC$4H8S) -TABLELG 11 ) =F(H86>70,0, $8$6 + $B$7*(1-$C$4(H86-1-$B$2)/SB4 + $B$3*SC$4H86) -TABLELG11 =F(H8770,0, $866 + $B$7*(1-$C$4A (H87-1-$B$2))/SB$4 + $B$3*SC$4H87) -TABLELG11 =F(H88>70,0, $8$6 +$B$7*(1-$C$4% (H88-1-$B$2)/SB$4 + $B$3*$C$4H88) TABLELG11) FIF(H89>70,0, $8$6 +$B$7*(1-$C$4 % (H89-1-$B$2)V/SB$4 + $B$3*SC$4H89) TABLELG11) FIF(H90>70,0, $8$6 +$8$7*(1-Scs 4% (H90-1-$B$2])/SB$4 +$B$3*SC$4NH90) TABLE-G11) FIF(H9170,0, $8$6+$8$7*(1-Scs 4 (H91-1-$B$2])/SB$4 +SBS3*SC$4H91) 83 70 84 7 86 73 87 74 72 73 74 75 76 91