#NUM! in IRR: Handling multiple internal rates of return in non-conventional cash flows.

The #NUM! error in an Excel =IRR formula means the calculation engine failed to find a valid rate of return within its standard 20 iterations. In financial modeling, this almost always occurs when working with non-conventional cash flows—streams where the cash sign flips between positive and negative more than once. Under Descartes’ Rule of Signs, every sign reversal introduces a potential mathematical rate of return. When these competing rates confuse Excel’s Newton-Raphson search algorithm, the calculation diverges and returns #NUM!.

Quick Answer

To clear the #NUM! error immediately, enter a seed value into the optional [guess] argument: =IRR(values, 0.1). If the cash flow has multiple sign reversals and #NUM! persists, replace IRR with =MIRR(values, finance_rate, reinvest_rate), which eliminates multiple rates by financing negative periods and reinvesting positive cash flows.

---

Why Non-Conventional Cash Flows Break the Standard IRR

A conventional cash flow has exactly one sign change: an upfront capital expenditure (negative) followed by a series of operating inflows (positive). This structure yields a single, mathematically unique internal rate of return where Net Present Value (NPV) equals zero.

Non-conventional cash flows flip signs multiple times. Common operational examples include:

• Natural resource extraction or infrastructure: Initial development costs (negative), operational profits (positive), and major environmental site remediation or decommissioning at closure (negative).
• Private equity and real estate syndications: Phased equity calls, interim dividend distributions, followed by secondary capital expenditures or debt pay-downs.
• Equipment overhauls: A mid-life refit or turbine replacement that turns an otherwise profitable asset cash-negative during year five.

When a series shifts from negative to positive and back to negative ($-/+/$-), the NPV curve does not cross the horizontal zero axis once. It can cross twice, touch the axis tangentially, or never cross it at all.

Net Present Value (NPV)
   ^
   |        * * * (Peak NPV)
   |      *       *
---+----*-----------*---------> Discount Rate (r)
 0 |   IRR 1       IRR 2
   |  (e.g. 8%)   (e.g. 42%)
   v

Because Excel’s native =IRR(values, [guess]) defaults to a 0.1 (10%) starting point and stops searching after 20 cycles, it often oscillates between these two inflection points without converging. When it fails to hit a solution within a tolerance of 0.00001%, it gives up and flags #NUM!.

---

Step-by-Step Diagnostic Path

Follow this sequence to diagnose whether your model is suffering from algorithmic divergence, multiple internal rates, or no mathematical solution.

Is there at least one negative and one positive value?
   ├── NO  --> Add required outflow/inflow. IRR is impossible.
   └── YES --> Does the cash flow change sign more than once?
                 ├── NO  --> Formula issue. See [#NUM! in IRR: Why the formula needs a "Guess"](<http://www.excelerrorfix.com/formula-errors/math-logic-type-errors/irr-num-error-guess-parameter>)
                 └── YES --> Try seeded [guess] arguments (-50% to +100%).
                               ├── Resolves to a rate? Check if rate is economically realistic.
                               └── Still #NUM! or rate is erratic? Switch to MIRR or build an NPV profile.

Step 1: Supply Targeted Seed Guesses

If you do not specify a guess, Excel assumes 10% (0.10). If the two mathematical solutions sit at -15% and 85%, a 10% start may bounce erratically between slopes.

Explicitly provide alternate starting points in the formula:

• Test a conservative rate: =IRR(C4:C12, -0.20)
• Test a moderate rate: =IRR(C4:C12, 0.15)
• Test an aggressive rate: =IRR(C4:C12, 0.60)

If entering -0.10 returns -8%, and entering 0.50 returns 54%, your project has two mathematically valid IRRs. Both solve the polynomial equation $\text{NPV} = 0$, but neither tells you the true economic viability of the project on its own.

Step 2: Plot an NPV Profile Table

When multiple rates exist, you must map the project’s discount curve to verify where it intersects zero:

1. Create a column of discount rates running from 50% to 100% in 5% increments.
2. In the adjacent column, calculate the NPV for each rate using Excel’s native formula:
   =C4 + NPV(Rate_Cell, C5:C12)(Note: Remember to leave Period 0 outside the =NPV() function to prevent discounting initial outlays. See NPV Function Trap: Why the NPV function in Excel is logically “wrong” (Period 0 vs Period 1)..)
3. Inspect where the values switch between positive and negative. If the values never cross zero, no real IRR exists, and =IRR will return #NUM! regardless of your guess.

---

The Permanent Fix: Transitioning to MIRR

When non-conventional cash flows produce multiple rates or unresolvable #NUM! errors, IRR is the wrong financial metric. Standard IRR assumes that all intermediate positive cash flows are reinvested at the internal rate itself—an assumption that completely falls apart when rates swing wildly.

The Modified Internal Rate of Return (MIRR) resolves this by separating borrowing costs from reinvestment yields:

• Finance Rate: The cost of capital used to fund negative periods.
• Reinvestment Rate: The realistic rate earned when cash distributions are put back to work.

=MIRR(C4:C12, Cost_of_Capital, Reinvestment_Rate)

Example Setup:
- Period 0: -$1,000,000 (Initial build)
- Period 1:  $1,500,000 (Operations)
- Period 2:   -$700,000 (Remediation / Tax adjustment)

Formula:
=MIRR(C4:C6, 0.08, 0.10)

MIRR handles negative interim balances by discounting them back to Period 0 at your finance rate, and compounds positive balances forward to the terminal period at your reinvestment rate. Because this reduces the stream to a single net outflow at the start and a single net inflow at the end, it is mathematically guaranteed to yield exactly one unique solution, eliminating #NUM! errors permanently.

---

When to Escalate to XIRR

If your irregular outlays and receipts do not occur on strict, identical annual or monthly cycles, recurring sign flips will compound calculation errors. Irregularly dated milestones require =XIRR(), though it introduces its own sensitivity to iteration limits. If your dates skip across multiple months or erratic fiscal closes, continue troubleshooting via #NUM! in XIRR: Solving the “Result not found” error for sporadic investment dates..