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

AuthorPosts

October 9, 2023 at 2:11 pm #24511
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.
Richard Purvey October 2023.

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