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

AuthorPosts

November 10, 2023 at 10:19 am #24618
Actuarial Contingencies: A simple way to get an Excel spreadsheet to calculate APVs based on an inputted “y, l(y)” table
Here is a simple way to get an Excel spreadsheet to calculate five APVs based on an inputted “y, l(y)” table. The five APVs are;
APV1
APV of an nyear temporary immediate life annuity, with a discount factor of v, of 1 per year payable once a year for (x)
APV2
APV of an nyear 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, y, into cell D1, and input the y values into cells D2 down to, and including, cell Dk
For example, say your table had the following y values: 80, 81, 82, 83 and 84. Cell Dk would then be cell D6 i.e. k would equal 6.
COLUMN E
Put the heading, l(y), into cell E1, and input the l(y) values into cells E2 down to, and including, cell Ek
COLUMN F
Put the heading, r, into cell F1, and input the formula, =IF(AND(D2>=$A$2, D2<=$A$2+$B$2), D2$A$2, “”) into cell F2 and copy this formula down to, and including, cell Fk
COLUMN G
Put the heading, v^r, into cell G1, and input the formula, =IF(F2<>””, $C$2^F2, “”) into cell G2 and copy this formula down to, and including, cell Gk
COLUMN H
Put the heading, x+r, into cell H1, and input the formula, =IF(F2<>””, D2, “”) into cell H2 and copy this formula down to, and including, cell Hk
COLUMN I
Put the heading, l(x+r), into cell I1, and input the formula, =IF(F2<>””, E2, “”) into cell I2 and copy this formula down to, and including, cell Ik
COLUMN J
Put the heading, v^rl(x+r), into cell J1, and input the formula, =IF(F2<>””, G2*I2, “”) into cell J2 and copy this formula down to, and including, cell Jk
COLUMN K
Put the heading, v^rl(x+r) for APV1 calc, into cell K1, and input the formula, =IF(AND(D2>=$A$2+1, D2<=$A$2+$B$2), J2,””) into cell K2 and copy this formula down to, and including, cell Kk
COLUMN L
Put the heading, v^rl(x+r) for APV2 calc, into cell L1, and input the formula, =IF(AND(D2>=$A$2, D2<=$A$2+$B$21), J2,””) into cell L2 and copy this formula down to, and including, cell Lk
COLUMN M
Put the heading, l(x), into cell M1, and input the formula, =IF(D2=$A$2, E2, “”) into cell M2 and copy this formula down to, and including, cell Mk
COLUMN N
Put the heading, v^nl(x+n), into cell N1, and input the formula, =IF(D2=$A$2+$B$2, J2, “”) into cell N2 and copy this formula down to, and including, cell Nk
COLUMN O
Put the heading, APV1, into cell O1, and input the formula, =SUM(K2:Kk)/SUM(M2:Mk) into cell O2
COLUMN P
Put the heading, APV2, into cell P1, and input the formula, =SUM(L2:Lk)/SUM(M2:Mk) into cell P2
COLUMN Q
Put the heading, APV3, into cell Q1, and input the formula, =1(1C2)*P2 into cell Q2
COLUMN R
Put the heading, APV4, into cell R1, and input the formula, =Q2SUM(N2:Nk)/SUM(M2:Mk) into cell R2
COLUMN S
Put the heading, APV5, into cell S1, and input the formula, =Q2R2 into cell S2
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 nyear 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: O2, P2, Q2, R2 and S2 and then paste these VALUES to cells: O3, P3, Q3, R3 and S3 respectively and then
Copy from cell A4 and then paste this VALUE to cell A2 and then copy from cells: O2, P2, Q2, R2 and S2 and then paste these VALUES to cells: O4, P4, Q4, R4 and S4 respectively and then
Copy from cell A5 and then paste this VALUE to cell A2 and then copy from cells: O2, P2, Q2, R2 and S2 and then paste these VALUES to cells: O5, P5, Q5, R5 and S5 respectively and then
Copy from cell A6 and then paste this VALUE to cell A2 and then copy from cells: O2, P2, Q2, R2 and S2 and then paste these VALUES to cells: O6, P6, Q6, R6 and S6 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: O2, P2, Q2, R2 and S2 and then pasting these VALUES to the column O, P, Q, R and S cells, in the row in which the value, h, appeared, respectively.
Finally
Run this Visual Basic code.
The column O, P, Q, R and S 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!

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