Mutual Fund Returns: How to Read XIRR, CAGR, and Absolute Return

Open your mutual fund statement or a fund house''s factsheet and you''ll encounter three different return numbers — sometimes for the same fund and the same period. Most investors look at one of them, draw conclusions, and either celebrate or panic without realising the numbers measure fundamentally different things. The most common confusion: a SIP investor sees the fund''s factsheet quoting "12% CAGR" and assumes that''s their return — but it''s not. The CAGR is what a lumpsum investor would have earned. The SIP investor''s return — which can be different from the published CAGR — is properly measured by XIRR.

This article unpacks the three metrics, explains when each is correct, walks through the Excel/Google Sheets XIRR formula step by step, and shows the specific cases where the wrong metric leads to wrong decisions. By the end, you should be able to look at any mutual fund return number and ask the right follow-up question: absolute, CAGR, or XIRR? Use Ganak''s SIP Calculator to project future XIRR for your SIP plan.

The Three Metrics, Defined

Each metric answers a slightly different question. Understanding the question each answers is more useful than memorising the formula.

The progression from absolute to CAGR to XIRR is a progression in sophistication. Absolute return tells you nothing about how long the money was at work. CAGR fixes that by annualising — but it assumes the simplest possible cashflow (all money in at start, all out at end). XIRR generalises CAGR to handle any cashflow pattern, including SIPs, withdrawals, switches, and top-ups.

Absolute return

The simplest measure: (Final Value − Total Invested) / Total Invested × 100. If you put in ₹1,00,000 and ended up with ₹1,35,000, the absolute return is 35%. It''s useful for quick communication but profoundly misleading without context. A 35% absolute return is excellent over one year, mediocre over five, and poor over ten — the same number means completely different things at different durations.

You''ll see absolute return prominently quoted in advertisements and headlines because large absolute numbers (50%, 100%, 200%) look impressive. Don''t make decisions on this metric alone. Always ask: "over what period?" — and convert mentally to an annualised number.

CAGR — Compound Annual Growth Rate

CAGR is the annualised return assuming a single upfront investment that grew at a constant rate. The formula:

CAGR = (Final Value / Initial Investment) ^ (1 / Years) − 1

Continuing the example: ₹1,00,000 grew to ₹1,35,000 over 5 years. CAGR = (1.35) ^ (0.2) − 1 = 6.19%. This tells you that the lumpsum compounded at 6.19% per year — a much more interpretable number than "35% over 5 years," because you can compare it directly against fixed deposit rates, PPF, inflation, or other fund CAGRs.

This is the standard return metric used by fund houses, AMFI, SEBI disclosures, and most return comparison tools. The metric assumes the entire amount was invested on day one and grew steadily. For a one-time lumpsum investment, CAGR is exactly the right metric — your actual experience matches the assumption.

XIRR — Extended Internal Rate of Return

XIRR is the annualised return when cashflows happen at different points in time. The formula is iterative — there''s no closed-form solution, so Excel and Google Sheets compute it numerically. Conceptually, XIRR finds the single annualised rate that, applied to your specific cashflow pattern, would have produced the final value you see today.

For a SIP of ₹10,000 every month for 24 months, each instalment is treated as a separate "investment" with its own holding period. The first ₹10,000 sat for 24 months; the 24th ₹10,000 sat for just one month. The XIRR is the single annualised rate that reconciles all 24 instalments against the current portfolio value.

For irregular contributions — say you started SIP at ₹5,000, increased to ₹10,000 after 18 months, then added a ₹2 lakh lumpsum on year three, then redeemed ₹50,000 at year four — only XIRR can produce a meaningful annualised return. CAGR can''t handle any of these scenarios.

Worked Example: The Three Metrics on the Same SIP

To make the distinction concrete, consider a SIP of ₹5,000 per month for 60 months (5 years) into an equity fund that''s tracking 12% CAGR over the period:

Notice the key result: the fund grew at 12% CAGR, and your XIRR also comes out to 12%. XIRR and the fund''s CAGR match when the underlying compounding rate is the same. But the naïve calculation — dividing final value by total invested and trying to annualise — produces 6.21%, completely wrong, because it treats your SIP as if you''d invested ₹3 lakh on day one when in fact your money was spread over five years.

