What Is XIRR in Mutual Funds (and How to Calculate It in Excel)

If you invest through SIPs, the return your app shows first is often "absolute return": total gain divided by total invested. It's easy to read and usually wrong for judging how a SIP performed.

Why absolute return misleads

Say you started a ₹5,000 SIP three years ago. Your first instalment has been invested for 36 months; last month's has been invested for one. Absolute return treats them the same. A 25% absolute gain after three years of SIPs is a very different result from a 25% gain on a lumpsum held for three years.

What XIRR does

XIRR (extended internal rate of return) finds the single yearly rate at which all your investments, each made on its own date, would grow into today's value. It is the number to compare with an FD rate or another fund.

A worked example

• 10 Jan: invest ₹5,000
• 10 Feb: invest ₹5,000
• 10 Mar: invest ₹5,000
• Today (10 Oct): value ₹16,200

Gain is ₹1,200 on ₹15,000, which is 8% absolute. But the money was invested for only about 8 months on average, so the XIRR comes to roughly 12% a year.

How to calculate XIRR in Excel or Google Sheets

1. In one column, list dates: each investment date, and today's date at the end.
2. In the next column, list cash flows: investments as negative numbers, redemptions as positive, and today's portfolio value as a positive number at the end.
3. Use `=XIRR(cash_flows, dates)` and format the cell as a percentage.

Rules that trip people up:

• There must be at least one negative and one positive value.
• The first date in the list should be the earliest.
• Blank cells inside the ranges cause errors; use 0 instead.

What is a good XIRR?

It depends on the fund type and the period. Over 7 to 10 years, diversified equity funds have historically delivered low to mid teens in India, but past returns don't guarantee future ones. Over one or two years, XIRR swings a lot; judge equity funds over at least five years.

XIRR vs CAGR

CAGR works for a single lumpsum: one amount in, one value out. XIRR handles many cash flows on different dates, including SIPs, top-ups and partial redemptions. For a single lumpsum held the whole time, the two give the same answer. For SIPs, always use XIRR.

Track it for every fund

Calculating XIRR fund by fund gets tedious. Our Mutual Fund Portfolio Tracker with XIRR does it for up to 10 funds and your whole portfolio from one transactions list, with units, gains and a category split. It works in Excel and Google Sheets with no login.