IRR formula error: #NUM!

Question

IRR formula results in #NUM! error.

I have set the preferences to iterative calculations as suggested and it still does not work.

I use the latest Office 365 on MAC products, and am relatively new to Excel (MBA student).

Please can anyone help me?  I will be really greatful.

Kind Regards

Jan

Answer 1

Jan wrote:

IRR formula results in #NUM! error. [....] Please can anyone help me?  I will be really greatful.

You should post your formula and data.

If the data is a realistic cash flow model, the #NUM error might simply indicate that the Excel function (IRR, XIRR  or RATE?) could not derive the IRR within the limitations of its internal algorithm.

The usual remedy is to specify a "guess" argument in order to override the default "guess" of 10%.

However, that might be easier said than done.  See below for one methodology.

And more often than not, the #NUM error arises because the cash flow model is not realistic:  either the cash flows themselves are unrealistic, or the model is set up incorrectly.

Or it might be a realistic cash flow model with a negative IRR.  A negative IRR is not meaningless or necessarily incorrect (unless it is less than -- more negative than -- -100%).  It might simply mean that the project or investment operates at a loss, especially when time-value is taken into account.

But with a negative IRR, the Excel functions are more likely to return a #NUM, especially as the number of cash flows increases.  I could explain; but suffice it to say:  it is a number theory issue.

IMHO, the best way to recognize and resolve all of these issues is to calculate the NPV curve for varying discount rates.  For example:

The formula in D2 is =NPV(C2, $A$2:$A$22).  Copy D2 into D3:D22.  Of course, extend the table if you suspect the IRR is larger than 100%.

(Caveat: XNPV does not allow a negative discount rate, for no good reason.  Use SUMPRODUCT instead.  See the math formula in the XNPV help page.)

In this example, the default Excel IRR formula (A24) returns #NUM.

But the NPV table (D2:E22) shows that the NPV curve crosses zero not just once, but three times.  In other words, there are three mathematical IRRs (!).

Which one to choose is a judgment call.  Since the sum of the nondiscounted cash flows is negative, I would choose a negative IRR.

(In this example, there is only one negative IRR.  But in other examples, there are multiple negative IRRs.  The choice can be difficult sometimes.)

To find the negative IRR, I would choose a "guess" of -15%, which is halfway between -20% and -10%, where the NPV crosses zero.  That results in the IRR in F11, which generates a near-zero NPV in G11.

Sometimes, it is not that simple.  The mathematical IRR algorithm and especially the Excel implementation are erratic.  Sometimes, it helps to "drill" down into the NPV curve; for example, using the range -20.0%, -19.9%, etc to -12.9%.  And sometimes, the Excel IRR, XIRR or RATE function simply fails.  (Sigh!)  In that case, we might try Solver; but that might fail, too.

Sometimes the NPV curve demonstrates that the IRR is not computable because the NPV curve never crosses zero.

The take-away is:  finding the IRR can be as much an art as it is a science, if it is computable at all.

Answer 2

Hi Jan,

This formula error often happens when you’ve entered a numeric value using a data type or a number format that’s not supported in the argument section of the formula.

You can find detailed information in this article:

How to correct a #NUM! error.

Regards,

Tina

Answer 3

Hi Jan,

Do you need further help? Can you share any updates with us?

Regards,

Tina