Internal Rate of Return calculations

[CathyMauro]

Hi All:

I've been using Excel's IRR function to calculate the Internal Rate of Return on an insurance project. The simple inputs are policy year (1, 2, 3, etc.), annual premiums (shown as a negative), and the cash value for any given year.

I've then overlaid a macro that basically looks up the results for each sequentual year and pastes that value in another column to illustrate how the IRR changes over the years (using an if formula to only show the current year's cash value).

From all the research I've done on this, I've come to accept that the IRR calculator in Excel is very tempermental and doesn't always produce correct results. When I use on-line calculators with the same inputs I receive a correct answer, but Excel shows the dreaded "#NUM " result. Yes, I'm aware that I can re-set the iterations options, and that I can add a "guess" to the IRR function, but I'm still getting #NUM results.

Any suggestions on how I can overcome this issue! I've been working on it for days and am at my wit's end.

[TABLE="width: 525"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]First Year Analysis:[/TD]
[TD]Policy Year[/TD]
[TD]Premiums[/TD]
[TD]Cash Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]-$4,419.00[/TD]
[TD]$20.00[/TD]
[TD="align: right"]-99.5%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Second Year Analysis:[/TD]
[TD]Policy Year[/TD]
[TD]Premiums[/TD]
[TD]Cash Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]-$4,419.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]-$4,419.00[/TD]
[TD]$41.00[/TD]
[TD="align: center"]#NUM ![/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Third Year Analysis:[/TD]
[TD]Policy Year[/TD]
[TD]Premiums[/TD]
[TD]Cash Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]-$4,419.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]-$4,419.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]-$4,419.00[/TD]
[TD]$62.00[/TD]
[TD="align: right"]-99%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Forth Year Analysis:[/TD]
[TD]Policy Year[/TD]
[TD]Premiums[/TD]
[TD]Cash Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]-$4,419.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]-$4,419.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]-$4,419.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]-$4,419.00[/TD]
[TD]$83.00[/TD]
[TD="align: center"]#NUM ![/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

I tend to agree that the Excel implementation of the IRR algorithm is "tempermental and doesn't always produce correct results" (and XIRR even more so). But it is not just the Excel implementation that is at fault. In part, it is the iterative method that we must use to calculate the IRR mathematically. My own IRR implementation, which I consider to be better and more accurate, returns #NUM for the first example (!) without a "guess" parameter.

So IMHO, there is no way to avoid providing a "guess" parameter, at least in extreme cases. And there is no way to guess a good "guess" parameter.

In order to determine a good guess, I manually construct a table of NPV values for varying discount rates -99%, -90%, -80%,..., 10%, 20%,..., 100%. I find "the" point of inflection, where -NPV become +NPV or vice versa. My guess is the middle interpolated discount rate. (But remember: mathematically, there might be more than one IRR; and even no IRR.) In extreme case, I might have to go as low as 99.9999% (or even closer to but not including 100%). Or I might have to extend the table to 200%, 1000%, 10000%, whatever. And I might need to "drill down" by increasing the precision of adjacent discount rates; for example, -80%, -79.9%,..., -79.1%, -79%.

I might implement a user-defined function to automate the choice of "guess". But the heuristic is complicated, especially if there is more than one IRR or none.

 

[CathyMauro]

Thanks so much for your response. I really appreciate it. Could you share an illustration of the table you'd manually construct with the NPV guesses. I'm having a hard time visualizing how that would work.

 

[joeu2004]

Something like the following for the cash flows in columns A:B. See the explanation below the table.


