Formula Errors

How to Fix Excel IRR Formula Returning #NUM! or #DIV/0! Errors

Anum MushtaqAnum Mushtaq Nov 2, 2026 870 views

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.

How to Fix the Excel IRR Formula Returning #NUM! or #DIV/0!
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click on an empty cell where you want the final average rate of return to be displayed.

2
Enter the GEOMEAN array formula

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.

3
Execute as an array formula

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.

Use the GEOMEAN Function for Periodic Rates of Return
Formatting Tip: To accurately verify the precision of your results, format your target cell as Scientific with 14 decimal places via the Format Cells menu.
Free Microsoft Office alternative

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. 1. Download and Install: Visit the WPS Office website to download the free installer and follow the quick setup wizard.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing .xlsx financial files.
  3. 3. Calculate Data: Use your favorite financial formulas, including IRR and GEOMEAN, exactly as you would in Microsoft Excel.
100% compatible with Microsoft Excel (.xlsx) formats and financial formulasLightweight architecture ensures fast calculation of large financial datasetsFamiliar ribbon interface requires no learning curve to switchCompletely free to download and use for your daily financial modeling
microsoft office alternative - wps office

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.