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

AuthorPosts

November 10, 2023 at 11:20 am #24621
Actuarial Contingencies: A simple way to get an Excel spreadsheet to calculate 1/m type APVs based on a survival function
Here is a simple way to get an Excel spreadsheet to calculate five APVs (the first four being 1/m type APVs) based on a survival function, S. The five APVs are;
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
Method;
COLUMN A
Put the heading, x, into cell A1
COLUMN B
Put the heading, n, into cell B1
COLUMN C
Put the heading, m, into cell C1
COLUMN D
Put the heading, v, into cell D1
COLUMN E
Put the heading, r, into cell E1, and put the value, 0, into cell E2 and input the formula, =E2+1 into cell E3 and copy this formula down to, and including, cell Emn+2
For example, if m=4 and n=10, cell Emn+2 would then be cell E42
COLUMN F
Put the heading, r/m, into cell F1, and input the formula, =E2/$C$2 into cell F2 and copy this formula down to, and including, cell Fmn+2
COLUMN G
Put the heading, v^(r/m), into cell G1, and input the formula, =$D$2^F2 into cell G2 and copy this formula down to, and including, cell Gmn+2
COLUMN H
Put the heading, x+r/m, into cell H1, and input the formula, =$A$2+F2 into cell H2 and copy this formula down to, and including, cell Hmn+2
COLUMN I
Put the heading, S(x+r/m), into cell I1, and input the formula, =S(H2), for example =EXP(0.00022*H22.7*10^(6)*(1.124^H21)/ln(1.124)), into cell I2 and copy this formula down to, and including, cell Imn+2
COLUMN J
Put the heading, v^(r/m)S(x+r/m), into cell J1, and input the formula, =G2*I2 into cell J2 and copy this formula down to, and including, cell Jmn+2
COLUMN K
Put the heading, APV1, into cell K1, and input the formula, =SUM(J3:Jmn+2)/(C2*I2) into cell K2
COLUMN L
Put the heading, APV2, into cell L1, and input the formula, =SUM(J2:Jmn+1)/(C2*I2) into cell L2
COLUMN M
Put the heading, APV3, into cell M1, and input the formula, =1C2*(1D2^(1/C2))*L2 into cell M2
COLUMN N
Put the heading, APV4, into cell N1, and input the formula, =M2J(mn+2)/I2 into cell N2
COLUMN O
Put the heading, APV5, into cell O1, and input the formula, =M2N2 into cell O2
x, n, m and v are, of course, input by the user into cells A2, B2, C2 and D2 respectively.
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/m,…h), on the same n, m and v basis in the case of each x, simply;
First
Put the value, z, into cell A3 and input the formula, =A3+1/$C$2 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: K2, L2, M2, N2 and O2 and then paste these VALUES to cells: K3, L3, M3, N3 and O3 respectively and then
Copy from cell A4 and then paste this VALUE to cell A2 and then copy from cells: K2, L2, M2, N2 and O2 and then paste these VALUES to cells: K4, L4, M4, N4 and O4 respectively and then
Copy from cell A5 and then paste this VALUE to cell A2 and then copy from cells: K2, L2, M2, N2 and O2 and then paste these VALUES to cells: K5, L5, M5, N5 and O5 respectively and then
Copy from cell A6 and then paste this VALUE to cell A2 and then copy from cells: K2, L2, M2, N2 and O2 and then paste these VALUES to cells: K6, L6, M6, N6 and O6 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: K2, L2, M2, N2 and O2 and then pasting these VALUES to the column K, L, M, N and O cells, in the row in which the value, h, appeared, respectively.
Finally
Run this Visual Basic code.
The column K, L, M, N and O cells in row 3 downwards now show the five APVs for x=z,z+1/m,…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!

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