[Excel] Error: #NUM! in IRR, RATE or Financial Formulas — How to Fix Non-Converging Iterations

Summary

The #NUM! error in Excel’s financial functions (IRR, RATE, XIRR) occurs when Excel’s iterative algorithm fails to find a valid numeric solution. This happens if cash flows don’t contain both positive and negative values, if the guess is too far from the true root, or if the mathematical problem has no valid solution. Fix it by checking cash flow signs, adding a better guess, or adjusting iteration settings. Works in Excel 2010+, 2016, and Microsoft 365.

Context

Financial formulas like IRR (internal rate of return) and RATE solve equations that require iteration. Excel starts from a guess and refines it until the result satisfies the formula within a precision threshold. When the pattern of cash flows or payments does not allow a valid rate — or when the guess leads the solver astray — Excel stops after 20 iterations and displays #NUM!. The same can occur with XIRR when date order or magnitude inconsistency prevents convergence.

Probable Cause

  • No sign change in cash flows. All inflows or all outflows — there is no possible rate that balances NPV to zero.
  • Poor initial guess. The default (0.1 or 10%) may be too far from the actual solution.
  • Multiple internal rates. When cash flows alternate between positive and negative, the equation can have several roots, some invalid.
  • Extreme or inconsistent parameters. Unrealistic payment/period combinations or large magnitudes cause overflow.
  • Insufficient iterations. The solver may stop before converging if global iteration limits are too low.

Quick Fix

  1. Ensure valid cash flow pattern. There must be at least one positive and one negative cash flow.
A1:A6 = -1000, 200, 300, 400, 500, 600  ✅ valid
A1:A6 = 100, 200, 300, 400, 500          ❌ all positive → #NUM!
  1. Add a reasonable guess argument. This helps Excel’s iterative process converge faster.
=IRR(A1:A6, 0.1)
=RATE(60, -200, 10000, 0, , 0.05)
  1. Try different guesses. If one fails, test 0.1, 0.2, or 0.5 to find a working root.
=IRR(A1:A6, 0.2)
  1. Use XIRR for irregular cash flows. It handles actual dates and converges more reliably.
=XIRR(A1:A6, B1:B6)
  1. Adjust iteration settings (if needed).
File → Options → Formulas → Enable Iterative Calculation
Maximum Iterations: 100
Maximum Change: 0.0001

Full Example

Scenario 1: Simple investment with valid sign change.

Cash Flows (A2:A6):
-1000
200
300
400
500

Formula:

=IRR(A2:A6)
→ 17.09%

Scenario 2: All cash flows positive → no IRR.

Cash Flows (A2:A6):
100
200
300
400
500

Formula:

=IRR(A2:A6)
→ #NUM!

Fix: Include at least one negative value representing the initial investment.

=IRR(A2:A6, 0.1)

Scenario 3: Multiple sign changes — use XIRR for stability.

Values: -1000, 3000, -2500, 800
Dates:  01/01/2024, 03/01/2024, 06/01/2024, 12/01/2024
Formula: =XIRR(A1:A4, B1:B4)

Result: Returns a valid rate where IRR would fail due to complex sign changes.

Pitfalls & Debug

  • Symptom → #NUM! despite valid data. Fix → Add a closer guess or test multiple guesses.
  • Symptom → XIRR fails on duplicated or unordered dates. Fix → Ensure all dates are unique and sorted chronologically.
  • Symptom → RATE() outputs #NUM!. Fix → Adjust nper, pmt, or pv so they align logically.
  • Symptom → IRR loops forever with iteration enabled. Fix → Reduce iteration count and verify the cash flow pattern includes both signs.
  • Symptom → Non-convergent models with extreme cash flows. Fix → Normalize or scale values to realistic magnitudes.

Validation & Next Steps

Before relying on results, confirm that the function found a meaningful rate:

Plot cumulative cash flows to verify at least one sign change.
Test NPV at different rates to confirm that IRR crosses zero.
Use Goal Seek (Data → What-If Analysis → Goal Seek) to validate IRR results manually.

Wrap your financial formulas in IFERROR() to handle failed iterations gracefully:

=IFERROR(IRR(A2:A6, 0.1), "No valid IRR")

Sources

https://support.microsoft.com/en-us/office/how-to-correct-a-num-error

https://support.microsoft.com/en-us/office/irr-function

https://support.microsoft.com/en-us/office/xirr-function

https://support.microsoft.com/en-us/office/rate-function

Labels

Tool/Excel, OS/Cross-platform, Topic/Financial Iteration & Convergence