[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[TH]H
[/TH]
[TH]I
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"]Per#
[/TD]
[TD="align: right"]CF
[/TD]
[TD="align: right"]IRR
[/TD]
[TD="align: right"]%Rate
[/TD]
[TD="align: right"]NPV
[/TD]
[TD="align: right"]%Rate
[/TD]
[TD="align: right"]NPV
[/TD]
[TD="align: right"]%Rate
[/TD]
[TD="align: right"]NPV
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-10000
[/TD]
[TD="align: right"]#NUM !
[/TD]
[TD="align: right"]-99.00%
[/TD]
[TD]1.60E+25
[/TD]
[TD="align: right"]-30.00%
[/TD]
[TD="align: right"]4.98E+03
[/TD]
[TD="align: right"]-30.00%
[/TD]
[TD="align: right"]4.98E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]-3088
[/TD]
[TD="align: right"]-29.58%
[/TD]
[TD="align: right"]-90.00%
[/TD]
[TD]1.81E+14
[/TD]
[TD="align: right"]-29.00%
[/TD]
[TD="align: right"]-6.17E+03
[/TD]
[TD="align: right"]-29.90%
[/TD]
[TD="align: right"]3.74E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]-4365
[/TD]
[TD="align: right"]-29.58%
[/TD]
[TD="align: right"]-80.00%
[/TD]
[TD]9.95E+10
[/TD]
[TD="align: right"]-28.00%
[/TD]
[TD="align: right"]-1.50E+04
[/TD]
[TD="align: right"]-29.80%
[/TD]
[TD="align: right"]2.54E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]-3354
[/TD]
[TD="align: right"]-29.58%
[/TD]
[TD="align: right"]-70.00%
[/TD]
[TD="align: right"]1.26E+09
[/TD]
[TD="align: right"]-27.00%
[/TD]
[TD="align: right"]-2.20E+04
[/TD]
[TD="align: right"]-29.70%
[/TD]
[TD="align: right"]1.36E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]-3413
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-60.00%
[/TD]
[TD="align: right"]5.56E+07
[/TD]
[TD="align: right"]-26.00%
[/TD]
[TD="align: right"]-2.74E+04
[/TD]
[TD="align: right"]-29.60%
[/TD]
[TD="align: right"]2.09E+02
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]-3189
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-50.00%
[/TD]
[TD="align: right"]4.53E+06
[/TD]
[TD="align: right"]-25.00%
[/TD]
[TD="align: right"]-3.16E+04
[/TD]
[TD="align: right"]-29.50%
[/TD]
[TD="align: right"]-9.17E+02
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]-2269
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-40.00%
[/TD]
[TD="align: right"]4.51E+05
[/TD]
[TD="align: right"]-24.00%
[/TD]
[TD="align: right"]-3.48E+04
[/TD]
[TD="align: right"]-29.40%
[/TD]
[TD="align: right"]-2.02E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]-4012
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-30.00%
[/TD]
[TD="align: right"]4.98E+03
[/TD]
[TD="align: right"]-23.00%
[/TD]
[TD="align: right"]-3.72E+04
[/TD]
[TD="align: right"]-29.30%
[/TD]
[TD="align: right"]-3.09E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]1647
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-20.00%
[/TD]
[TD="align: right"]-4.07E+04
[/TD]
[TD="align: right"]-22.00%
[/TD]
[TD="align: right"]-3.88E+04
[/TD]
[TD="align: right"]-29.20%
[/TD]
[TD="align: right"]-4.14E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]2151
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-10.00%
[/TD]
[TD="align: right"]-3.64E+04
[/TD]
[TD="align: right"]-21.00%
[/TD]
[TD="align: right"]-4.00E+04
[/TD]
[TD="align: right"]-29.10%
[/TD]
[TD="align: right"]-5.17E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]1579
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00%
[/TD]
[TD="align: right"]-2.83E+04
[/TD]
[TD="align: right"]-20.00%
[/TD]
[TD="align: right"]-4.07E+04
[/TD]
[TD="align: right"]-29.00%
[/TD]
[TD="align: right"]-6.17E+03
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10.00%
[/TD]
[TD="align: right"]-2.21E+04
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20.00%
[/TD]
[TD="align: right"]-1.77E+04
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30.00%
[/TD]
[TD="align: right"]-1.47E+04
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]40.00%
[/TD]
[TD="align: right"]-1.25E+04
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50.00%
[/TD]
[TD="align: right"]-1.09E+04
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]60.00%
[/TD]
[TD="align: right"]-9.67E+03
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]70.00%
[/TD]
[TD="align: right"]-8.70E+03
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]80.00%
[/TD]
[TD="align: right"]-7.91E+03
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]90.00%
[/TD]
[TD="align: right"]-7.26E+03
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100.00%
[/TD]
[TD="align: right"]-6.71E+03
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


