#NUM! in IRR: Why the formula needs a “Guess”

Excel returns a #NUM! error from the IRR function when its calculation engine cannot calculate an internal rate of return within 20 iterations, or when the cash flow series lacks the required combination of positive and negative values. Because internal rate of return equations cannot be solved through direct algebra, Excel uses an iterative numerical search. If the algorithm fails to converge on an answer accurate to 0.00001% within 20 calculation cycles, it halts and displays #NUM!.

Quick Fix

Supply a starting estimate in the optional [guess] argument: =IRR(A2:A10, -0.1) for low or negative expected returns, or =IRR(A2:A10, 0.5) for high returns. If the error persists, verify that your range contains at least one negative value (cash outflow) and at least one positive value (cash inflow); otherwise, a valid IRR is mathematically impossible.

---

The Mathematical Reason for the “Guess” Parameter

The Internal Rate of Return is the discount rate ($r$) that forces the Net Present Value (NPV) of a cash flow stream to exactly zero:

$$
\text{NPV} = \sum_{t=0}^{n} \frac{C_t}{(1 + r)^t} = 0
$$

Because $r$ appears in polynomial powers across multiple periods, Excel cannot isolate $r$ in a single step. Instead, it starts with an initial baseline rate—the Guess—and adjusts that value incrementally using the Newton-Raphson method:

=IRR(values, [guess])

• If you omit [guess], Excel defaults to 10% (0.1).
• Starting from 10%, the engine calculates the resulting NPV and evaluates the slope of the curve.
• It adjusts the interest rate estimate and recalculates, repeating up to 20 times.
• If the sequence of cash flows is irregular, highly volatile, or produces an NPV curve whose slope flattens near 10%, the algorithm can oscillate, diverge, or move too slowly to converge. At iteration 20, Excel aborts the process and outputs #NUM! as outlined in #NUM! in Mathematical Iterations: Fixing Excel’s Calculation Limits.

---

Primary Causes of #NUM! in IRR

Cause 1: Missing Inflow or Outflow (No Sign Change)

An IRR calculation requires an initial investment (negative cash flow) followed by subsequent returns (positive cash flows), or vice versa:

• If every cell in your cash flow series is positive, money was generated without any investment.
• If every cell is negative, money was lost without any recovery.
• In both cases, NPV can never equal zero at any positive or negative discount rate. Excel searches 20 times and throws #NUM!.

Cause 2: Extreme or Negative Returns

When an investment experiences heavy losses, the actual IRR might be negative (e.g., $-45\%$):

• Starting from the default guess of +10%, Excel’s search algorithm may step in the wrong direction or fail to reach $-45\%$ within 20 cycles.
• Supplying a negative guess tells the engine where to begin: =IRR(A2:A7, -0.2)

Cause 3: Multiple Sign Flips (Multiple Internal Rates of Return)

If cash flows change signs multiple times—for instance, an initial investment ($-$), positive operations ($+$), a mid-project overhaul ($-$), and a final recovery ($+$)—the polynomial can yield multiple mathematically correct rates of return, discussed in #NUM! in IRR: Handling multiple internal rates of return in non-conventional cash flows.:

• When multiple solutions exist, the algorithm can get trapped bouncing between two potential solutions until it hits the 20-iteration cap.
• Providing a specific [guess] guides Excel toward the rate most relevant to your financial analysis.

---

Diagnostic and Resolution Sequence

Step 1: Audit Cash Flow Direction

Verify that your cash flow series has at least one sign change:

1. In an empty cell, count negative values: =COUNTIF(A2:A20, "<0")
2. Count positive values: =COUNTIF(A2:A20, ">0")
3. Both formulas must return a count of at least 1. If either returns 0, correct your data inputs.

Step 2: Systematically Test Guess Values

If cash flow signs are valid, test a series of manual guesses inside the formula:

• Try a low positive return: =IRR(A2:A20, 0.02)
• Try a high positive return: =IRR(A2:A20, 0.50)
• Try a negative return: =IRR(A2:A20, -0.30)

Testing these anchors will usually locate the convergence zone and produce a valid percentage.

Step 3: Switch to XIRR for Irregular Dates

The standard IRR function assumes every cash flow occurs at perfectly equal intervals (e.g., exactly 365 days apart or exactly one month apart). In real-world projects, investments and payouts occur on specific, irregular calendar dates.

Using IRR on irregular dates can confuse the iteration engine. Use XIRR instead, which pairs cash flows with exact dates as explained in #NUM! in XIRR: Solving the “Result not found” error for sporadic investment dates.:

=XIRR(A2:A20, B2:B20, 0.1)

Where column A contains cash flows and column B contains transaction dates.

Step 4: Use MIRR for Non-Conventional Cash Flows

If your project features alternating inflows and outflows that trigger multiple rates of return, abandon standard IRR in favor of MIRR (Modified Internal Rate of Return).

MIRR resolves iteration failures by accepting explicit financing and reinvestment rates, eliminating iteration loops entirely:

=MIRR(A2:A20, 0.08, 0.10)

• 0.08: The cost of capital to fund negative cash flows.
• 0.10: The reinvestment rate for positive cash flows.

Because MIRR calculates via direct algebra rather than iterative searching, it avoids iteration-based #NUM! failures.

To understand related iteration limits in financial math, read #NUM! in RATE: Iteration limits exceeded. To understand how Excel handles period timing discrepancies, see NPV Function Trap: Why the NPV function in Excel is logically “wrong” (Period 0 vs Period 1)., and review foundational error logic across formulas in Fixing #VALUE!, #DIV/0!, and #NUM! Errors: The Excel Data Logic Guide.