XIRR in Mutual Funds: The Only Return Number That Fits SIPs
Open a mutual fund app after a year of monthly instalments and it can show two different return figures for the same folio: a profit of 8.5% on the holdings screen, 18.2% in the returns tab. Neither is a typo and neither is wrong. They answer different questions, and XIRR in mutual funds is largely the discipline of knowing which question you actually asked — because a figure that ignores the dates on your instalments cannot answer the one that matters.
This guide separates the three return numbers an Indian investor meets — absolute return, CAGR and XIRR — shows when each is right, and works through twelve dated instalments where the first misleads in both directions. It belongs to Gale’s mutual fund hub, which stays on fund data and mechanics.
What is XIRR in mutual funds?
XIRR stands for extended internal rate of return: the single annualised rate at which every rupee you paid in, compounded from its own date, exactly accounts for every rupee you took out or still hold.
A more usable way to hold it in your head: imagine a bank paying a fixed compounding rate on each deposit from the day it lands. XIRR is the rate that bank would have to offer for your account to reach exactly the value your fund holding shows today.
That framing carries a consequence most investors skip past. XIRR is not a property of the fund; it is a property of your cash flows into and out of it. Two people in the same scheme, holding identical units on the same day, can have genuinely different XIRRs because they paid in on different dates. The scheme has one NAV series; each investor has their own rate.
XIRR meaning in mutual funds, in one line
The XIRR meaning in mutual funds reduces to this: the annualised rate that makes the present value of all your dated cash flows net to zero. Every investment is a negative flow on its date, every redemption or current valuation a positive one, and XIRR is the discount rate at which the two sides cancel exactly.
No spreadsheet solves that in closed form. There is no formula to rearrange; software finds the rate by trial and refinement until the flows net to zero. Worth knowing, because the figure in your app came from a search rather than an equation — which is why the calculation occasionally fails to return a number at all.
Each flow is money leaving or entering your bank account on a date. The units and NAVs behind them — see what NAV actually measures — decide how large the final positive flow is, but the calculation itself sees only rupees and dates. If NAV, folio or AUM are still unfamiliar terms, keep the glossary and the what is a mutual fund guide open alongside this one.
Absolute return, CAGR and XIRR: three answers to three questions
The three numbers are not competitors. Each is right for one shape of investment and misleading for the others.
| Absolute return | CAGR | XIRR | |
|---|---|---|---|
| Question it answers | How much has this grown in total? | At what yearly rate did one investment grow? | At what yearly rate did a schedule of investments grow? |
| Calculation basis | (Value − Invested) ÷ Invested | (End ÷ Start)^(1 ÷ years) − 1 | Rate that discounts all dated flows to zero |
| Uses dates? | None | Start and end only | Every flow’s own date |
| Handles many instalments? | No | No | Yes |
| Handles partial withdrawals? | No | No | Yes |
| Annualised? | No | Yes | Yes |
| Right for | A plain “how much has it grown” | A lumpsum, or a NAV-to-NAV comparison | SIP, STP, SWP, top-ups, any dated stream |
| Goes wrong when | Holding periods differ across the money | More than two dated flows exist | All flows share one sign |
Absolute return is not false. It is silent about time, and time is most of what a return means: that a holding grew 61% tells you nothing until you know whether it took eight months or eight years. CAGR supplies the missing time, but only for an investment with one beginning and one end. XIRR supplies it for everything else.
Why a lumpsum only needs CAGR
A lumpsum has exactly two dated events: money goes in once, and it is worth something once. That is the whole cash-flow stream, and the compound annual growth rate handles it completely:
CAGR = (Ending value ÷ Starting value)^(1 ÷ number of years) − 1
An illustration: ₹1,20,000 invested on 5 September 2025 is worth ₹1,30,198 on 14 August 2026, a holding period of 0.94 years. The absolute return is 8.5%; the CAGR is 9.1%, higher because the gain arrived in under a year. These figures, and every figure below, are illustrations built to show the arithmetic — not a real scheme, a forecast or an expected return.
Run XIRR on that same pair of flows and it also returns 9.1%. CAGR is the two-date special case of XIRR: with only two flows the general method collapses into the simple formula, and the extra apparatus buys nothing.
That is why Gale’s Nifty 50 index fund comparison reports CAGR in its return columns rather than XIRR. Those columns set a scheme’s NAV on one date against the same scheme’s NAV on a later date — a single-point measurement of the fund’s own series, with no investor instalments in it. There is no cash-flow stream to weight, so CAGR is the honest tool and XIRR would return the identical number with more machinery. The moment your money starts entering on twelve different dates, that equivalence breaks.
Why an instalment stream needs XIRR
The reason is easy to state and easy to underestimate: in a monthly instalment plan, no two rupees share a holding period. A single percentage treating them as one pool invested on one date answers a question nobody asked.
Here is that failure in full — ₹10,000 a month into one equity scheme for twelve months, valued on 14 August 2026.
| # | Allotment date | Cash flow (₹) | NAV (₹) | Units allotted |
|---|---|---|---|---|
| 1 | 5 Sep 2025 | −10,000 | 47.80 | 209.205 |
| 2 | 6 Oct 2025 | −10,000 | 48.55 | 205.973 |
| 3 | 5 Nov 2025 | −10,000 | 47.10 | 212.314 |
| 4 | 5 Dec 2025 | −10,000 | 49.30 | 202.840 |
| 5 | 5 Jan 2026 | −10,000 | 50.05 | 199.800 |
| 6 | 5 Feb 2026 | −10,000 | 48.90 | 204.499 |
| 7 | 5 Mar 2026 | −10,000 | 47.65 | 209.864 |
| 8 | 6 Apr 2026 | −10,000 | 49.75 | 201.005 |
| 9 | 5 May 2026 | −10,000 | 51.20 | 195.313 |
| 10 | 5 Jun 2026 | −10,000 | 50.60 | 197.628 |
| 11 | 6 Jul 2026 | −10,000 | 52.35 | 191.022 |
| 12 | 5 Aug 2026 | −10,000 | 53.10 | 188.324 |
| — | 14 Aug 2026 (valuation) | +1,30,198 | 53.85 | 2,417.787 held |
Three allotment dates fall on the 6th because the 5th was a Sunday. Instruction dates and allotment dates are not the same thing, and the NAV you receive follows the cut-off and funds-realisation rules in the NAV guide. Use the dates units were actually allotted.
Now the two numbers:
- Total invested: ₹1,20,000
- Value on 14 August 2026: ₹1,30,198 (2,417.787 units × ₹53.85)
- Absolute return: 8.5%
- XIRR: 18.2%
More than double, from identical facts, and the reason is entirely in the dates. The twelfth instalment worked for nine days; the eleventh, thirty-nine. Weighted by how long each rupee was actually exposed, the average holding period is under six months — and 8.5% earned on money exposed under six months on average annualises to roughly 18%. The absolute figure is not lying; it declines to annualise, and an unannualised number on mostly young money will always look thin.
When the same arithmetic flatters instead
Reverse the maturity and the distortion reverses too. A second illustration: ₹10,000 a month for eight years, 96 instalments, ₹9,60,000 invested, worth ₹15,50,000 in August 2026.
| Illustration | Invested | Value | Absolute return | XIRR |
|---|---|---|---|---|
| 12 instalments over 1 year | ₹1,20,000 | ₹1,30,198 | 8.5% | 18.2% |
| 96 instalments over 8 years | ₹9,60,000 | ₹15,50,000 | 61.5% | 11.9% |
A 61.5% “profit” is the sort of figure that gets screenshotted. It is also eight years of compounding on the earliest instalments plus one month on the newest, presented as the achievement of a single period. Annualised properly, that holding returns 11.9%.
That is the real case against absolute return on a dated stream: it does not merely lose precision, it loses direction. On a young plan it understates, on a mature one it flatters, and nothing on the screen says which way it is bending today.
What the mutual funds XIRR on your statement includes
The mutual funds XIRR reported by an app, a registrar or a consolidated account statement is computed from your flows, so a few things are already baked into it:
- Costs are already inside it. The expense ratio is deducted daily within NAV before the number is struck, so a reported XIRR is net of recurring cost — see the expense ratio and exit load guide.
- Plan choice shows up as a different rate. Two investors with identical instalment dates in one scheme diverge if one holds direct and the other regular, because trail commission sits inside the regular plan’s NAV. The direct vs regular comparison quantifies that wedge.
- Exit load belongs in the final flow. Where a redemption suffered an exit load or statutory charges, the positive flow is the amount that reached your bank, not the gross valuation figure.
- Portfolio-level XIRR blends what should stay separate. One rate covering an aggressive equity scheme, a debt scheme and a liquid parking fund describes none of them; compute it per scheme, or per category from the types of mutual funds guide.
- ETFs need the traded price, not NAV. An exchange-traded fund is bought at a market price that can sit above or below NAV, plus brokerage, so the negative flow is what left your broking account. The ETF comparison covers the difference, and the guide to starting out the demat setup it needs.
Building a mutual funds return calculator in a spreadsheet
A spreadsheet is a more trustworthy mutual funds return calculator than most apps, for one reason: you decide which flows go into it. The layout is two columns.
| A — Date | B — Amount |
|---|---|
| 05-09-2025 | −10000 |
| 06-10-2025 | −10000 |
| … | … |
| 05-08-2026 | −10000 |
| 14-08-2026 | 130198 |
Then, in any empty cell:
=XIRR(B2:B14, A2:A14)Excel and Google Sheets both take =XIRR(values, dates) in that order — values first, dates second, the reverse of what most people guess. LibreOffice uses semicolons as separators but the same two arguments. An optional third argument is a starting guess.
The sign convention is the whole trick:
- Money leaving you is negative. Every purchase, instalment and switch into a scheme.
- Money coming back is positive. Every redemption, SWP credit and IDCW payout received in cash.
- Not yet redeemed? Enter today’s holding value as a positive flow dated today — a marked-to-market rate, honest as at that date and no other.
- One of each sign is required. An all-negative column returns
#NUM!, because no rate makes a one-sided stream net to zero.
Four traps account for most wrong answers. Dates must be real date values, not text that merely looks like dates. Investments entered as positive produce a mirror-image rate that looks plausible and means nothing. The result is a decimal, so format the cell as a percentage or you will read 0.182 as eighteen-hundredths of a percent. And a persistent #NUM! on a well-formed column means the search did not converge; a guess of 0.1 as the third argument normally settles it.
Every dated flow belongs in the column, not just the instalments: one-off purchases, step-ups, SWP withdrawals, and switches — a positive flow out of one scheme and a negative flow into the next, same date.
What XIRR still cannot tell you
XIRR is money-weighted, and that cuts both ways: it credits and blames your timing as much as the fund manager’s work. A scheme with a strong NAV-to-NAV CAGR can hand you a mediocre XIRR if your largest instalments landed before a drawdown, and the reverse holds too. Comparing two investors’ XIRRs in one scheme compares their payment schedules, not the fund.
So XIRR is the wrong tool for ranking funds unless dates and amounts are held identical. For fund-versus-fund work, point-to-point CAGR over the same window is cleaner — which is why fact sheets publish point-to-point and instalment-based returns as separate tables.
Two further limits matter. Short windows annualise noise: money invested for nine days, extrapolated to a full year, produces impressive rates carrying no information, so annualised figures below one year read better as arithmetic than as performance. And XIRR is silent on risk — two schemes can reach 12% by very different routes, and the rate alone will not tell a steady path from a violent one.
How much to invest each month, whether a tax-saving allocation earns its lock-in, and what redemption costs in tax are household-planning questions that Gale’s sister site WealthStem handles; this guide stays with the measurement.
FAQ
What is XIRR in mutual funds in one line?
XIRR is the annualised return of a series of dated cash flows — the single rate at which all your investments, discounted from their own dates, exactly account for what you redeemed or still hold.
Why is my XIRR higher than my absolute return?
Usually because most of your money is young. In a plan under two years old, the average rupee has been invested for well under half the total window, so annualising the gain lifts it above the unannualised figure. On plans running many years the relationship reverses.
Is XIRR the same as CAGR?
For a single lumpsum, yes — with only two dated flows both return the same rate. They diverge as soon as a third flow exists, and only XIRR handles that.
What counts as a good XIRR?
There is no universal threshold, and any figure quoted as one deserves suspicion. The meaningful comparisons are against the scheme’s own benchmark over the identical window, against the options actually available to you across that window, and against the risk taken to get there.
Should I trust my app’s XIRR or my own spreadsheet?
They should agree. When they do not, the cause is almost always a flow difference rather than a calculation difference — a missed switch, a payout counted twice, or gross redemption value entered where the net credit belonged. Reconcile the flow list first.
Why does my XIRR formula return #NUM!?
Most often because all the amounts share one sign, or the dates are stored as text. Failing that, the solver did not converge: add a guess such as 0.1 as the third argument.
Sources
- AMFI investor knowledge centre
- SEBI investor education portal
- Microsoft: XIRR function reference
- Google Sheets: XIRR function reference
Official references and linked Gale pages were checked on 18 August 2026. Spreadsheet function behaviour, cut-off timings and cost structures can change; confirm current SEBI, AMFI and fund-house material before relying on this.
What to weigh
Pick the measure that matches the shape of the money. One payment in and one value out is a CAGR question, which is why NAV-to-NAV fund comparisons are reported that way. A stream of dated instalments is an XIRR question, and nothing simpler does the job honestly. Absolute return earns its place as a plain statement of how much a holding has grown, never as a verdict on how well.
Then hold the number loosely. XIRR measures a schedule you chose as much as a fund you chose, it is silent on risk, and over short windows it annualises very little into something that looks like a lot. Rebuilding it once in a spreadsheet, from your own dated flows, is worth more than any figure an app displays — not because the app is wrong, but because you will finally know what went into it.
Gale.in is not a SEBI-registered investment adviser or research analyst; this article explains fund mechanics for education and is not a recommendation to buy or sell any scheme.