Home › Forums › Actuarial Resources & Blogs › Easy 1/m type APVs from a survival function using Excel
 This topic has 0 replies, 1 voice, and was last updated 3 weeks, 4 days ago by Richard Purvey.

AuthorPosts

November 6, 2023 at 4:45 pm #24599
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), at once, 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.
Bringing life contingencies into the 31st century, NO commutation functions, NO recursion formula, NO approximations

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