The contrast with a lumpsum scenario makes the wealth difference visible. Same fund, same period, same total invested:

The rate of return is the same (12%) but the final wealth is dramatically different (₹5.29 lakh vs ₹4.06 lakh). This is the source of much confusion. XIRR and CAGR are rate measures, not amount measures. A 12% return is a 12% return whether you invested lumpsum or via SIP — but if you invested via SIP, only half your money was earning that 12% for the full period, so the total wealth created is smaller. This is also why the SIP vs Lumpsum debate (covered in our SIP vs Lumpsum article) has lumpsum winning in absolute terms even though both can post identical "returns."

Why Fund Houses Publish CAGR (Not XIRR)

Open any mutual fund factsheet — the 1-page summary every AMC publishes monthly — and you''ll see "Returns" presented as a row of CAGR percentages: 1-year CAGR, 3-year CAGR, 5-year CAGR, since-inception CAGR. You won''t see XIRR. The reason is structural: a fund factsheet describes the fund''s performance independent of how investors used it. CAGR measures pure fund performance — what would a hypothetical lumpsum have done?

This is exactly the right metric for the fund house''s purpose. When comparing two equity funds, you want to know which fund delivered better underlying performance — CAGR answers that. When evaluating whether to invest a lumpsum, CAGR is also the right metric.

But when an investor reads the factsheet''s "12% 5-year CAGR" and wants to know "what did MY SIP earn," they''re asking a different question. The fund delivered 12% to a hypothetical lumpsum investor, but the actual SIP investor''s XIRR depends on the path the fund took — if it crashed in year 1 and recovered slowly, the SIP buying through the crash gets higher XIRR than the published CAGR; if it rallied early and stagnated later, the SIP gets lower XIRR. Same fund, same period, different investor experience.

The practical rule: fund factsheet CAGR is the right metric for fund comparison; your portfolio''s XIRR is the right metric for tracking your own progress. Don''t mix them up.

Computing Your XIRR in Excel or Google Sheets

This is one of the most useful spreadsheet formulas a personal investor can learn. Both Excel and Google Sheets have a built-in =XIRR(values, dates) function that handles all the iterative computation for you.

The setup is two columns. Column A: the dates of every transaction. Column B: the cashflow amount — negative for money going out (SIP installments, lumpsum investments, top-ups) and positive for money coming in (redemptions, current portfolio value). The current portfolio value is treated as a positive cashflow on today''s date.

Example: a 24-month SIP of ₹10,000 starting 5 January 2024. Lay it out like this:

Then in any empty cell, type the formula:

=XIRR(B2:B26, A2:A26)

where B2:B26 is the cashflow column (24 SIPs + 1 portfolio value) and A2:A26 is the dates column. Format the result cell as percentage (right-click → Format → Percentage) and Excel/Google Sheets will return the annualised return on your specific SIP — your actual XIRR.

The mechanics behind it: Excel iteratively tries different annual rates until it finds the one where the discounted sum of all cashflows equals zero. The first ₹10,000 (negative cashflow on 5 Jan 2024) gets discounted using a 24-month exponent; the last ₹10,000 (5 Dec 2025) gets discounted using a 1-month exponent; the final positive portfolio value gets discounted at 0 months. The rate that balances them all is your XIRR.

Practical tips:

• Get your transaction history from your CAMS or KFintech consolidated account statement — both AMCs and registrars provide downloadable CSV files with all transaction dates and amounts.
• The current value as of today comes from your latest portfolio statement.
• If you have multiple funds, you can compute XIRR for each fund separately or for the entire portfolio combined.
• For SIPs that are still ongoing, the XIRR you compute today is a snapshot — it will update as the portfolio value changes.
• If Excel returns "#NUM!" error, you''ve probably mixed up signs (cashflows out should be negative, current value positive) or the dates aren''t in valid date format.

When CAGR and XIRR Are Equal (and When They''re Not)

An interesting nuance: for a SIP into a fund that grows at a perfectly constant rate, XIRR and CAGR will be exactly equal. The earlier worked example showed this — fund growing at 12% CAGR, SIP XIRR also 12%.

But real markets don''t grow at constant rates. They have peaks, crashes, sideways periods, and surges. The path of returns matters for XIRR even when it doesn''t matter for CAGR (which only cares about the start and end values). Two scenarios that illustrate the divergence:

