What is XIRR in mutual funds? How to calculate it, and XIRR vs CAGR

XIRR is the one yearly rate that matches all the money you put in, on its dates, with what the investment is worth today. It is your own return. A fund's CAGR is a different number: what a single amount would have done between two dates.

CAGR is the average yearly growth rate over a period, as if the fund had grown at the same pace every year. This page covers XIRR, the Excel steps, and why your SIP's XIRR can come out negative in a year when the fund itself rose.

What is XIRR in mutual funds?

XIRR is the yearly rate of return on your own money when the amounts went in, or came out, on different dates. Think of a SIP, a systematic investment plan, where you invest a fixed amount every month. Each instalment buys units at that day's NAV, the price of one unit, and each one stays invested for a different length of time.

The first instalment has worked for twelve months by the time the last one has worked for one. A plain gain figure cannot tell them apart. XIRR can, because it uses the date of every amount.

The same goes for anything else you do with a fund:

• A lump-sum purchase added to a SIP in the same scheme is one more dated row, bought at its own day's NAV.
• A skipped instalment is simply a missing row.
• A partial withdrawal is a row too, with the amount on the date it reached you.

Where do the dates and amounts come from? Your consolidated account statement. It covers every month in which you had a transaction, SIP instalments included, and is issued by the 15th of the next month. It shows units, NAV, market value and cost. It does not show a return figure, so you work that out, as in the steps below. For what the statement contains and how to get one any time, read what a mutual fund statement (CAS) is. The Koshex app shows a customer's holdings and their account statement.

If you want the basics of the plan itself first, start with what a SIP is.

How do you calculate XIRR in Excel?

In Excel or Google Sheets, list each instalment as a negative amount with its date. Add today's value as a positive amount with today's date. Then use =XIRR(amounts, dates). The answer is a yearly rate.

The steps:

1. Put the dates in one column and the amounts in the next.
2. Enter every amount you paid in as a negative number, on the date it was invested.
3. On the last row, enter what the units are worth today as a positive number, with today's date.
4. In an empty cell, type =XIRR( then select the amounts, a comma, and select the dates. Close the bracket.

Microsoft's help page for Excel says that if the first value is a cost or payment, it must be negative. It also says the values need at least one positive and one negative amount. Google's help page for Sheets also says the amounts must contain at least one negative and one positive cash flow. Microsoft's page says the dates may be in any order.

Here is a hypothetical SIP of ₹4,000 on the 5th of each month, for twelve months. The NAVs are made up for the example, and the small stamp duty deducted from each purchase is ignored.

Units bought are the amount divided by that day's NAV. The 2,200.743 units, at ₹21.60 on 5 Apr 2025, are worth ₹47,536.05. Put that in the last row and the formula gives about -1.78% a year.

What is the function doing? Microsoft's help page describes it as returning the internal rate of return for a schedule of cash flows that is not necessarily periodic. In plain words, it looks for the one yearly rate at which all your dated amounts balance out against each other. Each amount is scaled down by (1 + rate) for every 365 days after the first date. That is why the result is always a yearly rate, whether the money was in for a year or for five.

What if you have sold some units?

Use the amount that actually reached your bank as a positive figure on that date. If an exit load applied, that is the amount after it. An exit load is a fee some funds charge if you sell within a set time after buying. For how the load works, see exit load in mutual funds. Tax on the gain also reduces what you keep, and how SIP returns are taxed explains it instalment by instalment.

XIRR vs CAGR: what is the difference?

CAGR describes one amount over one period, and XIRR describes your own series of dated amounts. They are not the same measure, and they answer different questions.

CAGR needs a start value, an end value and the time between them. It suits a fund's own figure. On AMFI's website, SEBI's rules require a scheme's returns to be shown as CAGR for 1, 3, 5 and 10 years and since inception. Performance adverts must use CAGR for at least 1, 3 and 5 years and since inception.

XIRR needs every dated amount. It describes you, not the fund.

When there is only one payment, the two agree. Take ₹1,00,000 invested on 1 April 2021 and worth ₹1,50,000 on 1 April 2025. XIRR comes to 10.66% and CAGR to 10.67%. The tiny gap is a day-count effect: that stretch includes a leap day, so it is 1,461 days, or 4.0027 years of 365 days.

The numbers drift apart only when money moves in several steps, which is the case with every SIP. For trailing returns and rolling returns, see absolute return vs CAGR.

Why is your XIRR on a SIP different from the fund's return?

Because the fund's figure follows one price from the start to the end, while your figure follows how much money was invested at each price. In our example the fund itself rose 8% over the year. The SIP shows a loss of about 1.8% a year, because most of the money went in at prices above the starting ₹20.00, and the average price paid ended up above the final ₹21.60.

Look at the table again. The NAV went from ₹20.00 on 5 Apr 2024 to ₹21.60 on 5 Apr 2025. That is +8.00%, the fund's one-year point-to-point return. But only the first instalment bought at ₹20.00. Six instalments bought at prices above ₹21.60, and one of them at ₹23.50. Divide the ₹48,000 you paid by the 2,200.743 units you got, and your average price was about ₹21.81. The units are worth ₹21.60 each, so you are slightly behind.

So the units are worth ₹47,536.05 against ₹48,000 paid:

• Absolute return, the simple gain or loss on what you paid, with no regard to time: -0.97%.
• XIRR, which also counts when each amount went in: -1.78% a year.

Neither is wrong. XIRR measures your money; the fund's return measures the fund.

This example uses made-up NAVs. It is not a real fund and it says nothing about what a SIP will do. A different path of prices would give a different answer, in either direction.

One more place the two get confused. The SIP figure on a fund page applies the fund's yearly return to each instalment. Your own return, from your actual dates and prices, is your XIRR. A calculator such as the SIP calculator projects with an assumed rate. XIRR measures what already happened. For how that assumed rate works, see SIP interest rate.

What mistakes give a wrong XIRR?

Most wrong answers come from the inputs, not the formula. These are the usual ones:

• Leaving out today's value. With only payments in, every amount is negative and Excel returns #NUM!. The sheet needs one positive amount.
• Wrong signs. Money you put in is negative. Today's value, or money you took out, is positive.
• Wrong or missing dates. Use the date the units were bought.
• Using the wrong amount for a sale. Use what reached your bank, after any exit load.
• Reading a few weeks of data. Over a short stretch, a small gain becomes a huge yearly figure.

The short-period trap deserves numbers. Put in ₹5,000 on 6 Jan 2025 and see ₹5,150 on 26 Jan 2025, a 3% gain in 20 days. XIRR works out to about 72% a year, 71.51% to be exact. If the value were ₹4,900 instead, it would show -30.84%. Neither says anything about the next year. Wait for at least a year of data before reading much into it.

Microsoft's help page also lists when Excel gives errors. You get #NUM! if there is no negative amount or no positive one. You get it if any date comes before the first date, or if the number of amounts and dates differ. You get #VALUE! if a date is not a valid date. If you leave out the optional third value, a guess at the rate, Excel assumes 0.1, or 10%. It tries up to 100 times to find a rate before giving up with #NUM!.

As AMFI puts it, past performance may or may not be sustained in future, and no XIRR changes that. Koshex reviews your holdings over time and flags changes. To see the funds on Koshex, go to mutual funds on Koshex.