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: ``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".
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)
OK, thanks for the correction.
HansVogelaar wrote: ``As @Joe User has explained very patiently [....]``
Thanks. I tried to be.
HansVogelaar 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.
As JoeUser2004 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.