Scenario A — V-shaped recovery: Fund starts at NAV 100, drops to NAV 60 within 18 months (severe correction), then recovers to NAV 150 by month 60. The fund''s 5-year CAGR is about 8.45% (₹100 to ₹150). A SIP investor through this period bought far more units when prices were low (during the trough), and the units bought between months 12-24 had a much higher cost basis advantage when prices recovered. The SIP''s XIRR can be 12-15% — meaningfully higher than the fund''s CAGR.

Scenario B — Late rally: Fund moves sideways for 4 years at NAV 100, then surges to NAV 200 in year 5. CAGR is about 14.87%. The SIP investor''s contributions were mostly already at high prices when the rally happened (by year 5, you''ve done 48 months of SIPs at NAV around 100, only 12 months at the rising price). The SIP''s XIRR can be 8-10% — meaningfully lower than the fund''s CAGR.

This is why the same fund can produce different XIRR for different SIP investors — depending on when they started and stopped, the path matters. It''s also why past CAGR is not a reliable predictor of your future XIRR — what matters is the path the fund takes during your specific investment window.

Common Mistakes in Reading Returns

Specific patterns that produce misleading conclusions:

Comparing absolute returns across different durations. A fund with 80% absolute return over 4 years vs another with 90% over 6 years sounds like the second is better, but the first compounded at 15.8% CAGR while the second only at 11.3%. Always convert to CAGR before comparing.

Calculating "annualised return" by dividing absolute return by years. A 35% absolute return over 5 years doesn''t mean "7% per year" — that''s an arithmetic average that ignores compounding. The CAGR is 6.19%, somewhat lower because compounding works against simple division. Always compound, never divide.

Comparing your SIP''s naïve return to a fund''s factsheet CAGR. A common misperception: you see your statement showing ₹3 lakh invested, ₹4 lakh value, do the math, and conclude your return is "33% over 5 years" — and then notice the fund''s 5-year CAGR is "12%" and panic that you''ve underperformed. You haven''t — the 33% is absolute return over the full period, and your actual XIRR is close to the fund''s 12% CAGR. The metrics are different scales.

Using point-to-point CAGR over short periods. Trailing 1-year and trailing 3-year CAGR numbers can swing dramatically depending on the start and end dates. A fund that''s "delivered 25% CAGR over 3 years" might just have caught a strong rally; the same fund might show -5% CAGR if measured one quarter earlier. Use rolling returns (CAGR computed across many overlapping start dates) for a more stable picture of fund performance.

Forgetting that XIRR can be negative. If your SIP started at a market peak and you haven''t held long enough for recovery, XIRR can be -5% or worse. This isn''t a calculation error; it''s a reflection of where you bought relative to where the fund is now. Continuing the SIP through this kind of period is usually the right move (you''re buying at lower NAVs) — stopping is the mistake. The XIRR will improve as the fund recovers.

Using CAGR for evaluating SIP performance. A fund''s factsheet says 18% CAGR over 5 years. Your SIP in the same fund shows XIRR of 14%. Many investors conclude the fund is somehow not delivering — but the gap is structural, not performance-based. Your SIP money was spread over the period, so the path of returns matters in a way it doesn''t for the hypothetical lumpsum. The 14% XIRR is your actual return; the 18% CAGR is what a lumpsum investor would have got.

Frequently Asked Questions

What is the difference between CAGR and XIRR?

CAGR (Compound Annual Growth Rate) is the annualised return assuming you invested as a single lumpsum at the start — it''s the standard metric on mutual fund factsheets and is the right measure for comparing funds against each other or evaluating lumpsum investments. XIRR (Extended Internal Rate of Return) is the annualised return on irregular cashflows like SIPs, top-ups, or partial withdrawals — it accounts for the fact that each instalment was invested at a different time and treats your portfolio''s actual transaction history. For SIP investors, XIRR is the correct metric for measuring your own returns; CAGR doesn''t apply because your money wasn''t invested as a lumpsum.

How do I calculate XIRR in Excel?

