XIRR vs CAGR mutual fund returns India 2026: SIP calculation, Excel formula, real examples — which method is correct?

XIRR vs CAGR confusion costs Indian investors wrong investment decisions worth lakhs annually. CAGR shows simple growth rate for lump sum investments. XIRR shows real annualized return for SIPs, NPS, ULIP, or any irregular cash flow scenario.

The fundamental problem: - 70%+ investors use CAGR for SIP returns → systematically wrong by 30-50% - ₹6L invested in SIP, grown to ₹8.7L over 5 years: naive CAGR says 7.2%; actual XIRR is 12.5% - Wrong calculation → wrong fund comparison → wrong exit decisions

Common pain points: - Investors think SIP "underperformed" when actually it performed well - Fund comparisons across SIP vs lump sum done incorrectly - FD vs MF comparison done wrong - Tax-adjusted comparisons missed

Why this article matters: - Equity SIPs are the #1 wealth creation vehicle for Indians (₹25,000+ crore monthly SIP flows) - 8+ crore MF folios in India (most are SIP) - Wrong return calculations lead to premature exits + missed compounding - Proper XIRR understanding = better fund evaluation + retention discipline

Yeh article aapko complete XIRR framework deta hai — vs CAGR comparison, Excel formula step-by-step, real SIP examples, FD comparison, 6 common mistakes, aur practical decision-making for ₹5K-1L monthly SIP investors.

# Fundamental Concepts

# CAGR — Compound Annual Growth Rate

Formula:

CAGR = (Final Value / Initial Value)^(1/Years) - 1

Use case: Single investment, single redemption

Example: - Invested ₹1,00,000 on 1 January 2021 - Worth ₹1,50,000 on 1 January 2026 (5 years later) - CAGR = (1,50,000 / 1,00,000)^(1/5) - 1 = 8.45% per annum

Where CAGR works: - Lump sum mutual fund investment - Single FD (start date to maturity) - Bond purchased + held to maturity - Real estate sold at single point - Any single inflow → single outflow scenario

# XIRR — Extended Internal Rate of Return

Concept: Annualized return for multiple cash flows at irregular intervals.

