Calculations using spreadsheet

Moredun1 · February 27, 2024, 2:12pm

How to perform five age calculation types using a spreadsheet

Calculation of Age On Last Birthday

Put the appropriate headings into cells A1, B1 and C1 and then put the date of birth into cell A2, the relevant later date into cell B2, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)

Calculation of Age On Next Birthday

Put the appropriate headings into cells A1, B1 and C1 and then put the date of birth into cell A2, the relevant later date into cell B2, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)+1

Calculation of Age In Years and Whole Months

Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of birth into cell A2 and the relevant later date into cell B2. Into cell C2, put the formula =DATEDIF(A2,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2,B2,”YM”)

Calculation of Age In Years and Days

Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of birth into cell A2 and the relevant later date into cell B2. Into cell C2, put the formula =DATEDIF(A2,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2,B2,”YD”)

Calculation of Age In Years, Months and Days

Put the appropriate headings into cells A1, B1, C1, D1 and E1 and then put the date of birth into cell A2 and the relevant later date into cell B2. Into cell C2, put the formula =DATEDIF(A2,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2,B2,”YM”) and into cell E2, put the formula =DATEDIF(A2,B2,”MD”)

I remember having to do these age calculation types manually, and quite often many at a time(!), in the 1980s. It was mainly the first two types I had to do, sometimes the third type and, occasionally, the 4th type. Thank goodness for spreadsheets!

Richard Purvey February 2024

AlexioXela · February 27, 2024, 4:39pm

It is worth noting that per DATEDIF function - Microsoft Support this function is included in Excel to support older workbooks and has at least one known issue.

Moredun1 · February 27, 2024, 6:27pm

How to perform six service calculation types using a spreadsheet

Note;

Calculation of Normal Retirement Date

Put the appropriate headings into cells A1, B1 and C1 and then put the date of birth into cell A2 and the age in years to be added (the normal retirement age, e.g. 60) into cell B2, and into cell C2, put the formula =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))

Calculation of Potential Service In Whole Years

Put the appropriate headings into cells A1, B1 and C1 and then put the date of the first day of service into cell A2 and the Normal Retirement Date into cell B2, and into cell C2, put the formula =DATEDIF(A2-1,B2-1,”Y”)

Calculation of Potential Service In Years and Whole Months

Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the Normal Retirement Date into cell B2. Into cell C2, put the formula =DATEDIF(A2-1,B2-1,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,B2-1,”YM”)

Calculation of Potential Service In Years and Days

Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the Normal Retirement Date into cell B2. Into cell C2, put the formula =DATEDIF(A2-1,B2-1,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,B2-1,”YD”)

Calculation of Actual Service In Whole Years

Put the appropriate headings into cells A1, B1 and C1 and then put the date of the first day of service into cell A2 and the date of the last day of service into cell B2, and into cell C2, put the formula =DATEDIF(A2-1,B2,”Y”)

Calculation of Actual Service In Years and Whole Months

Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the date of the last day of service into cell B2. Into cell C2, put the formula =DATEDIF(A2-1,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,B2,”YM”)

Calculation of Actual Service In Years and Days

Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the date of the last day of service into cell B2. Into cell C2, put the formula =DATEDIF(A2-1,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,B2,”YD”)

I remember having to do these service calculation types manually, and quite often many at a time(!), in the 1980s. Thank goodness for spreadsheets!

Richard Purvey February 2024

