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. That’s 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.