Easy APVs from a survival function using Excel

[Richard Purvey]

Actuarial Contingencies: A simple way to get an Excel spreadsheet to calculate APVs based on a survival function

Here is a simple way to get an Excel spreadsheet to calculate five APVs based on a survival function, S. The five APVs are;

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

Method;

COLUMN A

Put the heading, x, into cell A1

COLUMN B

Put the heading, n, into cell B1

COLUMN C

Put the heading, v, into cell C1

COLUMN D

Put the heading, r, into cell D1, and put the value, 0, into cell D2 and input the formula, =D2+1 into cell D3 and copy this formula down to, and including, cell Dn+2

For example, if n=10, cell Dn+2 would then be cell D12

COLUMN E

Put the heading, v^r, into cell E1, and input the formula, =$C$2^D2 into cell E2 and copy this formula down to, and including, cell En+2

COLUMN F

Put the heading, x+r, into cell F1, and input the formula, =$A$2+D2 into cell F2 and copy this formula down to, and including, cell Fn+2

COLUMN G

Put the heading, S(x+r), into cell G1, and input the formula, =S(F2), for example =EXP(-0.00022*F2-2.7*10^(-6)*(1.124^F2-1)/ln(1.124)), into cell G2 and copy this formula down to, and including, cell Gn+2

COLUMN H

Put the heading, v^rS(x+r), into cell H1, and input the formula, =E2*G2 into cell H2 and copy this formula down to, and including, cell Hn+2

COLUMN I

Put the heading, APV1, into cell I1, and input the formula, =SUM(H3:Hn+2)/G2 into cell I2

COLUMN J

Put the heading, APV2, into cell J1, and input the formula, =SUM(H2:Hn+1)/G2 into cell J2

COLUMN K

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

COLUMN L

Put the heading, APV4, into cell L1, and input the formula, =K2-H(n+2)/G2 into cell L2

COLUMN M

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

x, n and v are, of course, input by the user into cells A2, B2 and C2 respectively.

APV1 can, of course, be utilised for the answer to an n-year temporary curtate life expectancy for (x) if v is made equal to 1.

An additional note (optional read)

To utilise the Excel spreadsheet to calculate the five APVs for a list of values for x (x=z,z+1,…h), on the same n and v basis in the case of each x, simply;

First

Put the value, z, into cell A3 and input the formula, =A3+1 into cell A4 and copy this formula down to, and including, the column A cell in which the value, h, appears.

Next

Write a loop in Visual Basic which will;

Copy from cell A3 and then paste this VALUE to cell A2 and then copy from cells: I2, J2, K2, L2 and M2 and then paste these VALUES to cells: I3, J3, K3, L3 and M3 respectively and then

Copy from cell A4 and then paste this VALUE to cell A2 and then copy from cells: I2, J2, K2, L2 and M2 and then paste these VALUES to cells: I4, J4, K4, L4 and M4 respectively and then

Copy from cell A5 and then paste this VALUE to cell A2 and then copy from cells: I2, J2, K2, L2 and M2 and then paste these VALUES to cells: I5, J5, K5, L5 and M5 respectively and then

Copy from cell A6 and then paste this VALUE to cell A2 and then copy from cells: I2, J2, K2, L2 and M2 and then paste these VALUES to cells: I6, J6, K6, L6 and M6 respectively and then

Repeat this process until finally copying from the column A cell in which the value, h, appeared and then pasting this VALUE to cell A2 and then copying from cells: I2, J2, K2, L2 and M2 and then pasting these VALUES to the column I, J, K, L and M cells, in the row in which the value, h, appeared, respectively.

Finally

Run this Visual Basic code.

The column I, J, K, L and M cells in row 3 downwards now show the five APVs for x=z,z+1,…h respectively.

Richard Purvey November 2023.

No commutation functions, NO recursion formulae and NO approximate methods. Bringing actuarial life contingencies, APV calculations, into the 21st century!

[Author]

Posts