Forum Discussion
Help with #NUM error in IRR formula
IRR requires at least one negative value (as initial cost of business), see at https://support.office.com/en-us/article/IRR-function-64925EAA-9988-495B-B290-3AD0C163C1BC
mah2023 wrote: ``what should I do?``
First, you should take a critical look at the Excel file that HansVogelaar provides to be sure that he understood your cash flows correctly.
In particular, he considers only the first 33 cash flows that you numbered in your question to Sergei Baklan.
But in your question to hasan ahmed, you have 35 numbered cash flows.
Nevertheless, if we enter the additional cash flows into A34:A35 and calculate =IRR(A1:A35), the formula has no problem returning -0.486775222080371%, even without a "guess" parameter.
So....
-----
Second, you should present the data in a spreadsheet.
Ideally, attach an Excel file that demonstrates the problem.
Alternatively, provide a link to the Excel file that you uploaded to a file-sharing website that does not require that we log in to download the file.
The devil might be in details that we cannot see in the text that posted.
And most importantly, you should show us the formula that returns a #NUM error.
Is it truly =IRR(A1:A33), as HansVogelaar assumes, tacitly?
Or is it =IRR(A1:A35), as I assume? (If so, the data is not as it appears in your postings.)
Or is it something else entirely?
35 cash flows is correct(a1:a35), but unfortunately, an error appears after entering the formula(as the attach excel file) .
mah2023 wrote: ``35 cash flows is correct(a1:a35), but unfortunately, an error appears after entering the formula(as the attach excel file)``
Amazing what a little detail will reveal! Please keep that in mind for all future questions.
Refer to the image below, and see the attached Excel file for details.In general, when Excel IRR returns #NUM and there is nothing wrong with the data, we need to provide a "guess" (optional second parameter) in order to the Excel algorithm.
Unfortunately, there is no "good" way to choose a good guess.
The NPV curve in columns S:T suggests that there might be IRRs between -10% and -5% and between -5% and 0%, since the NPV changes signs in T23:T25.
In fact, Solver (not shown) finds IRRs at about -5.1703457688% and -0.486775222%.
Nevertheless, Excel IRR cannot find those IRR, even with a good "guess".
There are several factors that might contribute to that "failure".
The major factor seems to be the magnitude of the cash flows, which is extremely large.
With integer parts of 10 to 13 digits, they are already near the limits of 64-bit binary floating-point precision.
If we scale the cash flows down by 1E+06 (1 million), Excel IRR has no problem finding the IRRs with very little loss in precision relatively.
The IRR formulas are:
O40: =IRR(O4:O38/1000000)
O41: =IRR(O4:O38/1000000, -10%)
To confirm the results, the NPV formulas are:
Q40: =NPV(O40, $O$4:$O$38)
Q41: =NPV(O41, $O$4:$O$38)
The NPVs of -8.61E-04 and 5.63E-04 are sufficiently close to zero, IMHO.
- I mean: Is there a problem with my Excel settings?
mah2023 wrote: ``why does my Excel not show the negative number and gives an error?``
mah2023wrote: ``Is there a problem with my Excel settings?``
It seems that you still have not learned from past mistakes, namely: "with great details come great answers", to paraphrase Uncle Ben's advice to Spider-Man. (wink)
You say that Excel does "not show the negative number", and it still "gives an error".
Again, attach an Excel that demonstrates those problems.
What is your formula(s)? What is the data, if not the same as before?
Exactly what does Excel display? Show us a screenshot, or attach a JPG file with a screenshot.
Using your previous Excel file attachment, when I enter the formula =IRR(O4:O38/1000000) into O40 and =IRR(O4:O38/1000000, -10%) into O41, as I suggested, this is what I see:
I changed the format for O4:O38 to display 2 decimal places and to separate 1000s with commas.
(Aside.... Many of the values in O4:O38 have more than 2 decimal places, up to 5 decimal places. And all of the values have what I call "floating-point residuals": additional precision beyond 15 significant digits, which Excel does not format.)
The point is: I do not see any Excel error.
Instead, O40 displays red 0.00, and O41 displays red (0), even though the cell values are negative.
That is because of the cell formats: 0.00;[Red]0.00 for O40, and 0_);[Red](0) for O41.
Change the formats to Percentage with 2 decimal places to see the negative cell values (rounded), namely -0.49% in O40 and -5.17% in O41.
Increase the number of decimal places (up to 15) to see the greater precision of the cell values.
-----
If that does not answer your question, follow all of the instructions above to improve our understanding of the problem(s).
If you need instruction on how to set the cell format, I'm afraid that varies for different versions of Excel.
So, let us know what version of Excel you are using, and on what device (desktop, laptop, tablet, smartphone, etc).
For my version of Excel, I can select the cell(s), right-click, click Format Cells and the Number tab, then select the desired format.
Alternatively, I can select a limited set of format options from the Home "ribbon".
- Thank you. According to your explanation, this cash flow has a negative IRR. So why does my Excel not show the negative number and gives an error?