Is XIRR the right function for your personal investment return?

For most personal investors, most of the time: yes. If you want your own return — the one that accounts for when and how much you invested — XIRR is the cleanest tool in a spreadsheet, and you do not need anything fancier. But it has four sharp edges, and one of them is sharp enough that it will hand you a confident, absurd number without flagging anything wrong. Knowing the edges is the difference between a figure you can trust and one that merely looks plausible.

This is the practical companion to our cornerstone on how investment return is actually calculated. The cornerstone explains the two-questions framing — the investment's return versus your own. This piece is about the function that answers the second question, and exactly when it answers it well.

What XIRR actually computes

XIRR returns the internal rate of return on a series of dated cash flows: the single rate that makes the present value of everything you put in, everything you took out, and your closing balance net to zero. The "X" is for "extended" — it handles cash flows on irregular dates, which is what real investing looks like. (Plain IRR assumes evenly spaced periods; you almost never want it for a personal portfolio.)

Two facts about the output matter and are often missed. First, XIRR is money-weighted: it weights performance by how much money was invested at each time, so it captures your timing. That is the whole point — it answers "how did I do?", not "how did the investment do?". Second, XIRR always returns an annualised rate, on an actual/365 day-count basis.[1] A three-month holding does not give you a three-month figure; it gives you the annual rate that holding implies. That surprises people who expect the raw period return.

The sign convention — the edge that draws blood

This is the one that matters, because it fails silently. XIRR needs the direction of each cash flow encoded as a sign:

• Money you pay into the portfolio is negative — it left your pocket.

• Money that comes back to you is positive — withdrawals, and the closing balance as a final inflow.

Get this right and XIRR works. Get a sign wrong — enter a contribution as positive, say — and the function does not error. It iterates, finds a rate that satisfies the equation, and returns it with full confidence. The number is real arithmetic; it is just the answer to a cash-flow series you did not mean. The series must contain at least one negative and at least one positive value or the function fails outright[1] — but a partially mis-signed series usually still solves, to something absurd.

The fix is a discipline, not a formula: lay your flows out as a signed column, contributions negative, withdrawals and the closing value positive, and sanity-check the result against rough intuition before you trust it. A portfolio that grew should not return −90%.

A worked example you can reproduce

Here is the signed cash-flow column for the realistic rand portfolio from our cornerstone — twelve months, monthly contributions, a R200,000 bonus in August, an R80,000 withdrawal in November:

With the amounts in one column and the dates in the next, =XIRR(values, dates) returns 25.8966%.[2] That is the investor's money-weighted return — higher than the same portfolio's time-weighted return of 19.43%, because most of the capital was working during the strong second half. Note what is absent from the column: the August rebalance and any reinvested dividends. They are internal moves, not cash flows, and entering them would corrupt the result.

The other three edges

The sign trap is the common one. Three more matter when your flows get complicated:

Multiple answers. By Descartes' rule of signs, a cash-flow stream can have as many positive roots as it has sign changes. A simple series — money in, money in, money out at the end — changes sign once and has one clean IRR. But a stream that changes direction repeatedly (large withdrawals interleaved with contributions) can admit several mathematically valid IRRs, and XIRR will return just one of them, with no warning that others exist.[3] If your portfolio has heavy two-way flows, treat a lone XIRR figure with suspicion.

It can fail to converge. XIRR is an iterative solver: it starts from a guess (10% by default) and refines until it is close enough, giving up after 100 tries with a #NUM! error.[1] Volatile or sign-changing flows, or a bad starting guess, can make it fail. If you get #NUM!, supplying a sensible guess argument — say 0.1 or 0.2 — often rescues it.

The reinvestment assumption. IRR implicitly assumes interim cash you took out is reinvested at the IRR itself.[4] For a personal portfolio this is usually a footnote rather than a problem, but it is why two investors with identical-looking flows can have results that do not behave the way simple intuition expects.

When to reach for time-weighted return instead

XIRR is the right tool when the question is "how did I do, given my timing?" and you control the flows. It is the wrong tool in three cases:

• When you are judging the fund or manager, not yourself — their skill should not be flattered or punished by your deposit timing. Use time-weighted return, which you can build in a spreadsheet.

• When your flow stream changes sign repeatedly — multiple-IRR risk makes a single XIRR figure ambiguous.

• When early invested capital is near zero — the rate becomes unstable and can read as extreme.

The point

XIRR is the right function for your personal return far more often than not — it is money-weighted, it handles irregular dates, and it answers the question most individual investors actually care about. The danger is not the function; it is the inputs. A mis-signed flow produces a confident, wrong answer with nothing to flag it, and a repeatedly sign-changing stream can hide multiple valid answers behind the one it shows you. Lay the flows out signed, exclude internal moves, sanity-check the output, and reach for time-weighted return when the question is about the investment rather than about you.

If you would rather not hand-build the column, our calculator runs XIRR and the time-weighted return on the same cash flows (link to our mwr-vs-twr calculator ), enforces the sign convention for you, and shows you both numbers side by side.