#NUM! in XIRR: Solving the “Result not found” error for sporadic investment dates.

An Excel =XIRR formula outputs a #NUM! error when its calculation engine cannot compute an annualized internal rate of return after 100 iterations. Unlike the standard IRR function—which assumes uniform, discrete periods—XIRR calculates returns against fractional-year day counts using actual calendar dates. When cash flows are highly sporadic, separated by long idle gaps, or concentrated in extremely short time horizons, the underlying algorithm frequently diverges, generating a #NUM! error.

Quick Answer

Fix a #NUM! error in =XIRR by manually supplying a realistic seed rate in the third argument: =XIRR(Values, Dates, 0.15). Next, ensure your date range is sorted in strict chronological order, that your opening investment is negative, and that values contain at least one positive and one negative number.

---

Root Causes of Calculation Failures in XIRR

The XIRR engine uses a bisection and Newton-Raphson method to solve the equation:

$\sum_{i=1}^{n} \frac{P_i}{(1 + \text{rate})^{\frac{d_i – d_0}{365}}} = 0$

Where $P_i$ is the cash flow and $d_i$ is the date. This math is significantly more complex than standard periodic IRR. When dates are unevenly spaced, several structural issues cause the formula to blow past its calculation limits:

Cause	What Happens in the Background	Primary Diagnostic Indicator
Default Guess Divergence	The default guess of 10% (0.1) sends the initial tangent calculation in the wrong direction on steep yield curves.	#NUM! appears even though the data contains valid cash flows.
Chronological Out-of-Order Rows	The formula assumes the first row is $d_0$. An out-of-order date corrupts the exponent calculations.	Formula works on some data chunks, fails when filtered or unsorted.
Short-Horizon Annualization Trap	A high return over a short span (e.g., $1,000 to $1,500 over 12 days) yields an annualized rate exceeding millions of percent, exceeding Excel calculation thresholds.	Highly profitable trades held for less than 30 days.
Multi-Year Capital Inactivity	A 5-to-10 year void between an initial private equity commitment and a subsequent capital draw flattens the derivative of the curve to near zero.	Newton-Raphson divides by a value near zero and stalls.

---

Progressive Troubleshooting Procedure

Work through these diagnostic steps in order to identify and fix the failure point.

Step 1: Check Data Integrity (Negatives, Positives, Date Types)
   └── Valid? ──> Step 2: Sort Data Chronologically by Date
                     └── Still #NUM!? ──> Step 3: Seed the [guess] Argument
                                             └── Still #NUM!? ──> Step 4: Check for Annualization Distortion

Step 1: Validate Basic Array Construction

Before testing formula settings, verify your inputs satisfy Excel’s hard requirements:

1. Sign Check: Confirm there is at least one negative cash flow (outflow) and at least one positive cash flow (inflow). If all numbers share the same sign, =XIRR cannot solve and displays #NUM!.
2. First Value Rule: The very first cash flow in your series must represent an initial investment or commitment date. It must be negative.
3. Data Types: Ensure your dates are true Excel serial numbers and not text strings. You can test a cell using =ISNUMBER(Date_Cell). If it returns FALSE, the date must be converted before XIRR will parse it.

Step 2: Sort Chronologically

While standard IRR relies strictly on cell order, XIRR requires your earliest date to sit at the head of the array. If transaction records are pasted out of order (e.g., sorted by dollar amount rather than transaction date), Excel frequently miscalculates the time intervals.

• Select your data range.
• Go to Data > Sort.
• Sort by your Date Column from Oldest to Newest.

Step 3: Seed the [guess] Argument

Excel defaults to an initial guess of 10% (0.1). If the actual annualized return is -40% or 300%, the algorithm may head down the wrong path and hit its 100-iteration ceiling before finding the root.

=XIRR(C4:C25, B4:B25, -0.30)

Test a range of deliberate seed values:

• For distressed investments or significant write-downs: Try 0.50 or 0.20.
• For high-multiple venture exits: Try 0.50, 1.0 (100%), or 2.0 (200%).

Typical Syntax:
=XIRR(CashFlow_Range, Date_Range, 0.25)

Step 4: Identify the “Short-Horizon” Distortion

A common source of #NUM! in treasury management and venture models is applying XIRR to short-term events.

For instance, paying an expense of $10,000 on March 1 and receiving a reimbursed recovery of $11,000 on March 5 represents a 10% absolute gain over 4 days. Annualized across a 365-day year, this produces a theoretical return in excess of $700,000,000\%$. The calculation exceeds the floating-point limits of the application and results in #NUM!.

If your model evaluates short holding windows:

• Calculate the Holding Period Return (HPR) instead:
  =(Total Inflows - Total Outflows) / Total Outflows
• Avoid using XIRR on any transaction set whose total lifespan spans fewer than 30 to 60 days.

---

Diagnosing “Ghost Gaps” in Syndicated Deals

In long-term private equity or infrastructure models, an investment might have an initial contribution in 2018, zero activity through 2024, and a payout in 2026. This multi-year gap creates an extremely flat present value curve where incremental changes in the discount rate produce virtually no change in the slope of the NPV line.

If manual guesses fail to resolve the #NUM! error in these structures:

1. Construct an =XNPV() sensitivity table.
2. Use discount rates between 1% and 50% in cell increments of 1%.
3. Check if the resulting present values ever cross zero. If the total un-discounted inflows are less than total outflows, the project lost money, and the true solution is a negative rate.

If your financial model uses iterative routines or debt-sizing macros to resolve these cash balances, double-check that circular logic is not compounding the problem. Review Circular References: How to fix “The formula refers to its own cell” in Debt Sculpting. to ensure baseline inputs remain stable during calculation.