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