XIRR Calculator Guide: How to Work Out Your Real Return When You Add and Withdraw Money

XIRR is the yearly return that makes all your dated deposits, withdrawals and today's value balance out. It is the right number when you put money in and take money out at different times, because it gives each amount credit only for the time it was actually invested. In Excel or Google Sheets you get it with =XIRR(values, dates), where money you put in is negative and money you get back (including today's value) is positive.

This guide walks through a full worked example with real dates, shows the spreadsheet formula, explains how XIRR differs from CAGR and time-weighted return (TWR), lists the mistakes that give wrong answers, and answers the questions people ask most.

Why a simple percentage gain is not enough

A simple gain is (what you have now - what you put in) / what you put in. It works for one deposit and no withdrawals. Once money moves in and out, it breaks in two ways:

  • It ignores time. A 20% gain over 6 months and a 20% gain over 6 years look the same.
  • It ignores when each amount went in. Money added last month has barely had time to grow. Treating it like money invested three years ago drags your return down, or flatters it if you withdrew money along the way.

XIRR fixes both. It is a money-weighted return: every amount counts for exactly the days it was invested.

The XIRR formula in plain words

XIRR finds the rate r that makes this sum equal zero:

sum of: amount / (1 + r) ^ (days since the first cash flow / 365)

Money you put in is negative. Money you take out, and the value you hold today, is positive. There is no neat formula for r, so spreadsheets and apps try rates until the sum lands on zero. That is why you never need to solve it by hand.

Worked example: four cash flows over three years

Say you hold one investment account. The amounts are in dollars, but the method is the same in pounds, rupees, dirhams or Singapore dollars.

DateWhat happenedCash flow
15 Jan 2022First investment-10,000
1 Jul 2022Added more-5,000
10 Mar 2023Withdrew some+3,000
20 Sep 2023Added more-4,000
31 Dec 2024Value of the account today+20,500

The simple view: you put in 19,000 and got back 3,000 plus 20,500 = 23,500. That is a gain of 4,500, or 23.7%. But that 23.7% was earned over almost three years, and most of the money was not invested for the whole time.

The XIRR: 9.87% a year.

You can check it. Discount each amount back to 15 Jan 2022 at 9.87% a year:

DateDays from startCash flowValue at the start date
15 Jan 20220-10,000-10,000.00
1 Jul 2022167-5,000-4,789.19
10 Mar 2023419+3,000+2,692.67
20 Sep 2023613-4,000-3,414.98
31 Dec 20241,081+20,500+15,511.50
Total0.00

The discounted amounts add up to zero, which is exactly what XIRR means. At 10% the total would be about -47.5, so the answer is just under 10%.

How to calculate XIRR in Excel or Google Sheets

  1. Put the dates in column A and the cash flows in column B, one row each, as in the first table above.
  2. Enter money you put in as negative numbers and money you took out as positive.
  3. Add a last row with today's date and today's value as a positive number.
  4. In an empty cell type =XIRR(B2:B6, A2:A6) and format it as a percentage.

Both Excel and Google Sheets use the same function name and the same order: values first, then dates. Excel counts a year as 365 days in XIRR, which is the convention used in the check above.

If you get #NUM!, check that you have at least one negative and one positive amount, and that the dates are real dates, not text.

XIRR vs CAGR vs TWR

These three answer different questions. Use the one that matches yours.

CAGR: one deposit, one end value

CAGR (compound annual growth rate) is the yearly rate that turns a starting amount into an ending amount. Put in 10,000, have 15,000 five years later, and the CAGR is (15,000 / 10,000) ^ (1/5) - 1 = 8.45% a year. With a single deposit and no withdrawals, XIRR gives the same answer. Once you add or remove money, CAGR no longer applies.

TWR: how good was the investment?

Time-weighted return measures the investment itself and removes the effect of when you added money. Fund managers report it because they do not control when clients deposit. You link the return of each period together, ignoring the size of the account.

XIRR: how did you do?

XIRR includes your timing. If you added a lot of money just before a fall, XIRR shows the damage. That makes it the right number for "what did my money actually earn?"

Example: same fund, two investors

