How to Calculate XIRR in Excel and Google Sheets

The XIRR() function syntax

Both spreadsheets use the same syntax: XIRR(values, dates, guess). The third argument, guess, is optional in both and rarely needed.

• values: a range of cells containing your cash flow amounts, negative for money invested, positive for money received back.
• dates: a range of cells containing the matching date for each amount, in any order.
• guess (optional): a starting estimate for the rate, as a decimal, for example 0.1 for 10 percent. Leave it out unless the function returns an error.

Worked example: type this in and check your own result

Set up three cash flows in two columns, amount and date, exactly as below.

In an empty cell, type =XIRR(A1:A3,B1:B3) and format the result as a percentage. This should return approximately 15.83 percent. If you get exactly this figure, your formula and date formatting are both correct; you can cross-check the same three cash flows in the XIRR calculator's irregular cash flows mode, entering the two -100000 rows as Investment and the 250000 row as Withdrawal / value received.

Why the result might not be a whole, round number

XIRR's day-count convention is exact calendar days divided by 365, not a rounded number of years. Two cash flows exactly one calendar year apart, when that year includes 29 February, span 366 days rather than 365, which slightly changes the discounting compared to a non-leap year. This is expected behaviour, not a formula error, and both Excel and Google Sheets, and this site's calculator, use the identical convention, so results between them should always match exactly for the same inputs.

Fixing the #NUM! error

A #NUM! result from XIRR() almost always comes down to one of these:

• Every amount has the same sign. XIRR needs at least one negative (invested) and one positive (returned) value in the range; check that at least one row of each exists.
• The dates and amounts ranges are different sizes, for example A1:A3 paired with B1:B4. Both ranges must contain exactly the same number of cells.
• A date is stored as text rather than an actual date value. If a date cell is left-aligned by default in your spreadsheet, it is usually being read as text; reformat the column as a date and re-enter the values.
• The solver could not find a rate within its internal search range for an extreme or malformed input. Try adding a guess argument, for example =XIRR(A1:A3,B1:B3,0.1), or double-check the amounts for a typo such as a missing digit.

Adding a new transaction to a running log without breaking the formula

If you keep a running log of every SIP instalment, top-up and withdrawal in two columns, the amounts range and the dates range must always stay exactly the same length as each other, with no extra blank rows on either side. A range padded with blank rows for future transactions, for example =XIRR(A1:A200,B1:B200) when only 40 rows have data so far, is a common source of a #NUM! error: an uneven or blank-padded pair can derail the solver even though it looks harmless. Reselecting the two ranges each time a row is added, so both stop at the same last populated row, is the safest habit.

• Sort order never matters: both functions re-sort the pairs by date internally before solving, so new rows can simply be appended at the bottom of the log.
• A whole-column reference, for example =XIRR(A:A,B:B), does not avoid this problem: it still needs an exact row-for-row match, and any blank cell or stray text left in either column below the real data will produce the same error.

A shortcut if you would rather skip the spreadsheet

If you only need the answer and not a reusable spreadsheet, the XIRR calculator on this site's homepage solves the identical equation without opening Excel or Google Sheets at all: choose regular investments for a fixed SIP schedule, or irregular cash flows to enter transactions exactly as you would in a spreadsheet's two columns. See XIRR vs CAGR if you are trying to decide which metric your comparison actually needs, and the privacy policy for how your entered numbers are handled (they are not sent to a server, in either tool).