The Excel formula is =XIRR(values, dates). Set up two columns: dates of every transaction in one column, cashflow amounts in another. Make all money-out transactions (SIP installments, lumpsum investments) negative, and the current portfolio value (or any redemption) positive. The formula returns the annualised return as a decimal — format the cell as percentage to read it. Google Sheets has identical =XIRR() syntax. The function handles all the iterative computation automatically. If you get a "#NUM!" error, check that you''ve mixed signs correctly (cashflows out are negative, current value or redemptions are positive) and that all dates are in valid date format.

Why is my SIP XIRR different from the fund''s published CAGR?

Because the path of returns matters for XIRR but not for CAGR. The fund''s CAGR only depends on the start and end NAVs over the measurement period — it ignores everything in between. Your SIP''s XIRR depends on when each instalment was invested relative to the fund''s peaks and troughs during the period. If the fund crashed early and recovered later (V-shaped path), your SIP XIRR will be higher than the fund''s CAGR because you bought at low prices. If the fund rallied late after a long sideways period, your SIP XIRR will be lower than the CAGR because most of your contributions were already invested before the rally. Same fund, same period, different investor experience.

What is absolute return in mutual funds?

Absolute return is the simple percentage gain or loss over the entire investment period: (Final Value − Initial Investment) / Initial Investment × 100. It ignores time entirely — a 35% absolute return could be over 1 year, 5 years, or 15 years and the number looks identical. This makes absolute return useful for quick communication but misleading for comparison or decision-making. Always convert absolute return to CAGR (for lumpsum) or compute XIRR (for SIPs) to get a time-adjusted view. A 35% absolute over 5 years is roughly 6.19% CAGR — much less impressive than the 35% headline suggests.

Which return metric should I use to compare two mutual funds?

CAGR — specifically, the trailing CAGR over multiple periods (1-year, 3-year, 5-year, since-inception). This is what appears on factsheets and what AMFI publishes. CAGR is the correct comparison metric because it measures fund performance independent of any specific investor''s contribution pattern. Even better is rolling returns (CAGR computed across many overlapping start dates), which removes the bias of cherry-picked start and end points. Don''t compare absolute returns across funds with different inception dates, and don''t use your personal XIRR for fund comparison — your XIRR reflects your specific cashflow timing, not the fund''s underlying performance.

What does negative XIRR mean?

A negative XIRR means your portfolio is currently worth less than what you''ve invested, on an annualised basis. If you started an equity SIP at a market peak and haven''t held long enough for recovery, XIRR can be -5%, -10%, or worse. This isn''t a calculation error; it''s a reflection of where you bought relative to where the fund is now. Negative XIRR during the first 1-3 years of an equity SIP is common and not necessarily a reason to stop. The XIRR will typically improve as the fund recovers and as more SIP instalments add to the position. Stopping the SIP during a downturn locks in the negative return; continuing is usually the right move because you''re buying more units at lower prices.

Where do I find the dates and amounts to compute my XIRR?

Two main sources. First, your CAMS or KFintech consolidated account statement — both registrars provide downloadable Excel/CSV files showing every transaction across all your mutual fund folios with dates, amounts, NAV, and units. Request via cams.com or kfintech.com (formerly Karvy) using your email and PAN. Second, your AMC''s portal — each fund house (Zerodha Coin, Groww, Kuvera, ICICI Pru AMC, HDFC AMC, etc.) provides transaction history downloadable to CSV. For the final positive cashflow in your XIRR formula, use your current portfolio value as shown on the latest statement. Update this regularly to track XIRR progression over time.

Sources and Further Reading

This article references mutual fund return reporting standards from AMFI (Association of Mutual Funds in India), SEBI''s disclosure requirements for fund factsheets, and Excel/Google Sheets'' built-in XIRR financial function. For official references and tools:

• AMFI — Association of Mutual Funds in India, return calculation methodology and fund factsheets
• SEBI — mutual fund disclosure standards and investor education
• CAMS — Computer Age Management Services, consolidated account statement download
• KFintech (formerly Karvy) — consolidated account statement download for funds it serves as RTA

Last verified: 2 June 2026. The XIRR formula syntax (=XIRR(values, dates)) is identical in Microsoft Excel and Google Sheets and has not changed in recent versions. CAGR, XIRR, and absolute return as concepts are standard finance metrics that don''t change with tax rules or market structure.