 This topic has 0 replies, 1 voice, and was last updated 1 month, 3 weeks ago by Richard Purvey.

AuthorPosts

February 26, 2024 at 6:11 pm #24911
Actuarial Life and Death: How to write a spreadsheet to calculate 1/m type APVs based on an inputted survival function
The sheet does not use any commutation functions, recursion formulae or approximations, it calculates exactly.
It is a fourteen column sheet (columns A to N), with the first four columns (columns A to D) accommodating the input of the values for x, m, n and v respectively, column G accommodating the input of the survival function, and the last five columns (columns J to N) displaying the five calculated 1/m type APVs, namely;
APV1
APV of an nyear temporary immediate life annuity, with a discount factor of v, of 1 per year payable m times per year for (x)
APV2
APV of an nyear temporary life annuity due, with a discount factor of v, of 1 per year payable m times per 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 1/m 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 1/m 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 D
Put the headings x, m, n and v into cells A1, B1, C1 and D1 respectively, and then input the values for x, m, n and v into cells A2, B2, C2 and D2 respectively.
m=1, 2, 3, 4, 6 or 12.
COLUMN E
Put the heading, r, into cell E1, then put the value, 0, into cell E2, then input the formulae, =E2+1 into cell E3 and then copy this formula down to, and including, cell E2000
COLUMN F
Put the heading, r/m, into cell F1, then input the formula, =E2/$B$2 into cell F2 and then copy this formula down to, and including, cell F2000
COLUMN G
Put the heading, S(x+r/m), into cell G1, then input your specific version of the general formula, =S($A$2+F2), for example, =EXP(0.00022*($A$2+F2)2.7*10^(6)*(1.124^($A$2+F2)1)/LN(1.124)), into cell G2 and then copy this formula down to, and including, cell G2000
REMEMBER TO ALWAYS COPY YOUR FORMULA, FROM CELL G2, DOWN TO, AND INCLUDING, CELL G2000 EACH TIME YOU CHANGE IT.
COLUMN H
Put the heading, v^(r/m)S(x+r/m) list for APV1 calc, into cell H1, then input the formula, =IF(AND(F2>0,F2<$C$2+1/$B$2),$D$2^F2*G2,0) into cell H2 and then copy this formula down to, and including, cell H2000
COLUMN I
Put the heading, v^(r/m)S(x+r/m) list for APV2 calc, into cell I1, then input the formula, =IF(F2<$C$2, $D$2^F2*G2,0) into cell I2 and then copy this formula down to, and including, cell I2000
COLUMN J
Put the heading, APV1, into cell J1 and then input the formula, =SUM(H2:H2000)/(B2*G2) into cell J2
COLUMN K
Put the heading, APV2, into cell K1 and then input the formula, =SUM(I2:I2000)/(B2*G2) into cell K2
COLUMN L
Put the heading, APV3, into cell L1 and then input the formula, =1B2*(1D2^(1/B2))*K2 into cell L2
COLUMN M
Put the heading, APV4, into cell M1 and then input the formula, =L2D2^C2*LOOKUP(C2,F2:F2000,G2:G2000)/G2 into cell M2
COLUMN N
Put the heading, APV5, into cell N1 and then input the formula, =L2M2 into cell N2
You now have a spreadsheet to calculate 1/m type APVs based on inputted values for x, m, n and v and an inputted survival function!
An additional note (optional read)
To utilise your spreadsheet to calculate and display the five 1/m type APVs for x=z,z+1/m,…h, follow the four steps below;
STEP 1
Input the values for m, n and v, as well as the survival function, 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/$B$2 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(“J2:N2”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“J” & i & “:N” & 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 J, K, L, M and N cells in row 3 down to, and including, the row in which the value for h appeared, now show the five 1/m type APVs for x=z,z+1/m,…h
NOTE: If you now want the spreadsheet to calculate and display the five 1/m type APVs, using the same survival function and the same values for z and h as before, but using a different value for m, then follow the five steps below;
STEP 1
First, delete what’s in the column J, K, L, M and N cells in row 3 downwards.
STEP 2
Next, delete what’s in cell A2 and then make your change to the value for m, along with any change(s) you want to make to the value(s) for n and/or v
STEP 3
If you have increased the value for m, then extend the copying of the formula in cell A4 down to, and including, the new column A cell in which the value for h appears, noting this new row number.
If you have decreased the value for m, then delete what’s in the column A cells below the new column A cell in which the value for h appears, noting this new row number.
STEP 4
Go into your existing Visual Basic code in Excel’s Visual Basic facility and replace the existing value for R with the new row number of the new row in which the value for h appeared.
STEP 5
Now, run this new Visual Basic code.
If, however, you just want the spreadsheet to calculate and display the five 1/m type APVs, using the same survival function, the same values for z and h AND the same value for m as before, but using a different value(s) for n and/or v, then simply; delete what’s in the column J, K, L, M and N 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 February 2024

AuthorPosts
 You must be logged in to reply to this topic.