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

Excel returns a #NUM! error in the IRR function when its calculation engine fails to converge on a valid discount rate within 20 iterations. Because the internal rate of return cannot be solved using direct algebra, Excel uses an iterative trial-and-error process starting at a default initial rate of 10% (0.1). If the cash flows are unconventional, contain non-standard return profiles, or require a rate far from 10%, Excel exceeds its iteration limit and outputs #NUM!.

Fast-Fix: The 45-Second Solution

Excel throws a #NUM! error in =IRR(values, [guess]) because its iterative engine fails to find a discount rate that yields a zero Net Present Value within 20 cycles using the default 10% seed. To fix this, supply a realistic starting estimate as the second argument, such as =IRR(A1:A10, 0.5) for high returns or =IRR(A1:A10, -0.2) for losses. Also, ensure your range contains both negative initial investments and positive cash inflows.

Quick Risk Snapshot

• Severity Tier: Moderate (Breaks corporate capital budgeting models and investment yield schedules).
• Is it safe to ignore?: No. A #NUM! error invalidates hurdle rate evaluations, waterfall distributions, and financial decision models.
• Most common cause: Omitting the optional [guess] argument when the true return is significantly higher than 10%, lower than -50%, or negative.
• Rare/Serious cause: Cash flow ranges missing a negative outflow, or non-conventional cash flows with multiple sign switches causing polynomial oscillation.

Low Risk vs. High Risk

• If the error occurs in a simple, single-investment tracking sheet: It is Low Risk. Adding an explicit [guess] argument (such as 0.2 or 0.1) usually gives the algorithm enough traction to resolve the rate immediately.
• If the error occurs in multi-tier private equity waterfalls or real estate development models: It is High Risk. A failing IRR calculation can stem from non-conventional cash flows (e.g., intermediate construction reinvestments or environmental remediation costs) where multiple mathematical internal rates of return exist, making a single IRR output misleading.

The Mechanics of the Break

The Internal Rate of Return is defined mathematically as the discount rate r that forces the Net Present Value (NPV) of a series of cash flows to zero:

NPV=t=0∑n(1+r)tCt=0

Where Ct represents the cash flow at period t, and n is the total number of periods.

Because r appears in the denominator raised to power t, there is no simple algebraic equation to isolate r for multi-period cash flows. Excel must solve for r using an iterative numerical approach called the Newton-Raphson method.

By default, if you leave the [guess] argument blank in =IRR(values), Excel sets r=0.1 (10%). It evaluates NPV at 10%, calculates the mathematical slope of the cash flow curve, adjusts r, and re-evaluates. Excel repeats this process up to 20 times. If it cannot find a discount rate that yields an NPV accurate to within 0.00001% (0.0000001) within those 20 cycles, the calculation engine halts and returns #NUM!.