The formula =IRR(B2:B12) in C2 returns #NUM .

So I would start with a "table" (range of formulas) in D1:E22. The formula =NPV(D2,$B$2:$B$12) in E2 is copied down the column.

Note that NPV changes sign between -30% and -20%. Usually, that means that NPV is (nearly) zero in between. So I would use the midpoint as a "guess" with the formula =IRR(B2:B12,-25%) in C3. In this example, that is sufficient: IRR returns about -29.58%.

(It is "good practice" to use a formula like =NPV(C3,B2:B12) -- not shown above -- to confirm that NPV returns about zero. As you say: sometimes Excel IRR (more often XIRR) returns incorrect numeric values instead of an error.)

But sometimes, that "guess" is not close good enough, and Excel IRR still returns #NUM . So I would "drill" down with a "table" in F1:G12. (I miswrote before: first, I would use 1% increments.) Again, I would try the midpoint as "guess" with the formula =IRR(B2:B12,-29.5%) in C4.

And if that still returns #NUM , I would "drill" down again with a "table" in H1:I12. And again, I would try the midpoint as a "guess" with the formula =IRR(B2:B12,-29.55%) in C5. Etc, etc, etc.

Some important notes:

1. Although Excel NPV allows negative discount rates, Excel XNPV does not -- an obvious design flaw. So for XNPV, we would use the SUMPRODUCT equivalent, for example (if column A contains dates instead of cash flow numbers):
=SUMPRODUCT($B$2:$B$12/(1+D2)^(($A$2:$A$12-$A$2)/365))

2. I format the NPV as Scientific; otherwise, the column can become unduly wide. If you don't understand that format, remember that we are primarily interested in sign changes. But the magnitude and direction of NPV values might provide a hint that there is not likely to be an IRR, or at least a reasonable one, or that we have to increase the range of rates substantially in order to find an IRR.

3. Even though the NPV changes sign between two discount rates, that does not mean an IRR lies in between. The NPV might not come close enough to zero in between in order for the discount rate to be considered an IRR.

4. There might be multiple IRRs -- that is, multiple changes in NPV signs with NPV=0 in between. Often, that is the result of too many sign changes among the cash flows and wildly varying cash flow values. (And often, that is the result of an incorrect or unrealistic cash flow model.) In that case, we must use "common sense" to choose an appropriate IRR. Typically, common sense would suggest a negative IRR if the sum of the undiscounted cash flows is negative, and a positive IRR if the sum is positive. Usually, that is most "appealing" to us. But unfortunately, the correct IRR does not always follow such common sense. Mathematically, we can legitimately have a negative IRR for a positive sum of undiscounted cash flows, and vice versa.

 

[joeu2004]

FYI, I could not respond to your PM. I get the error: "CathyMauro has exceeded their stored private messages quota and cannot accept further messages until they clear some space". My response is....

I want to say "yes". But I cannot commit to any formal arrangement. My hands are full dealing with an elderly parent, who is in the middle of some major life changes right now.

 

[CathyMauro]

I've been on this forum for quite a while, but with an alias that I've forgotten, so I had to create a new profile in order to make a new thread. This time to keep it simple I went with my real info. However, I'm guessing that since it's a new account I have no storage space for private messages.

Please email me directly at cathy.cmsolutions@gmail.com.

I understand about your caregiving requirements (I cared for my late husband for 15 years), and can certainly work around them.

It's one of the reasons why I freelance in Excel now -- gave me the freedom to work when I can, and also stimulated my mind.