Calculating XIRR by hand vs in Excel
Why XIRR needs an iterative solver, and how spreadsheet software handles it.
CAGR has a closed-form formula — plug in a beginning value, an ending value, and a number of years, and algebra hands you the answer directly. XIRR doesn’t work that way once there are more than two irregular cash flows: there’s no rearrangement of the equation that isolates the rate on one side. Instead, it has to be found by guessing a rate, checking how wrong the guess is, and refining it — the same approach both this site’s calculator and Excel’s XIRR() function actually use under the hood.
Why there’s no plug-and-chug formula
XIRR is defined as the rate that makes the net present value of every cash flow — each one discounted back by its own number of days from a chosen start date — sum to exactly zero. With only two cash flows (one outflow, one inflow), that equation can be rearranged and solved directly. With three or more irregular cash flows — any real SIP, any multi-purchase portfolio — the rate appears raised to a different fractional power in every term, and there’s no algebraic way to isolate it. The only way in is to search for it numerically.
The iteration, in action
₹1,00,000 invested, ₹1,10,000 received back 200 days later. With just two cash flows this case has a direct algebraic answer to check against: 18.9985%. Running the exact Newton-Raphson method computeXirruses, starting from the same 10% initial guess Excel defaults to: iteration 1 lands at 18.4657%, iteration 2 at 18.9967%, iteration 3 at 18.9985% — matching the closed-form answer to four decimal places in three refinements. Each step uses how wrong the previous guess’s NPV was to compute a better one, rather than guessing blindly.
Work out the annualised return across a series of dated cash flows.
That convergence in three steps isn’t a coincidence of this particular example — Newton-Raphson typically closes in fast when the starting guess is reasonably close, which 10% usually is for realistic investment returns. The same mechanism handles any number of irregular cash flows; a two-flow case is just the only one simple enough to verify against an independent algebraic answer.
How Excel does the same thing
Excel’s =XIRR(values, dates, [guess])function runs the identical Newton-Raphson search internally, defaulting to the same 10% starting guess unless you override it with the optional third argument. If the iteration fails to converge — which can happen with certain cash-flow patterns, like flows that don’t change sign or amounts that produce no realistic solution — Excel returns a #NUM! error rather than a wrong number, the same failure mode computeXirr falls back to a bisection search to recover from before giving up.
In practice, this means you never need to run the iterations by hand — both Excel and any proper XIRR calculator do it in milliseconds. What’s worth understanding is why the number sometimes looks like it “solved itself” rather than coming from a formula you could rearrange on paper, and why an unusual cash-flow pattern (all inflows, or all outflows, with nothing to balance against) is the one situation where even a correct solver has nothing to converge toward.
All figures are indicative and for educational purposes only — not financial advice.
Related reading
More articles worth reading next.