XIRR Mutual Fund Returns: What It Is and How to Calculate It

XIRR, or extended internal rate of return, is the yearly return on money invested on different dates. In an XIRR mutual fund calculation, each rupee counts only for the time it stayed invested, which makes it the right measure for a SIP or for extra investments made later.

Most investors meet two other numbers first: absolute return and CAGR. Both have a place, but each misleads when money goes in at different times. This article explains the difference, works through two examples, and shows how to calculate XIRR in a spreadsheet.

Absolute return ignores time

Absolute return is the gain divided by the amount invested. It answers how much the money grew, but never how long it took. A gain of the same size earned in six months and in six years produces the same absolute return, even though the yearly pace is very different.

CAGR suits a single lump sum

CAGR, or compound annual growth rate, converts a total gain into a steady yearly rate. It works well when money goes in once and comes out once, because the start value, end value and number of years are enough to calculate it:

CAGR = (end value ÷ start value)^(1 ÷ years) − 1

Published fund returns are usually CAGR. They assume one lump sum invested on the first day of the period. The formula has no place for money added later, so applied to a SIP it treats every instalment as if it arrived on day one.

XIRR suits SIPs and later investments

XIRR finds the single annual rate that makes the investments (negative) and the current value (positive) balance out, with each amount dated on the day it moved. An early rupee earns for longer and so carries more weight than a late one. That is why XIRR suits a SIP, where money goes in every month, and a lump sum with later investments.

Worked example: a lump sum and a later investment

Suppose ₹1,00,000 goes in on 1 January 2023, a further ₹50,000 goes in on 1 January 2024, and the holding is worth ₹1,90,000 on 1 January 2026. These are illustrative cash flows.

Investments are negative and the current value is positive. The total invested is ₹1,50,000.

Data note: Absolute return is for the whole period and is not annualised.

The absolute return is the largest number, but it describes three years without saying so. Simple CAGR is lower because it treats the ₹50,000 as if it had three years to grow, when it had only two. XIRR corrects for that and gives 9.22% a year.

Worked example: a monthly SIP over 36 months

Now a SIP. Suppose ₹5,000 goes in on the first of every month for 36 months, from 1 January 2023 to 1 December 2025, and the units are worth ₹2,15,000 on 1 January 2026.

Illustrative figures. Absolute return covers the whole period; XIRR is an annual rate.

The 19.44% is the gain on ₹1,80,000 across the full period. The 11.85% is the yearly rate that makes the 36 payments and the final value agree. Only the second can be compared with a yearly return from any other investment.

Calculating XIRR for a mutual fund in a spreadsheet

Excel and Google Sheets use the same function, so the steps below work in either. You need only two columns.

1. Put the dates in column A, one row per cash flow. Enter them as real dates, not text.
2. In column B, enter each investment as a negative number and the current value as a positive number. For the lump sum example above, the three rows are −100000, −50000 and 190000.
3. For a SIP, enter each monthly instalment as its own negative row, and add the current value as a final positive row dated on the day you measure the holding.
4. In an empty cell, type =XIRR(B2:B4, A2:A4) and press Enter. Replace the ranges with your own rows. The function takes the amounts first and the dates second.
5. Format the result cell as a percentage. For the lump sum example it shows 9.22%. If you see #NUM!, check that the column has at least one negative and one positive value.

Fund returns and your own returns differ

A fund's published return is usually CAGR for a single lump sum, so it describes the fund and not your money. Your own portfolio return is XIRR, because your cash flows happened on different days and in different amounts. Before comparing your XIRR with a fund's CAGR, check which measure each figure uses. Comparing the two directly mixes up a timing-weighted rate with a day-one rate.

Frequently asked questions

Is XIRR the same as CAGR?

No. CAGR assumes one investment at the start and one value at the end. XIRR handles several investments made on different dates, so it gives the same answer as CAGR only when a single lump sum is involved. For a SIP, the two numbers usually differ, and XIRR is the one to use.

Can I calculate XIRR for a SIP without a spreadsheet?

Not by hand in any practical way. XIRR has to be solved numerically, by trying rates until the investments and the current value balance. A spreadsheet does this in one step, using the date and amount of every instalment and the current value.

Why is my XIRR different from the return a fund shows?

The fund's published figure usually assumes one lump sum on the first day and reports CAGR. Your XIRR reflects the dates and amounts of your own cash flows, so a SIP started in a different month, or an extra investment made at a different time, produces a different number even in the same fund.