Easy 1/m type APVs from a survival function using Excel

[Richard Purvey]

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 n-year 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 n-year 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*H2-2.7*10^(-6)*(1.124^H2-1)/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, =1-C2*(1-D2^(1/C2))*L2 into cell M2

COLUMN N

Put the heading, APV4, into cell N1, and input the formula, =M2-J(mn+2)/I2 into cell N2

COLUMN O

Put the heading, APV5, into cell O1, and input the formula, =M2-N2 into cell O2

 

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

 

Richard Purvey October 2023.

 

 

 

 

[Author]

Posts