Moredun1 · February 28, 2024, 7:56am

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 n-year 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 n-year 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.710^(-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, =1-B2*(1-D2^(1/B2))*K2 into cell L2

COLUMN M

Put the heading, APV4, into cell M1 and then input the formula, =L2-D2^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, =L2-M2 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

Moredun1 · March 7, 2024, 3:24pm

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

Moredun1 · March 12, 2024, 3:28pm

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 n-year 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 n-year 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 n-year 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 n-year 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 n-year 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 APV2 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-(1-D2)*Q2 into cell R2

COLUMN S

Put the heading, APV4, into cell S1 and then input the formula, =R2-LOOKUP(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, =R2-S2 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[quote=“Moredun1, post:7, topic:8926, full:true”]
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 n-year 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 n-year 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 n-year 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 n-year 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 n-year 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 APV2 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-(1-D2)*Q2 into cell R2

COLUMN S

Put the heading, APV4, into cell S1 and then input the formula, =R2-LOOKUP(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, =R2-S2 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
[/quote]

Actuarial Life and Death: How to write a spreadsheet to calculate 1/m type joint life status xy APVs based on two inputted survival functions

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 five columns (columns A to E) accommodating the input of the values for x, y, m, n and v respectively, columns H and I accommodating the input of the two survival functions, and the last five columns (columns P to T) displaying the five calculated 1/m type joint life status xy APVs, namely;

APV1

APV of an n-year temporary joint life status xy immediate life annuity, with a discount factor of v, of 1 per year payable m times per year

APV2

APV of an n-year temporary joint life status xy life annuity due, with a discount factor of v, of 1 per year payable m times per year

APV3

APV of an n-year 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 1/m year of death

APV4

APV of an n-year temporary joint life status xy death benefit, with a discount factor of v, of 1 payable at the end of the 1/m year of death

APV5

APV of an n-year temporary joint life status xy pure endowment, with a discount factor of v, of 1

HOW TO WRITE THE SHEET

COLUMNS A TO E

Put the headings x, y, m, n and v into cells A1, B1, C1, D1 and E1 respectively, and then input the values for x, y, m, n and v into cells A2, B2, C2, D2 and E2 respectively.

m=1, 2, 3, 4, 6 or 12.

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 F2000

COLUMN G

Put the heading, r/m, into cell G1, then input the formula, =F2/$C$2 into cell G2 and then copy this formula down to, and including, cell G2000

COLUMN H

Put the heading, S(x+r/m), into cell H1, then input your specific version of the general formula, =S($A$2+G2), for example, =EXP(-0.00022*($A$2+G2)-2.710^(-6)(1.124^($A$2+G2)-1)/LN(1.124)), into cell H2 and then copy this formula down to, and including, cell H2000

REMEMBER TO ALWAYS COPY YOUR FORMULA, FROM CELL H2, DOWN TO, AND INCLUDING, CELL H2000 EACH TIME YOU CHANGE IT.

COLUMN I

Put the heading, S(y+r/m), into cell I1, then input your specific version of the general formula, =S($B$2+G2), for example, =EXP(-0.00022*($B$2+G2)-2.710^(-6)(1.124^($B$2+G2)-1)/LN(1.124)), into cell I2 and then copy this formula down to, and including, cell I2000

REMEMBER TO ALWAYS COPY YOUR FORMULA, FROM CELL I2, DOWN TO, AND INCLUDING, CELL I2000 EACH TIME YOU CHANGE IT.

COLUMN J

Put the heading, v^(r/m)S(x+r/m) list for APV1 calc, into cell J1, then input the formula, =IF(AND(G2>0,G2<$D$2+1/$C$2),$E$2^G2*H2,0) into cell J2 and then copy this formula down to, and including, cell J2000

COLUMN K

Put the heading, S(y+r/m) list for APV1 calc, into cell K1, then input the formula, =IF(AND(G2>0,G2<$D$2+1/$C$2),I2,0) into cell K2 and then copy this formula down to, and including, cell K2000

COLUMN L

Put the heading, v^(r/m)S(x+r/m)S(y+r/m) 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 L2000

COLUMN M

Put the heading, v^(r/m)S(x+r/m) list for APV2 calc, into cell M1, then input the formula, =IF(G2<$D$2,$E$2^G2*H2,0) into cell M2 and then copy this formula down to, and including, cell M2000

COLUMN N

Put the heading, S(y+r/m) list for APV2 calc, into cell N1, then input the formula, =IF(G2<$D$2,I2,0) into cell N2 and then copy this formula down to, and including, cell N2000

COLUMN O

Put the heading, v^(r/m)S(x+r/m)S(y+r/m) list for APV2 calc, into cell O1, then input the formula, =M2*N2 into cell O2 and then copy this formula down to, and including, cell O2000

COLUMN P

Put the heading, APV1, into cell P1 and then input the formula, =SUM(L2:L2000)/(C2H2I2) into cell P2

COLUMN Q

Put the heading, APV2, into cell Q1 and then input the formula, =SUM(O2:O2000)/(C2H2I2) into cell Q2

COLUMN R

Put the heading, APV3, into cell R1 and then input the formula, =1-C2*(1-E2^(1/C2))*Q2 into cell R2

COLUMN S

Put the heading, APV4, into cell S1 and then input the formula, =R2-LOOKUP(2,1/(L:L<>0),L:L)/(H2*I2) into cell S2

COLUMN T

Put the heading, APV5, into cell T1 and then input the formula, =R2-S2 into cell T2

You now have a spreadsheet to calculate 1/m type joint life status xy APVs based on inputted values for x, y, m, n and v and two inputted survival functions!

An additional note (optional read)

To utilise your spreadsheet to calculate and display the five 1/m type joint life status xy APVs for x=z,z+1/m,…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 m, n and v, as well as the two survival functions, 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/$C$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(“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 1/m type joint life status xy APVs for x=z,z+1/m,…h

NOTE: If you now want the spreadsheet to calculate and display the five 1/m type joint life status xy APVs, using the same survival functions 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 P, Q, R, S and T 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 formula in cell B2 and/or 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 joint life status xy APVs, using the same survival functions, the same values for z and h AND the same value for m 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