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
IRR uses an iterative algorithm to find the rate. Apparently, the iteration failed.
I tried several methods using the Solver add-in to find the rate. GRG Non-Linear and Simplex LP failed. Evolutionary came close, but was still a bit off.
So I set up a simulation - see the attached workbook.
It shows that the NPV switches from + to - near the limit of Excel's precision. Note the repetitions in the NPV column.
so what should I do?
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?
HansVogelaar wrote: ``I had tried 35 cash flows too, but also got #NUM! mah2023's data are not exactly as displayed``
"Had tried" or "now tried"?
We didn't have access to the exact cash flows until now. Right?
Anyway, amazing what a little detail will do! (wink)
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.
