Why IRR returns #NUM! ?

[excelos]

Hello

I select a range of cells for the IRR formula and it returns #NUM !
I formatted the range to be Numbers but nothing changed.

Any idea?

 

[sandy666]

from Microsoft:


Guess (Optional). A number that you guess is close to the result of IRR.

• Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM ! error value is returned.
• In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
• If IRR gives the #NUM ! error value, or if the result is not close to what you expected, try again with a different value for guess.

 

[excelos]

I tried with 0.2, 0.5, 0.9 and none fixed it. Any hint?

 

[joeu2004]

Usually, the #NUM error indicates the need to provide a "guess", the last parameter of Excel IRR and XIRR.

Other indications: a #DIV/0 error; and a bogus "IRR" that might appear to be zero, but which is actually +/2.98E-09.

AFAIK, there is no reliable formula for determining a "guess". And with Excel XIRR, in particular, even a good "guess" sometimes fails. (Sigh.)

I construct a table that represents the "NPV curve" in order to determine a "guess". LMK if you need help with that.

Often, however, such erroneous results indicate an incorrect cash flow model.

If you still have problems, I suggest that you post your cash flow values (and dates for Excel XIRR), together with an explanation of what they represent as well as the IRR formula that you use.

 

[joeu2004]

I tried with 0.2, 0.5, 0.9 and none fixed it. Any hint?

Our responses criss-crossed in the internet.

Again, I suggest that you post the cash flows (and dates for Excel XIRR) as well as your IRR formula(s).

Assuming a valid cash flow model (although I suspect it is not), I construct the "NPV curve" as follows.

In one column (e.g. R1:R21), enter possible discount rates -99%, -90%, -80%, etc through 100%. You might need to extend that to 200%, 300%, etc.

For Excel IRR, in a parallel column, enter formulas of the form =NPV(R1,$C$1:$C$20), where C1:C20 are the cash flows.

For Excel XIRR, we cannot use XNPV because it does not allow negative discount rates (a defect!). Instead, in a parallel column, enter formulas of the form =SUMPRODUCT($C$1:$C$20/(1+R1)^(($D$1:$D$20-$D$1)/365)), where D1:D20 are the corresponding dates.

If you see a sign change between two NPVs, use the midpoint of the corresponding discount rates as a "guess".

If you do not see a sign change, that usually indicates that either there is no IRR (it happens), or the cash flow model is incorrect (more likely).

 

[excelos]

Hello, this is the evolution of the amount (starting at 375,000) in the future:

375,000.0 250,000.0 125,000.0 - - - - - - - - 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 14,964,737.9 65,694,409.7 117,438,674.9 169,182,940.1 221,962,090.6 274,741,241.1 328,575,974.7 382,410,708.2 437,322,136.4 492,233,564.6 548,243,221.4 604,252,878.2 661,382,728.1 718,512,578.0 776,785,024.9 835,057,471.8 894,495,367.6 953,933,263.4

I use the formula IRR(range of the above cells)

Any hint please?

 

[alansidman]

Having a hard time distinguishing what the numbers are, but it appears that there is not negative number for the end value that was received for all the outputs.

 

[excelos]

Having a hard time distinguishing what the numbers are, but it appears that there is not negative number for the end value that was received for all the outputs.

"there is not negative number for the end value" why does the end value needs to be negative? The investment starts at 375,000 and ends up at 953,933,263.

"that was received for all the outputs" I dont understand what that means.

 

[joeu2004]

There does not have to be a negative value at the end per se. But there must be at least one negative value. In fact, it is usually the first one (the initial investment).

Why? To begin with, because that is what the help page for Excel IRR states. To wit: ``Values must contain at least one positive value and one negative value to calculate the internal rate of return``. See https://support.office.com/en-ie/article/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc .

As the help page explains: ``The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods``.

That is not entirely correct. The investment consists of inflows and outflows; we can choose either sign for inflows and the opposite sign for outflows. That is true for most Excel financial functions.

However, note that an "inflow" or an "outflow" refers to external funds, not periodic balances. I suspect that your non-zero values are balances.

By "external funds", I mean: additional investments (same sign as 375,000) and withdrawals (opposite sign).

Finally, in your presentation of the "cash flows", it is unclear what you mean by dash ("-") versus zero.

For your next posting, please present numbers on separate lines, one for each period.

And please explain all of the numbers. For example, you explain that 375,000 is the initial investment, and 953,933,263(!) is final balance. What are the intermediate values?

 

[joeu2004]

This is my guess for your cash flows in A1:A43.

-375,000.00
-250,000.00
-125,000.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
14,964,737.90
65,694,409.70
117,438,674.90
169,182,940.10
221,962,090.60
274,741,241.10
328,575,974.70
382,410,708.20
437,322,136.40
492,233,564.60
548,243,221.40
604,252,878.20
661,382,728.10
718,512,578.00
776,785,024.90
835,057,471.80
894,495,367.60
953,933,263.40

In other words, the first 3 values are inflows (investments), followed by 22 periods of no cash flows, followed by 18 periods of withdrawals (net outflows). The first value must always be the initial balance or investment; the last value must always be the final balance, oppositely signed.

=IRR(A1:A43) returns 32.304448866336% in B1. As a check, note that =NPV(B1,A1:A43) is 9.45E-10, very nearly zero.