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

AuthorPosts

October 4, 2023 at 3:38 pm #24503
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.
Richard Purvey October 2023.

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