No simple formula — calculated iteratively by software (Excel's XIRR function).

Use case: SIPs, NPS contributions, ULIP, dividend reinvestment, partial redemptions, any real-world investment with multiple transactions.

Why XIRR is necessary: - Each cash flow has its own holding period - Money invested earlier has more time to compound - Money invested later has less time to compound - Simple average / CAGR misses this nuance

# XIRR Excel Formula — Complete Guide

# Formula syntax

=XIRR(values_range, dates_range, [guess])

• values_range: Cell range containing cash flow amounts (negative for outflows, positive for inflows)
• dates_range: Cell range containing dates of each cash flow
• guess (optional): Estimated XIRR; default 10% (can ignore for most cases)

# Step-by-step setup

Scenario: ₹10,000 monthly SIP for 60 months from January 2021 to December 2025. Current value (23 May 2026): ₹8,70,000.

# Step 1: Set up columns

Critical: - Investments as negative (money leaving you) - Current/redemption value as positive (money received)

# Step 2: Enter XIRR formula

In any empty cell (e.g., D2):

=XIRR(B2:B62, A2:A62)

# Step 3: Format as percentage

Result will appear as decimal (e.g., 0.125). Format → Number → Percentage → Decimal places 2.

Result: 12.50% (per annum XIRR)

# Quick template

Date            Amount
01-Jan-2021    -10000   ← First SIP
01-Feb-2021    -10000
...            ...
01-Dec-2025    -10000   ← Last SIP
23-May-2026    +870000  ← Current value (positive!)

XIRR formula = XIRR(amounts, dates)

# Real Worked Examples

# Example 1: 5-Year ₹10K Monthly SIP

Setup: - Start: 1 January 2021 - End SIP: 1 December 2025 (60 months) - Total invested: ₹6,00,000 - Current value: ₹8,70,000

Naive CAGR (WRONG approach):

CAGR = (8,70,000 / 6,00,000)^(1/5.4) - 1 = 7.2%

XIRR (CORRECT approach):

XIRR considering 60 individual ₹10K cash flows + ₹8.7L final = ~12.5%

Difference: 5.3 percentage points understated by naive CAGR.

Why: Average money in SIP is invested only ~2.7 years (not 5.4), so per-rupee return is much higher.

# Example 2: SIP with Partial Redemption

Setup: - ₹10K SIP for 36 months (₹3,60,000 invested) - Partial redemption ₹2,00,000 on 1 January 2024 - Final value of remaining units: ₹3,50,000 on 23 May 2026

Cash flows:

XIRR: Captures partial inflow at exact date → accurate annualized return.

# Example 3: Multiple SIPs in Different Funds

If investing in 3 different funds: - Calculate XIRR per fund separately - Or pool all cash flows + calculate combined portfolio XIRR

Combined portfolio approach: - All investments (all funds, all dates) as negative - All current values (sum of all funds) as positive on today's date - XIRR formula on combined range

# Example 4: NPS XIRR Calculation

NPS contributions are similar to SIP — calculate XIRR considering: - Monthly/annual contributions (negative) - Current NPS corpus value (positive on today's date)

NPS-specific note: Employer contribution + own contribution both included; tax benefits don't directly affect XIRR (XIRR is pre-tax return).

# Comparing Investments — XIRR Framework

# MF SIP vs Bank FD

Apples-to-apples comparison via XIRR:

Tax-adjusted (30% tax bracket):

Verdict: Equity SIP delivers ~6% post-tax XIRR advantage over FD for high tax bracket.

# Different Funds Comparison

Compare XIRR over same time period + same SIP amount for fair comparison.

Example:

Insight: Higher risk funds typically show higher XIRR over 5+ year periods but with more drawdowns en route.

# CAGR vs XIRR — When Each Wins

# When XIRR is Negative

# Common scenarios

#### Scenario 1: Bear market entry - SIP started January 2008 (just before global financial crisis) - Market crashed 50%+ by March 2009 - 15-month XIRR: -40% to -60%

#### Scenario 2: Sectoral fund downturn - Banking sector fund SIP during 2018-2020 PSU crisis - 24-month XIRR: -10% to -20%

#### Scenario 3: Early phase volatility - New equity SIP in first 6-12 months - Often negative XIRR due to insufficient compounding time + market volatility

# Decision framework for negative XIRR

Mean reversion: Indian equity markets have historically delivered 12-15% XIRR over 10+ year SIPs. Negative short-term XIRR often turns strongly positive with patience.

# 6 Common XIRR Calculation Mistakes

### Mistake #1: Wrong sign convention Error: All investments as positive numbers
Result: Excel can't identify cash flow direction → wrong XIRR
Fix: Investments negative, redemptions positive

### Mistake #2: Missing intermediate cash flows Error: Forgetting to include partial redemptions, dividend payouts
Result: Inflated apparent return
Fix: Capture ALL cash flows (every investment + every redemption)

### Mistake #3: Wrong dates Error: Using approximate dates (e.g., always 1st of month)
Result: Minor accuracy issue (usually ≤0.5% off)
Fix: Use actual transaction dates from CAMS/Karvy statements

### Mistake #4: Final value date wrong Error: Using arbitrary future date instead of today/redemption date
Result: Significantly distorted XIRR
Fix: Use today's date (current value) or actual redemption date

### Mistake #5: Excluding final value Error: Including only investments without current/final value
Result: Cannot calculate XIRR (no positive cash flow)
Fix: Always include final value (current portfolio value or redemption amount) as positive entry

### Mistake #6: Confusing absolute vs annualized Error: Reporting absolute % gain (e.g., 50% gain over 5 years) as annual return
Result: Overstating returns
Fix: Use XIRR for proper annualization

# XIRR in Indian MF Apps

# Direct XIRR access (no Excel needed)

Apps showing XIRR directly: - Zerodha Coin — Portfolio dashboard → XIRR per fund + overall - Groww — Investments → Returns → XIRR - Kuvera — Portfolio summary → XIRR - ETMoney — Returns analysis → XIRR - Paytm Money — Returns metric - AMC apps (HDFC, ICICI, SBI MF, Axis MF) — Most show XIRR in portfolio reports

### CAMS/KFinTech consolidated statement - Cumulative XIRR across all funds - Folio-wise breakdown - Useful for tax-time portfolio review

# Practical Investment Decisions Using XIRR

# Fund evaluation framework

Step 1: Check 5-year + 10-year XIRR of fund (since inception if longer) Step 2: Compare with category benchmark + index Step 3: Assess consistency (year-by-year XIRR variance) Step 4: Tax-adjust per holding period + tax bracket Step 5: Decision matrix

# Decision matrix

# Common decision errors

• Exiting after 1-year negative XIRR: Premature; equity needs 5+ years
• Holding underperformer for 5+ years hoping: Hope is not strategy; switch if 3-year + 5-year XIRR both lag
• Comparing different time periods: New fund vs old fund XIRR not directly comparable

# Action Plan

### One-time setup - [ ] Download portfolio statements from CAMS/KFinTech - [ ] Set up Excel/Sheet with date + cash flow columns - [ ] Calculate XIRR for each fund + overall portfolio - [ ] Save template for future reference

### Quarterly review - [ ] Update current values - [ ] Recalculate XIRR - [ ] Compare with benchmark - [ ] Identify any underperformers (3+ years lag)

### Annual review - [ ] Comprehensive XIRR analysis - [ ] Tax-adjusted returns calculation - [ ] Compare with risk-free benchmarks - [ ] Rebalance if needed (per asset allocation rules)

### Pre-redemption check - [ ] Calculate post-tax XIRR - [ ] Compare with alternative deployment - [ ] Tax implications (STCG/LTCG) - [ ] Make informed exit decision

# References (verified 23 May 2026)

• ClearTax — XIRR Calculation Mutual Fund SIP
• ETMoney — XIRR vs CAGR Explained
• Groww — XIRR Calculator + Tutorial
• Investopedia — XIRR Function Excel
• AMFI — Mutual Fund Performance Metrics
• Zerodha Varsity — Personal Finance + Returns Calculation
• Microsoft Excel — XIRR Function Documentation

Disclaimer: Yeh article educational guidance hai for understanding mutual fund return calculation methods. XIRR + CAGR are mathematical concepts independent of specific fund recommendations. Mutual fund investments subject to market risks — past performance not indicative of future returns. Tax implications (STCG, LTCG, indexation post Budget 2024) require qualified CA consultation. SEBI-registered advisors recommended for portfolio-specific advice. Data verified 23 May 2026.

Written by

Prabhakar Kumar

Chartered Accountant (ICAI, Nov 2019)

Founder of VittSphere Technologies. Practicing CA serving 200+ MSME clients across Pune. 86% win-rate at AO and CIT(A) level tax appeals. Writes on Indian taxation, capital gains, and personal finance.