IRR Calculations For Insurance Illustration

[preciseau]

Hi guys, I am trying to mimic the calculations I saw in a video about life insurance illustration calculations.

The guy running the video was using a suite of calculators from Truth Concepts Software

It seems like a highly overpriced piece of software that can easily be replicated by Excel - so I have attempted to do that!

I was able to mimic year one, but after that, I am not able to get the IRR and ROR numbers to match the screenshot below.

The calculations I used for Year 1 Total IRR on Net Cash Value was: (EOY Net Cash Value/Annual Premium)-1 (formatted to show percentage)

For Year One Total IRR on Net DB I used: (EOY Net Death Benefit/Annual Premium)/Annual Premium

Any help would be greatly appreciated.

Here is my spreadsheet:

 

[Legacy 256096]

This is a straight forward IRR calculation, you are making an error when accumulating the premium

For example, IRR for CV for second year would include the premium payments for first two years and the CV for 2nd year
Similarly IRR for DB for second year would include the premium payments for first two years and the DB for 2nd year

Do remember, for Excel IRR you would need to denote the premiums as negative cash flows

As as example

A1: -16500 B1: 23291
A2: -16500

=IRR( (A1:A2,B1)
= -21.10%


A1: -16500 C1: 386103
A2: -16500
=IRR( (A1:A2,C1)
336.31%

 

[preciseau]

Thank you, here is what I have now:

I used the formulas you outlined above along with switching the premiums to be negative.

What would I use to calculate the "Annual ROR on Net CV" ? I tried the XIRR formula, but it did not work.

Thanks

 

[preciseau]

Figured it out, here is the formula I used to figure out the E3 value:

=SUM(B3/(B2-A3))-1

The Annual ROR on Net CV is basically how much you earn vs. how much you are paying in.

 

[woodawg]

I ran a similar calculation today for a whole life illustration that pays $35,000 per year for the first 7 years, with no premium payments thereafter. My IRR calculations on the Net Cash Value are matching the illustration through year 26. Then for years 26-37, I receive a #NUM! error and for years 36-121, I receive a #DIV/0! error. I increased by maximum iterations to 1,000 for the calculations and receive the same errors. Any thoughts on how to remedy this?

 

[Legacy 256096]

I ran a similar calculation today for a whole life illustration that pays $35,000 per year for the first 7 years, with no premium payments thereafter. My IRR calculations on the Net Cash Value are matching the illustration through year 26. Then for years 26-37, I receive a #NUM! error and for years 36-121, I receive a #DIV/0! error. I increased by maximum iterations to 1,000 for the calculations and receive the same errors. Any thoughts on how to remedy this?

Excel IRR uses a rather trivial algorithm to find IRR - there are third party Excel functions that in most cases find an IRR without making use of a guess rate

In any case finding IRR is not possible with a formula when number of cash flows exceed 5, thus iterative methods are employed in finding an IRR

With these iterative methods a seed value is used to start the iterative calculations and usually 10% is the default guess. If the iterative calculations converge within a number of iterations (20 in Excel) the rate is assumed to have been found

But if the successive calculations diverge and a solution is not found within 20 iterations, one would have to enter a seed value or a guess rate other than 10%

But here is the Catch 22, you have no way of knowing this guess or do you. If you did then you wouldn't have bothered to use Excel IRR in the first case

I have some code of my own that I am testing, it calculates the GUESS RATE from given cash flows and in most cases this GUESS RATE when fed to the iterative calculations produces the IRR

Would you mind posting your cash flows so I can see if my code works, if it does I will provide you with a link to the IRR code

 

[woodawg]

Thanks for the quick response. I did figure out the IRR% based on guestimates, but it sounds like you are on to an interesting/valuable idea. This is a policy that has been inforce for 7 years and I'm doing an analysis of the value of overfunding it. Please see below my series of cash flows going forward:

[TABLE="width: 211"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]8[/TD]
[TD="align: right"]-26289[/TD]
[TD="align: right"]104,609.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]-35052[/TD]
[TD="align: right"]144,282.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]-35052[/TD]
[TD="align: right"]184,989.00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]-35052[/TD]
[TD="align: right"]223,780.00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]-35052[/TD]
[TD="align: right"]265,193.00[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]-35052[/TD]
[TD="align: right"]309,292.00[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]-35052[/TD]
[TD="align: right"]353,128.00[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]365,497.00[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]379,067.00[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]390,830.00[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]403,737.00[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]417,792.00[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]429,962.00[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]443,251.00[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]458,007.00[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]473,105.00[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]488,638.00[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]504,711.00[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]521,345.00[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]540,116.00[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]559,511.00[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]579,584.00[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600,385.00[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]623,061.00[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]646,239.00[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]669,961.00[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]694,266.00[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]719,155.00[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]743,603.00[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]770,284.00[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]797,656.00[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]825,794.00[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]854,853.00[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]884,932.00[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]916,117.00[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]948,577.00[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]980,925.00[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,014,724.00[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,048,283.00[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,082,818.00[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,116,616.00[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,150,793.00[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,185,391.00[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,219,721.00[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,256,494.00[/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,291,749.00[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,329,212.00[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,367,999.00[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,405,470.00[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,445,296.00[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,482,933.00[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,522,533.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,567,901.00[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,614,367.00[/TD]
[/TR]
</tbody>[/TABLE]

Excel IRR uses a rather trivial algorithm to find IRR - there are third party Excel functions that in most cases find an IRR without making use of a guess rate

In any case finding IRR is not possible with a formula when number of cash flows exceed 5, thus iterative methods are employed in finding an IRR

With these iterative methods a seed value is used to start the iterative calculations and usually 10% is the default guess. If the iterative calculations converge within a number of iterations (20 in Excel) the rate is assumed to have been found

But if the successive calculations diverge and a solution is not found within 20 iterations, one would have to enter a seed value or a guess rate other than 10%

But here is the Catch 22, you have no way of knowing this guess or do you. If you did then you wouldn't have bothered to use Excel IRR in the first case

I have some code of my own that I am testing, it calculates the GUESS RATE from given cash flows and in most cases this GUESS RATE when fed to the iterative calculations produces the IRR

Would you mind posting your cash flows so I can see if my code works, if it does I will provide you with a link to the IRR code

 

[Legacy 256096]

Hello I get the following results from a user defined Excel IRR function that at first calculates a GUESS that is fed to the Newton Raphson method

8 -26289 104,609.00 297.919%
9 -35052 144,282.00 76.906%
10 -35052 184,989.00 38.274%
11 -35052 223,780.00 23.468%
12 -35052 265,193.00 16.552%
13 -35052 309,292.00 12.793%
14 -35052 353,128.00 10.325%
15 0 365,497.00 8.989%
16 0 379,067.00 8.120%
17 0 390,830.00 7.406%
18 0 403,737.00 6.894%
19 0 417,792.00 6.515%
20 0 429,962.00 6.154%
21 0 443,251.00 5.874%
22 0 458,007.00 5.662%
23 0 473,105.00 5.479%
24 0 488,638.00 5.322%
25 0 504,711.00 5.186%
26 0 521,345.00 5.067%
27 0 540,116.00 4.981%
28 0 559,511.00 4.903%
29 0 579,584.00 4.834%
30 0 600,385.00 4.772%
31 0 623,061.00 4.724%
32 0 646,239.00 4.678%
33 0 669,961.00 4.635%
34 0 694,266.00 4.592%
35 0 719,155.00 4.552%
36 0 743,603.00 4.508%
37 0 770,284.00 4.473%
38 0 797,656.00 4.441%
39 0 825,794.00 4.409%
40 0 854,853.00 4.379%
41 0 884,932.00 4.351%
42 0 916,117.00 4.326%
43 0 948,577.00 4.302%
44 0 980,925.00 4.275%
45 0 1,014,724.00 4.252%
46 0 1,048,283.00 4.225%
47 0 1,082,818.00 4.200%
48 0 1,116,616.00 4.172%
49 0 1,150,793.00 4.143%
50 0 1,185,391.00 4.114%
51 0 1,219,721.00 4.085%
52 0 1,256,494.00 4.059%
53 0 1,291,749.00 4.030%
54 0 1,329,212.00 4.004%
55 0 1,367,999.00 3.980%
56 0 1,405,470.00 3.953%
57 0 1,445,296.00 3.929%
58 0 1,482,933.00 3.901%
59 0 1,522,533.00 3.876%
60 0 1,567,901.00 3.858%
61 0 1,614,367.00 3.840%

 

[Legacy 256096]

The Excel IRR function that I have used is part of large library of financial functions that is coded in C programming language and is distributed as a .XLL file for Excel

However a limited version of this IRR function coded in Excel VBA is found here at it's authors sites Find IRR in Excel 2007|2010|2013

It is trimmed down version of the actual IRR function that is quite feature rich with many options