Help with #NUM error in IRR formula

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.