XIRR Calculator - Extended Internal Rate of Return Calculator
📈 Calculate XIRR for Mutual Fund SIP | Annualized Returns | Irregular Cash Flows | Portfolio Performance
An XIRR Calculator (Extended Internal Rate of Return Calculator) is an essential financial tool for calculating the annualized rate of return on investments with irregular cash flows, particularly mutual fund SIPs (Systematic Investment Plans) where you invest varying amounts at different dates. Unlike CAGR (Compound Annual Growth Rate) which assumes a single lump sum investment, XIRR accounts for the time value of money by considering each cash flow's date and amount, making it the most accurate metric for evaluating real-world investment performance. This free online XIRR calculator helps mutual fund investors, stock market traders, and portfolio managers calculate true returns on SIP investments, lump sum purchases, partial redemptions, and dividend reinvestments across platforms like Groww, Zerodha Coin, Upstox, Kuvera, and ET Money. Whether you're tracking equity mutual funds, debt funds, hybrid funds, or direct equity portfolios, XIRR provides the annualized return percentage that factors in every rupee invested and its timing, enabling accurate comparison with benchmark indices (Nifty 50, Sensex) and other investment options like PPF (7.1%), FD (6-7%), and NPS returns.
Calculate Your XIRR Returns
Enter each investment as negative (-) and current/redemption value as positive (+)
Understanding XIRR in Mutual Funds
XIRR (Extended Internal Rate of Return) is a financial metric that calculates the annualized rate of return for investments with multiple cash flows occurring at irregular intervals. In mutual fund investing, particularly SIPs, investors make multiple investments over time - monthly ₹5,000, quarterly ₹15,000, or irregular lump sums - making traditional return calculations like CAGR inadequate since CAGR assumes a single investment date.
XIRR solves this by considering both the amount and timing of each cash flow (investment or redemption), applying the time value of money principle where money today is worth more than the same amount in the future. For example, ₹10,000 invested in January has 12 months to grow, while ₹10,000 invested in December has only 1 month - XIRR accounts for this difference, providing a true annualized return percentage that reflects your actual investment experience across all transactions in equity funds, debt funds, hybrid funds, ELSS, or any other mutual fund category.
XIRR Formula and Calculation Method
XIRR is calculated using an iterative numerical method that solves for the rate (r) in the Net Present Value equation:
XIRR Mathematical Formula
Σ [CF_i / (1 + XIRR)^((d_i - d_0)/365)] = 0
CF_i = Cash flow at transaction i (negative for investments, positive for redemptions)
d_i = Date of cash flow i
d_0 = Date of first cash flow
XIRR = Extended Internal Rate of Return (what we're solving for)
Σ = Summation of all cash flows
=XIRR(values, dates, [guess])*100
values = Range of cash flows (B1:B10)
dates = Range of corresponding dates (A1:A10)
guess = Optional starting guess (default 10%)
Step-by-Step Calculation Example:
Scenario: Monthly SIP of ₹10,000 in equity mutual fund
Cash Flows:
• 01-Jan-2024: -₹10,000 (first investment)
• 01-Feb-2024: -₹10,000
• 01-Mar-2024: -₹10,000
• 01-Apr-2024: -₹10,000
• 01-May-2024: -₹10,000
• 01-Jun-2024: -₹10,000
• 01-Jul-2024: -₹10,000
• 01-Aug-2024: -₹10,000
• 01-Sep-2024: -₹10,000
• 01-Oct-2024: -₹10,000
• 01-Nov-2024: +₹1,05,000 (current value)
Calculation in Excel:
1. Enter dates in Column A (A1:A11)
2. Enter cash flows in Column B (B1:B11, first 10 negative, last positive)
3. Use formula: =XIRR(B1:B11, A1:A11)*100
4. Result: XIRR ≈ 9.5% to 11.5% (depending on exact market performance)
Total Invested = ₹1,00,000 | Current Value = ₹1,05,000 | Absolute Return = ₹5,000 (5%)
But XIRR annualizes this over ~10 months considering timing: ~10.2% annualized
XIRR vs CAGR - Key Differences
Understanding the difference between XIRR and CAGR is crucial for accurate return measurement:
| Aspect | XIRR | CAGR |
|---|---|---|
| Definition | Extended Internal Rate of Return | Compound Annual Growth Rate |
| Investment Type | Multiple cash flows, irregular dates | Single lump sum investment |
| Formula | NPV equation, iterative method | [(End Value/Start Value)^(1/Years)] - 1 |
| Timing Consideration | Accounts for date of each cash flow | Only considers start and end dates |
| Best For | SIP, irregular investments, multiple transactions | Lump sum, one-time investments |
| Complexity | Complex, requires iterative calculation | Simple, direct formula |
| Accuracy for SIP | Very accurate, considers all investments | Inaccurate, overestimates or underestimates |
Comparative Example:
Investment: ₹1,20,000 reaching ₹1,50,000 in 3 years
Scenario 1 - Lump Sum (CAGR appropriate):
Invested ₹1,20,000 on Day 1
CAGR = [(1,50,000/1,20,000)^(1/3)] - 1 = 7.72%
XIRR = 7.72% (same as CAGR for lump sum)
Scenario 2 - Monthly SIP (XIRR accurate):
Invested ₹3,333/month for 36 months = ₹1,20,000 total
CAGR (incorrectly) = 7.72% (same formula, wrong application)
XIRR = ~5.8% to 6.5% (lower because money invested gradually, less time to compound)
Difference: CAGR overestimates SIP returns by 1.5-2% compared to accurate XIRR!
How to Calculate XIRR for SIP Mutual Funds
Calculating XIRR for SIP requires systematic data collection:
- Step 1 - Download Transaction Statement: Get complete transaction history from AMC website, Groww, Zerodha Coin, Kuvera, or ET Money showing all SIP dates and amounts
- Step 2 - List All Investments: Create Excel sheet with two columns: Date (Column A) and Cash Flow (Column B). Enter each SIP as negative value with date
- Step 3 - Add Current Value: Check portfolio value today. Add row with today's date and current value as positive number (this represents redemption value)
- Step 4 - Apply XIRR Formula: Use =XIRR(B:B, A:A)*100 to get percentage. Ensure dates sorted chronologically (earliest first)
- Step 5 - Verify Result: XIRR should be logical - equity funds 8-20%, debt 5-9%. If absurdly high/low, check data entry
Example SIP Calculation:
| Date | Cash Flow | Description |
|---|---|---|
| 01-Jan-2023 | -₹5,000 | First SIP installment |
| 01-Feb-2023 | -₹5,000 | SIP |
| 01-Mar-2023 | -₹5,000 | SIP |
| ... | ... | Continue monthly |
| 01-Oct-2024 | -₹5,000 | Last SIP (22nd month) |
| 12-Nov-2024 | +₹1,25,000 | Current portfolio value |
Result:
Total Invested: ₹1,10,000 (22 months × ₹5,000)
Current Value: ₹1,25,000
Absolute Return: ₹15,000 (13.6%)
XIRR: ~12-14% annualized (accounting for 22-month investment period and timing)
XIRR Meaning in SIP Investments
XIRR in SIP context represents the annualized rate at which your money grows considering every monthly investment's timing:
- Time-Weighted Returns: First SIP has 24 months to grow, last SIP only 1 month - XIRR weights accordingly
- Annualized Metric: Converts multi-year returns into per-year percentage for easy comparison (10% XIRR = 10% per year growth)
- Includes All Transactions: Additional lump sum, missed SIPs, step-up SIPs, partial redemptions - all factored in
- Benchmark Comparison: Compare your fund's XIRR with Nifty 50 (12-15%), Sensex (13-14%), or category average
- Performance Evaluation: XIRR > 15% in equity = excellent, 12-15% = good, < 10% = review required
What Different XIRR Percentages Mean:
Equity Mutual Funds (Large Cap, Mid Cap, Small Cap):
• 18-25%: Outstanding performance, top-performing funds
• 15-18%: Excellent returns, beating benchmarks
• 12-15%: Good returns, in line with market
• 8-12%: Average returns, consider switching
• Below 8%: Poor performance, review fund immediately
Debt Mutual Funds:
• 9-12%: Excellent (rare in current rates)
• 7-9%: Good, beating FD rates
• 5-7%: Average, comparable to FD
• Below 5%: Poor, consider alternatives
XIRR to Annual Return Conversion
XIRR is already an annualized return - no conversion needed. Common misconceptions:
XIRR Return Interpretation
- Your investment growing at 15% per year (annualized)
- Equivalent to 15% CAGR if it were lump sum
- Accounts for all cash flows and their timing
To project future value:
Future Value = Current Value × (1 + XIRR)^Years
Example: Current ₹2 lakh, XIRR 15%, 5 more years
Future Value = 2,00,000 × (1.15)^5 = ₹4,02,271
XIRR Calculator for Different Scenarios
XIRR applies to various investment scenarios:
1. Regular Monthly SIP
Most common: Fixed amount every month. Easy to calculate with consistent pattern. Example: ₹5,000/month for 3 years. XIRR reflects market performance during investment period.
2. Step-Up SIP
Increasing SIP amount annually (e.g., 10% step-up). Enter each increased SIP amount with date. XIRR accounts for larger later investments having less time to grow.
3. Irregular Lump Sum + SIP
Combination of monthly SIP + occasional lump sums (bonus, inheritance). Enter all as separate cash flows. XIRR weights lump sum appropriately based on timing.
4. Partial Redemptions
Withdrawals during investment period. Enter redemptions as positive cash flows (opposite of investment). Reduces portfolio value, XIRR reflects actual returns post-withdrawal.
5. Dividend Reinvestment
If dividends auto-reinvested, no separate entry needed (NAV reflects it). If dividends received (payout option), enter as positive cash flows on dividend dates.
Pro Tip - Using XIRR Effectively: Calculate XIRR quarterly to track fund performance trends. If XIRR dropping consistently (e.g., 18% → 15% → 12% over 3 quarters), fund may be underperforming - compare with category average and benchmark. For tax planning, check XIRR of ELSS funds - if below 12%, regular equity funds without lock-in might be better. For STP (Systematic Transfer Plan), calculate XIRR separately for source and target funds. Download XIRR calculator Excel from Groww, ET Money, or create custom sheet with =XIRR formula for offline tracking.
Common XIRR Calculation Mistakes
Avoid these frequent errors that distort XIRR:
- Wrong Sign Convention: Investments should be negative (-), redemptions positive (+). Reversing signs gives incorrect negative XIRR
- Date Format Issues: Excel requires consistent format (DD-MM-YYYY). Mixed formats cause #NUM! error
- Unsorted Dates: While XIRR works with unsorted data, chronological order prevents errors and helps verification
- Missing Transactions: Forgetting lump sums, redemptions, or switches skews XIRR. Always use complete transaction history
- Using Portfolio Value Wrong: Current value should be total redemption value if selling today (not just gains). Full value, not just profit
- Mixing Multiple Funds: Calculate XIRR separately for each fund. Combining different funds' cash flows gives meaningless blended XIRR
- Short Investment Period: XIRR for 1-2 months can be misleadingly high/low. Reliable only after 12+ months
XIRR vs Other Return Metrics
Comparison with other investment return measures:
| Metric | Formula | Best For | Limitation |
|---|---|---|---|
| XIRR | NPV = 0 iterative | SIP, irregular cash flows | Complex calculation |
| CAGR | [(End/Start)^(1/Y)] - 1 | Lump sum investments | Ignores intermediate cash flows |
| Absolute Return | [(End - Start)/Start] × 100 | Short-term, < 1 year | Not annualized, can't compare periods |
| TWR (Time-Weighted) | Product of sub-period returns | Fund manager performance | Complex for individual investors |
| MWR (Money-Weighted) | Same as XIRR/IRR | Investor's actual returns | Same as XIRR |
When to Use Which Metric:
• Use XIRR: SIP, SWP, multiple investments/redemptions, real portfolio tracking
• Use CAGR: Lump sum index funds, single investment tracking, comparing fund NAV growth
• Use Absolute Return: Investments < 1 year (can't annualize meaningfully)
• Use Rolling Returns: Evaluating fund consistency across different market cycles
Calculating XIRR on Groww, Zerodha & Other Platforms
Most platforms now display XIRR automatically:
| Platform | XIRR Display Location | Manual Calculation |
|---|---|---|
| Groww | Portfolio → Mutual Funds → Individual fund → XIRR % | Download statement, use Excel |
| Zerodha Coin | Holdings → Fund details → XIRR shown | Console → Reports → Import to Excel |
| ET Money | Portfolio tab → Returns → XIRR displayed | Download transaction history |
| Kuvera | Holdings → Fund card → XIRR percentage | Export transactions |
| Paytm Money | Portfolio → Investment details → XIRR | Transaction report export |
Manual Calculation from Platform Data:
1. Download transaction statement (CSV/Excel format)
2. Filter for specific fund if calculating individual XIRR
3. Create two columns: Date and Amount (negative for purchase, positive for redemption)
4. Add row for current value with today's date (check platform for current NAV × units)
5. Apply =XIRR(amounts, dates)*100
6. Verify against platform-displayed XIRR (should match within 0.1-0.5%)
XIRR for Tax Planning
Tax implications related to XIRR returns:
- Long-Term Capital Gains (LTCG): Equity funds held > 1 year, tax 10% on gains above ₹1 lakh. XIRR helps project if crossing threshold
- Short-Term Capital Gains (STCG): Equity < 1 year = 15% tax. Higher XIRR (> 15%) needed to beat post-tax FD returns
- Debt Fund Taxation: As per slab rate (marginal tax). XIRR > 8% needed for 30% bracket to beat post-tax FD
- ELSS Tax Planning: Calculate XIRR for ELSS vs non-ELSS funds. If ELSS XIRR < 12%, regular funds (no lock-in) might be better despite 80C benefit
Important Note - XIRR Limitations: XIRR assumes reinvestment at same rate, which is unrealistic. Future returns may differ from historical XIRR. Don't extrapolate blindly - a fund with 20% XIRR last 5 years won't necessarily deliver 20% next 5 years. Market conditions change. Use XIRR to evaluate past performance and current trajectory, not as guaranteed future return. Diversify across funds even if one has high XIRR - past performance doesn't guarantee future results. Review XIRR quarterly; sharp drops indicate potential fund issues requiring investigation or switching.