#NUM! Error in IRR Formula: Troubleshooting Guide
#NUM!IRRThe IRR (Internal Rate of Return) function is powerful for analyzing investment returns, but it frequently triggers a #NUM! error that frustrates even experienced Excel users. This happens because IRR uses an iterative calculation method to find the rate of return, and it can fail when the underlying data doesn't support a mathematical solution. Common culprits include cash flows that never change sign, insufficient data points, or an initial guess that's too far from the actual rate. The good news? This is one of Excel's most straightforward errors to resolve. With a few quick checks of your data structure and formula parameters, you'll have IRR working reliably. Understanding why IRR struggles in specific scenarios puts you in control and helps you build more robust financial models.
Why IRR causes #NUM!
No sign change in cash flow series
IRR requires at least one positive and one negative cash flow to calculate a rate of return. If all values are positive or all negative, IRR cannot converge to a solution. This is the most common cause of #NUM! in IRR.
=IRR(B2:B5) where B2:B5 contains [1000, 2000, 3000, 4000] (all positive values)No convergence within 20 iterations
IRR uses an iterative algorithm that attempts to find the rate within 20 iterations. If the cash flow pattern is irregular or extreme, the algorithm may fail to converge to a solution even if a mathematical solution exists. Providing a better initial guess can help.
=IRR(B2:B10) where cash flows are highly irregular like [−100000, 1, 1, 1, 50000, −500, 100, −50, 200] without a reasonable guess parameterInvalid guess parameter causes non-convergence
If the optional guess parameter is too far from the actual IRR, the iterative calculation may fail to converge within 20 iterations. The guess should be a reasonable estimate of the expected rate (typically between −0.99 and a positive value).
=IRR(B2:B5, 10) where the guess of 1000% (10 as decimal) is unrealistic and prevents convergence to the actual IRRStep-by-Step Solution
- 1Click on the cell displaying the #NUM! error to select it and view the formula in the formula bar
- 2Verify your cash flow values include both positive and negative numbers—IRR requires at least one positive and one negative value to calculate a rate of return
- 3Check that your cash flow range is continuous with no blank cells; select the range and use Ctrl+Down to identify gaps, then fill or remove them
- 4Ensure your initial investment (typically the first value) is negative and subsequent cash flows are positive; if all values have the same sign, IRR cannot converge
- 5Examine the magnitude of your cash flows for extreme values or decimal precision issues; try scaling values or adjusting decimal places using Format Cells (Ctrl+1)
- 6Add an optional guess parameter to the IRR formula (e.g., =IRR(range, 0.1)) to help Excel converge on a solution, starting with 10% or 20%
- 7If the error persists, wrap the formula in IFERROR to return an alternative value: =IFERROR(IRR(range, 0.1), "Unable to calculate") and press Enter
- 8Verify the result makes logical sense; if IRR returns an extreme percentage, review your cash flow sequence and timing to ensure data accuracy
Concrete Example
Investment project evaluation with negative cash flows
A finance manager evaluates a real estate development project using IRR to determine if the 15% required return threshold is met. The project requires an initial investment of $500,000, followed by mixed positive and negative annual cash flows over 5 years.
Before (error)
=IRR(B2:B6)After (fixed)
=IRR(B2:B6,0.1)Problem: The #NUM! error appears because all cash flows are positive (no initial negative investment), or the cash flow series contains only negative values. IRR cannot calculate a rate of return when there's no sign change in the cash flows—it mathematically cannot solve the equation.
Solution: Ensure the cash flow series includes at least one negative value (typically the initial investment) and at least one positive value. If the error persists, provide an initial guess parameter or restructure the cash flows to reflect the actual investment pattern.
Prevention Tip
Ensure your cash flow series contains both positive and negative values; IRR returns #NUM! when all values have the same sign or when no discount rate exists that makes NPV equal zero. Test your data by checking that inflows and outflows are properly signed (negative for initial investment, positive for returns).
Free Tools to Fix Your Formulas
Use these free tools to avoid this error:
Excel Formula Generator
Describe what you want to calculate and get the Excel formula instantly
VLOOKUP Generator
Generate VLOOKUP formulas instantly by describing what you need in plain English
Excel Formula Explainer
Paste any Excel formula and get a clear, step-by-step explanation powered by AI. Understand complex formulas instantly.