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

AuthorPosts

November 10, 2023 at 10:30 am #24620
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 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, 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*F22.7*10^(6)*(1.124^F21)/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(1C2)*J2 into cell K2
COLUMN L
Put the heading, APV4, into cell L1, and input the formula, =K2H(n+2)/G2 into cell L2
COLUMN M
Put the heading, APV5, into cell M1, and input the formula, =K2L2 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 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: 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!

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