A fund rises 20% in its first year and falls 10% in its second.

  • TWR for the fund: 1.20 x 0.90 - 1 = 8% over two years, or about 3.92% a year. This is the same for everyone.
  • Investor A puts 10,000 in on day one. After two years it is 10,000 x 1.20 x 0.90 = 10,800. XIRR: about 3.92% a year, the same as the fund.
  • Investor B puts 1,000 in on day one, then adds 9,000 after the good year. After two years: (1,200 + 9,000) x 0.90 = 9,180 from 10,000 put in. XIRR: about -7.5% a year.

Same fund, same TWR, very different results. TWR tells you the fund was fine. XIRR tells Investor B the truth about their own money.

Short periods: be careful with yearly rates

XIRR is a yearly rate. Over a few weeks or months, turning a small gain into a yearly figure produces numbers nobody actually earns. A 5% gain over 91 days works out to about 21.6% "a year". It would only be real if the same thing happened four times in a row.

For anything held under a year, look at the plain gain in money and percent first. Treat a yearly rate over a short period as a rough signal, not a result.

Common mistakes

  • Wrong signs. Money in is negative, money out is positive. Flip them and you get nonsense or an error.
  • Leaving out today's value. Without a final positive amount, there is nothing to measure against.
  • Leaving out dividends you took as cash, or fees. A cash dividend is money back to you, so it is a positive flow. Fees you paid on top of a purchase are part of what you put in.
  • Counting moves between your own accounts as new money. If you move money from a savings account into a fund, and you track both, the overall portfolio did not gain new money. Count it per account, not twice in the total.
  • Inventing dates. If you only know a balance from a statement and not when you bought, any date you guess changes the answer. A guessed date three years too late can make an ordinary return look huge.
  • Averaging XIRRs across holdings. Portfolio XIRR is not the average of each holding's XIRR. You have to put all the cash flows together and solve once.

How Agni Folio shows XIRR

Agni Folio works out XIRR from the transactions you record. Here is how it behaves today:

  • Per holding: each holding gets its own XIRR from its dated buys, sells, deposits, withdrawals, income and today's value. Fees are counted as part of what you paid.
  • No guessing. If a holding only has a balance from a statement and no real purchase date, its XIRR stays blank rather than being estimated. If all of a holding's transactions are on the same day, XIRR is also blank, because no time has passed.
  • Whole portfolio: the portfolio figure is one XIRR solved over the pooled cash flows of every holding, not an average. Loans are left out. Holdings with no dated cash flows are left out too, and when the figure covers less than half of what you invested, the dashboard says "based on X% of portfolio".
  • Absolute return alongside. The plain gain is shown next to XIRR, so for short holding periods you can read the money figure instead of the yearly rate.

To get a true XIRR, record real transactions with real dates. For Indian mutual funds, importing a detailed CAMS or KFintech CAS statement brings in every dated purchase. The transactions docs explain each transaction type, and the portfolio insights docs show where the figures appear.

If you also want to measure a managed account against its reported numbers, see our guide to tracking PMS portfolio performance, and for the bigger picture, how to track your net worth.

Frequently asked questions

What is a good XIRR?

There is no single number. Compare your XIRR with what you could have earned elsewhere over the same dates, such as a broad index fund or a bank deposit in your own currency. A 7% XIRR is strong where deposits pay 3% and weak where they pay 7%.

Is XIRR the same as IRR?

They solve the same equation. IRR assumes cash flows come at equal intervals, such as once a year. XIRR uses the actual date of each cash flow, which is what real investing looks like.

Why is my XIRR negative when I have a profit?

It should not be if the inputs are right. Check the signs, check that today's value is included, and check that no date is wrong. A negative XIRR with a positive total gain usually means a flow has the wrong sign or date.

How do I calculate XIRR for a SIP or monthly investment?

Enter each monthly instalment as a negative amount on its own date, then add today's value as a positive amount on today's date. =XIRR() handles any number of rows.

Should I use XIRR or CAGR?

Use CAGR for a single lump sum held with no additions or withdrawals. Use XIRR whenever money went in or came out more than once.

Why does my XIRR look very high for a new investment?

Because a short gain is being turned into a yearly rate. Over a few months, look at the plain percentage gain instead.

Related Articles