Home Forums SOA Exams Actuarial Life and Death 2

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #24925
    Richard Purvey
    Participant

      Actuarial Life and Death: How to write a spreadsheet to calculate APVs based on an inputted life table

      The sheet does not use any commutation functions, recursion formulae or approximations, it calculates exactly.

      It is a thirteen column sheet (columns A to M), with the first three columns (columns A to C) accommodating the input of the values for x, n and v respectively, columns D and E accommodating the input of the life table, and the last five columns (columns I to M) displaying the five calculated APVs, namely;

      APV1

      APV of an n-year temporary immediate life annuity, with a discount factor of v, of 1 per year payable once a year for (x)

      APV2

      APV of an n-year temporary life annuity due, with a discount factor of v, of 1 per year payable once a year for (x)

      APV3

      APV of an endowment insurance, with a discount factor of v, of 1, where the death benefit is payable at the end of the year of death for (x), provided this occurs within n years

      APV4

      APV of a death benefit, with a discount factor of v, of 1 payable at the end of the year of death for (x), provided this occurs within n years

      APV5

      APV of a pure endowment, with a discount factor of v, of 1 payable after n years as long as (x) is still alive then

      HOW TO WRITE THE SHEET

      COLUMNS A TO C

      Put the headings x, n and v into cells A1, B1 and C1 respectively, and then input the values for x, n and v into cells A2, B2 and C2 respectively.

      COLUMN D

      Put the heading, x values, into cell D1, and then input the x values, in ascending order, down column D, starting by inputting the lowest x value into cell D2

      COLUMN E

      Put the heading, lx values, into cell E1, and then input the lx values, in descending order, down column E, starting by inputting the highest lx value into cell E2

      COLUMN F

      Put the heading, r, into cell F1, then put the value, 0, into cell F2, then input the formulae, =F2+1 into cell F3 and then copy this formula down to, and including, cell F300

      COLUMN G

      Put the heading, v^rl(x+r) list for APV1 calc, into cell G1, then input the formula, =IF(AND(F2>0,F2<$B$2+1),$C$2^F2*LOOKUP($A$2+F2,$D$2:$D$300,$E$2:$E$300),0) into cell G2 and then copy this formula down to, and including, cell G300

      COLUMN H

      Put the heading, v^rl(x+r) list for APV2 calc, into cell H1, then input the formula, =IF(F2<$B$2,$C$2^F2*LOOKUP($A$2+F2,$D$2:$D$300,$E$2:$E$300),0) into cell H2 and then copy this formula down to, and including, cell H300

      COLUMN I

      Put the heading, APV1, into cell I1 and then input the formula, =SUM(G2:G300)/LOOKUP(A2,D2:D300,E2:E300) into cell I2

      COLUMN J

      Put the heading, APV2, into cell J1 and then input the formula, =SUM(H2:H300)/LOOKUP(A2,D2:D300,E2:E300) into cell J2

      COLUMN K

      Put the heading, APV3, into cell K1 and then input the formula, =1-(1-C2)*J2 into cell K2

      COLUMN L

      Put the heading, APV4, into cell L1 and then input the formula, =K2-C2^B2*LOOKUP(A2+B2,D2:D300,E2:E300)/LOOKUP(A2,D2:D300,E2:E300) into cell L2

      COLUMN M

      Put the heading, APV5, into cell M1 and then input the formula, =K2-L2 into cell M2

      You now have a spreadsheet to calculate APVs based on inputted values for x, n and v and an inputted life table!

      An additional note (optional read)

      To utilise your spreadsheet to calculate and display the five APVs for x=z,z+1,…h, follow the four steps below;

      STEP 1

      Input the values for n and v, as well as the life table, but don’t put in a value for x

      STEP 2

      Now put the value for z into cell A3, then input the formula, =A3+1 into cell A4 and then copy this formula down to, and including, the column A cell in which the value for h appears, noting this row number.

      STEP 3

      Now enter the following Visual Basic code into Excel’s Visual Basic facility, replacing R with the row number of the row in which the value for h appeared;

      Sub Macro1()

      ‘ Macro1 Macro

      For i = 3 To R

      Range(“A” & i).Select

      Selection.Copy

      Range(“A2”).Select

      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

      :=False, Transpose:=False

      Range(“I2:M2”).Select

      Application.CutCopyMode = False

      Selection.Copy

      Range(“I” & i & “:M” & i).Select

      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

      :=False, Transpose:=False

      Next i

      End Sub

      STEP 4

      Now run this Visual Basic code.

      The column I, J, K, L and M cells in row 3 down to, and including, the row in which the value for h appeared, now show the five APVs for x=z,z+1,…h

      NOTE:  If you now want the spreadsheet to calculate and display the five APVs, using the same life table and the same values for z and h as before, but using a different value(s) for n and/or v, then simply; delete what’s in the column I, J, K, L and M cells in row 3 downwards, then delete what’s in cell A2, then make your change(s) to the value(s) for n and/or v and then run the existing Visual Basic code again.

      There you have it!

       

      Richard Purvey March 2024

       

       

       

       

       

       

       

    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.