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
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.
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.
OK, thanks for the correction.