Think of Excel’s IRR algorithm as an automated artillery targeting system attempting to hit a target hidden behind a hill. The system fires a test shell using a standard 10-degree barrel angle (guess = 0.1). If the target is located at a 75-degree angle or a negative slope, the automated adjustments will over-shoot or under-shoot wildly. After 20 blind trial shots without a direct hit, the turret safety locks engage and display a targeting system fault (#NUM!). Providing a manual guess manually re-aims the barrel closer to the target before firing.

Cash Flow Profile	Default Guess (10%) Result	System Action	Solution
Initial -$100k, then +$30k/yr for 5 yrs	Converges to ~15.2%	Solves in ~4 iterations	Standard formula works
Initial -$100k, then +$500k in Year 1	#NUM!	Fails after 20 iterations (True IRR = 400%)	Add guess: =IRR(range, 3.0)
Initial -$100k, then +$10k/yr for 3 yrs	#NUM!	Fails (True IRR is deeply negative)	Add guess: =IRR(range, -0.3)
All positive values ($100k, $20k, $30k)	#NUM!	Fails instantly (No mathematical solution)	Add initial negative cash flow

Probability Breakdown

• Likely (60%): The true rate of return lies far outside the default 10% starting point (e.g., venture capital returns above 100% or distressed asset losses below -30%).
• Possible (30%): The cash flow series lacks a negative value (no initial capital outflow entered) or consists entirely of negative entries.
• Rare (10%): Cash flows switch signs three or more times across the timeline, creating multiple mathematical roots where the algorithm oscillates infinitely between two potential rates.

What Escalates the Risk

Complex capital schedules with intermediate funding draws escalate this failure. In real estate development or infrastructure projects, initial capital outlay is followed by intermediate operating cash flows, then secondary capital calls for renovation, and finally asset sale proceeds.

Every time cash flow flips from positive to negative, the mathematical polynomial changes direction. This can create multiple valid return rates or cause the default solver to swing wildly between extreme estimates until it hits the 20-iteration limit.

Consequence Timeline

• 24 Hours: Investment summary cards and hurdle rate checks output #NUM!, halting underwriting reviews.
• 1 Week: Secondary formulas like equity waterfall allocations and partner catch-up provisions fail, corrupting distribution calculations.
• 1 Month: Incomplete financial models lead to inaccurate capital allocations or missed investment deadlines due to unreliable yield reporting.

Common Confusion Fix

Distinguish #NUM! in IRR from other financial formula breaks:

• #NUM! vs. #VALUE!: #NUM! means the inputs are valid numbers, but the numerical algorithm failed to converge or encountered an invalid mathematical state. #VALUE! means a cell in the range contains non-numeric text or invisible space characters.
• #NUM! in IRR vs. #NUM! in XIRR: IRR assumes equal periodic time intervals (e.g., monthly or annual). XIRR accepts exact dates. When XIRR returns #NUM!, it often points to unsorted dates or invalid date formats rather than iteration limits.
• IRR vs. NPV Logic: IRR solves for the exact rate where NPV=0. If you need to test cash flows against an explicit discount rate without algorithm failure, use NPV.

What To Do Right Now

1. Supply an Explicit Guess Parameter

If your investment has high expected growth or significant losses, supply a manual guess argument:

• For high returns: =IRR(A1:A10, 0.8) (80% guess)
• For negative returns: =IRR(A1:A10, -0.3) (-30% guess)

2. Verify Cash Flow Direction Signs

Audit your cash flow range to ensure at least one number is negative and at least one is positive:

=AND(MIN(A1:A10) < 0, MAX(A1:A10) > 0)

If this test returns FALSE, IRR is mathematically impossible. Ensure initial investments are entered as negative numbers (e.g., -$100,000).

3. Handle Sporadic Dates with XIRR

If your cash flows occur at irregular calendar intervals rather than strict annual/monthly periods, switch to XIRR:

=XIRR(values_range, dates_range, [guess])

4. Switch to MIRR for Non-Conventional Cash Flows

If cash flows change signs multiple times (e.g., negative, positive, negative, positive), use the Modified Internal Rate of Return (MIRR). MIRR resolves iteration failures by incorporating explicit finance and reinvestment rates:

=MIRR(A1:A10, finance_rate, reinvest_rate)

Hard-Stop Triggers

Stop entering data and review model logic if:

• The cash flow range contains no negative values.
• Cash flows alternate signs more than twice, creating multiple internal rate possibilities.
• Varying the [guess] argument from 0.9 to 5.0 repeatedly returns #NUM!.

Professional Audit Path

When auditing a financial model returning #NUM! in return calculations:

1. Verify Sign Changes: Count the total number of sign variations in the cash flow series. If sign changes >1, evaluate whether MIRR or XIRR provides a more stable metric.
2. Test Boundary Guesses: Incrementally adjust the [guess] argument in steps of 0.2 (0.4, 0.2, 0.0, 0.2, 0.4, 0.8, 1.5) to check if the solver converges.
3. Audit Dates and Intervals: Ensure periodic cash flows are evenly spaced. If cash flow dates are uneven, convert the calculation to XIRR.

Complexity & Repair Range

• Minor (Guess Parameter Adjustment): 2 minutes. Adding an explicit [guess] value to the formula.
• Moderate (Cash Flow Restructuring): 15–30 minutes. Correcting cell sign conventions, fixing non-numeric entries, or converting to XIRR.
• Major (Waterfall & MIRR Re-architecture): 1–2 hours. Re-building non-conventional cash flow schedules using MIRR or Power Query transformation pipelines to handle complex capital calls.

Symptom Escalators

If financial or iterative formulas continue to break across your workbook, reference these related troubleshooting guides:

• If financial rate calculations exceed iteration limits, see #NUM! in RATE: Iteration limits exceeded.
• To adjust Excel’s global calculation limits and iteration settings, see #NUM! in Mathematical Iterations: Fixing Excel’s Calculation Limits.
• For handling multiple internal rates of return in complex cash flows, see #NUM! in IRR: Handling multiple internal rates of return in non-conventional cash flows.
• For resolving date-based iteration breaks in irregular investment streams, see #NUM! in XIRR: Solving the “Result not found” error for sporadic investment dates.
• To fix net present value timing logic errors, see NPV Function Trap: Why the NPV function in Excel is logically “wrong” (Period 0 vs Period 1).
• For construction and multi-phase draw calculations, see Internal Rate of Return (IRR): Handling #NUM! in multi-phase construction draws.

Final Calculation

The #NUM! error in IRR occurs because Excel’s iterative algorithm gives up after 20 unsuccessful trial cycles starting from a 10% default rate. Providing a realistic [guess] argument gives the algorithm an accurate starting trajectory to converge on a solution. For complex cash flow schedules with multiple sign flips or irregular payment dates, transitioning to MIRR or XIRR eliminates solver failures and ensures long-term financial model stability.