 This topic has 0 replies, 1 voice, and was last updated 6 months ago by Richard Purvey.

AuthorPosts

March 18, 2024 at 2:58 pm #24967
Actuarial Life and Death: How to write a spreadsheet to calculate joint life status xy APVs based on two inputted life tables
The sheet does not use any commutation functions, recursion formulae or approximations, it calculates exactly.
It is a twenty column sheet (columns A to T), with the first four columns (columns A to D) accommodating the input of the values for x, y, n and v respectively, columns E, F, G and H accommodating the input of the two life tables, and the last five columns (columns P to T) displaying the five calculated joint life status xy APVs, namely;
APV1
APV of an nyear temporary joint life status xy immediate life annuity, with a discount factor of v, of 1 per year payable once a year
APV2
APV of an nyear temporary joint life status xy life annuity due, with a discount factor of v, of 1 per year payable once a year
APV3
APV of an nyear temporary joint life status xy endowment insurance, with a discount factor of v, of 1, where the death benefit is payable at the end of the year of death
APV4
APV of an nyear temporary joint life status xy death benefit, with a discount factor of v, of 1 payable at the end of the year of death
APV5
APV of an nyear temporary joint life status xy pure endowment, with a discount factor of v, of 1
HOW TO WRITE THE SHEET
COLUMNS A TO D
Put the headings x, y, n and v into cells A1, B1, C1 and D1 respectively, and then input the values for x, y, n and v into cells A2, B2, C2 and D2 respectively.
COLUMN E
Put the heading, x values, into cell E1, and then input the x values, in ascending order, down column E, starting by inputting the lowest x value into cell E2
COLUMN F
Put the heading, lx values, into cell F1, and then input the lx values, in descending order, down column F, starting by inputting the highest lx value into cell F2
COLUMN G
Put the heading, y values, into cell G1, and then input the y values, in ascending order, down column G, starting by inputting the lowest y value into cell G2
COLUMN H
Put the heading, ly values, into cell H1, and then input the ly values, in descending order, down column H, starting by inputting the highest ly value into cell H2
COLUMN I
Put the heading, r, into cell I1, then put the value, 0, into cell I2, then input the formulae, =I2+1 into cell I3 and then copy this formula down to, and including, cell I300
COLUMN J
Put the heading, v^rl(x+r) list for APV1 calc, into cell J1, then input the formula, =IF(AND(I2>0,I2<$C$2+1),$D$2^I2*LOOKUP($A$2+I2,$E$2:$E$300,$F$2:$F$300),0) into cell J2 and then copy this formula down to, and including, cell J300
COLUMN K
Put the heading, l(y+r) list for APV1 calc, into cell K1, then input the formula, =IF(AND(I2>0,I2<$C$2+1),LOOKUP($B$2+I2,$G$2:$G$300,$H$2:$H$300),0) into cell K2 and then copy this formula down to, and including, cell K300
COLUMN L
Put the heading, v^rl(x+r)l(y+r) list for APV1 calc, into cell L1, then input the formula, =J2*K2 into cell L2 and then copy this formula down to, and including, cell L300
COLUMN M
Put the heading, v^rl(x+r) list for APV2 calc, into cell M1, then input the formula, =IF(I2<$C$2,$D$2^I2*LOOKUP($A$2+I2,$E$2:$E$300,$F$2:$F$300),0) into cell M2 and then copy this formula down to, and including, cell M300
COLUMN N
Put the heading, l(y+r) list for APV2 calc, into cell N1, then input the formula, =IF(I2<$C$2,LOOKUP($B$2+I2,$G$2:$G$300,$H$2:$H$300),0) into cell N2 and then copy this formula down to, and including, cell N300
COLUMN O
Put the heading, v^rl(x+r)l(y+r) list for APV calc, into cell O1, then input the formula, =M2*N2 into cell O2 and then copy this formula down to, and including, cell O300
COLUMN P
Put the heading, APV1, into cell P1 and then input the formula, =SUM(L2:L300)/(LOOKUP(A2,E2:E300,F2:F300)*LOOKUP(B2,G2:G300,H2:H300)) into cell P2
COLUMN Q
Put the heading, APV2, into cell Q1 and then input the formula, =SUM(O2:O300)/(LOOKUP(A2,E2:E300,F2:F300)*LOOKUP(B2,G2:G300,H2:H300)) into cell Q2
COLUMN R
Put the heading, APV3, into cell R1 and then input the formula, =1(1D2)*Q2 into cell R2
COLUMN S
Put the heading, APV4, into cell S1 and then input the formula, =R2LOOKUP(2,1/(L:L<>0),L:L)/(LOOKUP(A2,E2:E300,F2:F300)*LOOKUP(B2,G2:G300,H2:H300)) into cell S2
COLUMN T
Put the heading, APV5, into cell T1 and then input the formula, =R2S2 into cell T2
You now have a spreadsheet to calculate joint life status xy APVs based on inputted values for x, y, n and v and two inputted life tables!
An additional note (optional read)
To utilise your spreadsheet to calculate and display the five joint life status xy APVs for x=z,z+1,…h, with y being related to x through a formula such as y=x or y=x+10, etc, follow the four steps below;
STEP 1
Input whatever formula is needed into cell B2 (for example, =A2 or =A2+10, etc) along with the values for n and v, as well as the two life tables, 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(“P2:T2”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“P” & i & “:T” & 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 P, Q, R, S and T cells in row 3 down to, and including, the row in which the value for h appeared, now show the five joint life status xy APVs for x=z,z+1,…h
NOTE: If you now want the spreadsheet to calculate and display the five joint life status xy APVs, using the same life tables and the same values for z and h as before, but using a different formula in cell B2 and/or a different value(s) for n and/or v, then simply; delete what’s in the column P, Q, R, S and T cells in row 3 downwards, then delete what’s in cell A2, then make your change to the formula in cell B2 and/or 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

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