Home Forums Actuarial Resources & Blogs Easy APVs from an imputed “y, l(y)” table using Excel

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #24597
    Richard Purvey
    Participant

      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 n-year temporary immediate life annuity, with a discount factor of v, of 1 per year payable once a year for (x)

      APV2

      APV of an n-year 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$2-1), 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-(1-C2)*P2 into cell Q2

      COLUMN R

      Put the heading, APV4, into cell R1, and input the formula, =Q2-SUM(N2:Nk)/SUM(M2:Mk) into cell R2

      COLUMN S

      Put the heading, APV5, into cell S1, and input the formula, =Q2-R2 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 n-year 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), at once, 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.

      Bringing life contingencies into the 21st century, NO commutation functions, NO recursion formula, NO approximations.

       

       

       

       

       

       

       

       

    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.