Jul 13 2019 11:05 AM
In the attached image and file (See tab B&C, Cell B15,) I'm having difficulties with the IRR formula as it is displaying a #NUM error instead of a numerical value. What seems to be the issue here?
Jul 13 2019 11:33 AM
SolutionIRR 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
Jul 13 2019 11:37 AM
Hi Hasan,
While calculating IRR you need to input outflow with negative sign.
I have edited it, please refer the attached file,
Thanks,
Tauqeer
May 23 2023 02:09 AM
I have the same problem. please check the table and help me.
cash flow | |||
1372000000000 | 1 | ||
0 | 2 | ||
(77777777778) | 3 | ||
(73009451097) | 4 | ||
(68241124416) | 5 | ||
(63472797735) | 6 | ||
(58704471055) | 7 | ||
(53936144374) | 8 | ||
(49167817693) | 9 | ||
(1416399491012) | 10 | ||
(39631164332) | 11 | ||
42914940127 | 12 | ||
42914940127 | 13 | ||
42914940127 | 14 | ||
42914940127 | 15 | ||
40076609987 | 16 | ||
37238279847 | 17 | ||
34399949707 | 18 | ||
31561619568 | 19 | ||
28723289428 | 20 | ||
25884959288 | 21 | ||
23046629148 | 22 | ||
20208299008 | 23 | ||
17369968868 | 24 | ||
17369968868 | 25 | ||
17369968868 | 26 | ||
17369968868 | 27 | ||
15439972327 | 28 | ||
13509975786 | 29 | ||
11579979246 | 30 | ||
9649982705 | 31 | ||
7719986164 | 32 | ||
5789989623 | 33 | ||
3859993082 | 34 | ||
1929996541 | 35 | ||
IRR | #NUM! |
| |
|
May 23 2023 02:11 AM
May 23 2023 03:25 AM
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.
May 28 2023 11:28 PM - edited May 28 2023 11:32 PM
@mah2023 wrote: ``what should I do?``
First, you should take a critical look at the Excel file that @Hans Vogelaar 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 @Hans Vogelaar 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?
May 29 2023 01:16 AM - edited May 29 2023 01:59 AM
35 cash flows is correct(a1:a35), but unfortunately, an error appears after entering the formula(as the attach excel file) .
May 29 2023 01:36 AM
May 29 2023 06:27 AM
@Hans Vogelaar 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)
May 29 2023 06:39 AM
I did try it with the data as originally posted, but my memory is admittedly a bit hazy.
May 29 2023 02:18 PM - edited May 29 2023 03:27 PM
@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.
May 29 2023 08:56 PM
May 29 2023 09:48 PM
May 29 2023 11:59 PM - edited May 30 2023 05:56 AM
@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".
May 30 2023 02:43 AM
hi- Thank you for your explanation
Information : Excel 2016 -device: pc (desktop)- File: It is the same as the initial file.
I know the basics of working with Excel, such as formatting and... (wink).
My main question: When I enter the yield formula into the cell it gives NUM error. Why should I reduce the unit (divide by 1,000,000) when the cash flow amounts are the same numbers? (see attach file- sheet IRR2)
May 30 2023 02:56 AM
As @Joe User has explained very patiently, the cash flows are so large that the calculations involved approach or even exceed the limit of what Excel's 64-bit floating point engine can handle.
By dividing the values by 1000000, they become smaller, making the calculation manageable. And since all values are reduced in proportion, the result is valid for the original numbers too.
By the way, it does not have to be 1000000. 10 would have worked too, but 1000000 is safer.
See the attached workbook.
May 30 2023 03:31 AM - edited May 30 2023 03:39 AM
@Hans Vogelaar wrote: ``As @Joe User has explained very patiently [....]``
Thanks. I tried to be.
@Hans Vogelaar wrote: ``exceed the limit of what Excel's 64-bit floating point engine can handle``
I amended my explanation later. Such large values strain the limits of 64BFP precision.
Of course, 64BFP "can handle" much larger values, up to about 1.8E+308.
May 30 2023 03:37 AM
OK, thanks for the correction.
Jul 13 2019 11:33 AM
SolutionIRR 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