How to Fix Excel IRR Formula Returning #NUM! or #DIV/0! Errors
Question details
The user needs to resolve #NUM! and #DIV/0! errors when calculating the Internal Rate of Return (IRR) in Excel, which persist even after modifying the guess and maximum iteration settings.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating financial returns or average rates of change using a sequence of numeric data points.
- Observed behavior
- The IRR formula fails to compute a valid rate and instead outputs a #NUM! or #DIV/0! error code.
Verify that your data set contains at least one positive and one negative value, as the IRR mathematical model strictly requires both cash inflows and outflows to compute a result.
Use the GEOMEAN Function for Periodic Rates of Return
If your data consists of periodic percentage rates of change rather than actual monetary cash flows, the IRR function is mathematically incorrect and you should use a geometric mean.
The IRR function is built exclusively for analyzing cash flows (like deposits and withdrawals). Applying it directly to periodic rates of return will produce incorrect results or calculation errors.
To find the correct average rate of return across multiple periods, the GEOMEAN function will provide an accurate average rate of change without triggering calculation limit errors.
Click on an empty cell where you want the final average rate of return to be displayed.
Type the formula =GEOMEAN(1+A4:A115)-1 into the formula bar. Be sure to replace A4:A115 with the actual cell range containing your data.
If you are using an older version of Excel, you must press Ctrl+Shift+Enter simultaneously to evaluate it properly as an array formula. Modern versions can just use Enter.

Adjust the IRR Guess Parameter Based on the NPV Curve
When dealing with valid cash flows that still return errors, manually calculating a more accurate 'guess' parameter helps the algorithm find the result.
Handle Complex Financial Models with WPS Office Spreadsheet
If you frequently work with advanced financial modeling, array formulas, and functions like IRR or GEOMEAN, WPS Office Spreadsheet provides a robust, lightweight, and highly compatible alternative to Microsoft Excel.
- 1. Download and Install: Visit the WPS Office website to download the free installer and follow the quick setup wizard.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing .xlsx financial files.
- 3. Calculate Data: Use your favorite financial formulas, including IRR and GEOMEAN, exactly as you would in Microsoft Excel.

Frequently Asked Questions
Why didn't changing the Maximum Iterations setting fix my IRR error?
Despite common misconceptions and some outdated documentation, changing the Maximum Iterations option in Excel's settings does not alter the hardcoded iteration limit of financial functions like IRR, XIRR, or RATE. You must adjust the formula's 'guess' parameter instead.
What does a #DIV/0! error mean when using the IRR function?
In the specific context of the IRR function, a #DIV/0! error typically means the same thing as a #NUM! error: the internal algorithm reached its calculation limit without finding a valid mathematical solution for your dataset.
Can I use the IRR function for periodic percentage rates?
No, the IRR function requires external net cash flows (monetary deposits and withdrawals) to calculate properly. Applying it to periodic rates of return is mathematically incorrect and will often result in errors. You should use the GEOMEAN function instead.
How do I choose the right 'guess' parameter for my IRR formula?
The most reliable method is to plot a Net Present Value (NPV) curve for your cash flows at different discount rates. Find the rate where the NPV crosses zero (changes from positive to negative) and use a percentage close to that crossing point as your guess.




