Contents
- 1 XIRR Calculator — Annualised Return on Irregular Cash Flows
XIRR Calculator — Annualised Return on Irregular Cash Flows
Calculate accurate annualised returns for SIP, top-ups, partial withdrawals, real estate, ULIP and any irregular investment pattern with custom dates.
Visual Breakdown
What is XIRR?
XIRR (Extended Internal Rate of Return) is the most accurate way to measure the return on investments with IRREGULAR cash flows — like SIPs with varying instalments, lumpsum additions, partial withdrawals, or any non-uniform investment pattern. While CAGR works for single buy-sell, and IRR works for evenly-spaced cash flows, XIRR is the only correct metric for real-world investment patterns.
The Excel Equivalent
In Excel/Google Sheets: =XIRR(values, dates, [guess]) — where values is an array of cash flows (negative = outflow, positive = inflow) and dates is the corresponding date array. The function returns the annualised effective rate that makes Net Present Value of cash flows equal to zero.
When to Use XIRR vs CAGR vs IRR
| Scenario | Best Metric | Why |
|---|---|---|
| One-time buy + one-time sell (e.g., bought stock 2018, sold 2026) | CAGR | Single cash flow pair — CAGR formula handles it exactly |
| Annual fixed investments + final redemption | IRR | Evenly-spaced cash flows — IRR/PMT functions work |
| Monthly SIP + variable top-ups + partial withdrawals | XIRR | Only XIRR handles dates accurately |
| Real estate (down payment + EMIs + sale price) | XIRR | Cash flows at irregular dates |
| Insurance/ULIP (annual premiums + maturity) | XIRR | Reveals true return vs marketing pitches |
| NPS Tier-I (monthly contributions + lump-sum at 60) | XIRR | Long horizon with monthly contributions |
Worked Example — SIP with Top-Up
You’ve been investing in an equity fund:
| Date | Cash Flow (₹) | Action |
|---|---|---|
| 1 Apr 2020 | (50,000) | Lump-sum start |
| 1 May 2020 – 1 Apr 2023 | (5,000)/month × 36 | SIP |
| 1 Apr 2023 – 1 Apr 2025 | (7,000)/month × 24 | Stepped-up SIP |
| 15 Jun 2024 | (1,00,000) | Lump-sum top-up |
| 1 Apr 2026 | +7,50,000 | Full redemption |
Calculating CAGR manually here is impossible. XIRR gives the true annualised return considering each cash flow’s exact date and amount. Result: ~13.2% XIRR — meaning your money grew at the equivalent of a constant 13.2% annual rate.
XIRR Interpretation Guide
| XIRR Range | Assessment |
|---|---|
| > 18% | Excellent — outperforming broader market; review for risk |
| 14-18% | Strong — beats Nifty 50 long-term |
| 11-14% | Good — in line with diversified equity expectation |
| 8-11% | Average — debt-leaning portfolio or weak equity |
| 5-8% | Below par — FD-equivalent; equity fund must be reviewed |
| 0-5% | Poor — barely beating savings rate; major underperformance |
| Negative | Loss — review strategy, asset allocation, fund selection |
Common XIRR Pitfalls
- Forgetting current value: XIRR needs at least one POSITIVE cash flow (treat current holding value as a hypothetical “sale” with today’s date)
- Mixed assets in single calculation: Compute XIRR per investment vehicle, not combined portfolio
- Wrong sign convention: Outflows must be negative, inflows positive — getting this wrong gives nonsense results
- Insurance / ULIP miscount: Include allocation charges, mortality charges, GST as part of cash outflow
- Tax not adjusted: XIRR is pre-tax. For post-tax comparison, subtract LTCG/STCG from final inflow
- Inflation ignored: Nominal XIRR vs Real XIRR — for purchasing power, subtract ~6% inflation
How XIRR is Calculated (Math)
XIRR finds the rate r such that NPV (Net Present Value) of all cash flows equals zero:
Σ [CFi / (1+r)(di-d0)/365] = 0 Where CFi is each cash flow, di is its date, d0 is the first date. The equation has no closed-form solution — solved iteratively using Newton-Raphson or bisection method. Excel uses Newton-Raphson with a guess parameter (default 0.1 = 10%).
XIRR for Comparing Mutual Funds
When comparing two funds with same SIP amount and tenure, XIRR is the only fair benchmark. Fund A might have higher absolute value because you invested more lumpsum; XIRR levels the playing field. Most fund aggregators (Coin, Groww, Kuvera) auto-display XIRR for your portfolio. Pro tip: Compare XIRR with the fund’s benchmark XIRR over same period. If your fund’s XIRR < benchmark XIRR, the fund is failing to add alpha — consider switching to an index fund.
Frequently Asked Questions
Why does Excel show #NUM error for XIRR?
Three common reasons: (1) All cash flows are same sign (need at least one negative AND one positive), (2) Initial guess is too far off — try guess=0.5, (3) The cash flow pattern has no real solution (rare). Solution: verify signs and add a guess parameter.
Can XIRR be negative?
Yes — when total inflows are less than total outflows in present value terms, XIRR is negative. Indicates loss. Common in failed startups, depreciating assets, or poorly-performing investments.
Is XIRR the same as IRR?
Conceptually similar but different math. IRR assumes evenly-spaced cash flows (typically annual). XIRR uses actual dates, making it accurate for irregular intervals. For real investments (SIP, top-ups), always use XIRR.
How is XIRR different from CAGR?
CAGR works for single buy-sell (one outflow, one inflow). XIRR handles multiple cash flows at various dates. For SIP, CAGR is mathematically incorrect; use XIRR.
Should XIRR include taxes?
Default XIRR is pre-tax. For real after-tax return, subtract estimated tax (LTCG @ 12.5%, STCG @ 20%, etc.) from final inflow. This gives ‘effective XIRR’ — what you actually keep.
What is XIRR for index funds (Nifty 50)?
Historically ~13-14% over 10-year periods. Long-term (20+ years): 12-15%. Short-term (1-2 years): highly variable (-20% to +60%). Always benchmark over similar horizon.
Can XIRR be used for real estate?
Yes — input down payment + monthly EMIs as outflows, sale proceeds (net of capital gains tax) as inflow. Don’t forget rent received (if any) as periodic positive inflows. Real estate XIRR usually 6-10% — lower than equity but with leverage benefits.
What’s a ‘good’ XIRR for SIP in equity funds?
12-15% is reasonable long-term. Top-quartile funds can deliver 16-20% in good cycles. Below 10% over 5+ years suggests fund selection issue or poor entry timing — review and possibly switch.
How do I compute XIRR if I don’t know current value?
You need it — XIRR requires latest NAV × units held = hypothetical sale amount on today’s date. Without this final value, you only have outflow history (negative numbers), which gives no meaningful answer.
Why is XIRR higher than CAGR sometimes?
Because of timing benefits. If your large investments happened during market lows, they earned higher returns — boosting XIRR. CAGR (using start and end values only) cannot capture this timing benefit.
Can I use XIRR for crypto investments?
Yes — same approach. Note: Indian crypto tax is 30% flat on each sale, no setoff. After-tax XIRR will be ~30% lower than pre-tax due to taxation cycle.
Does insurance / endowment plan need XIRR?
Absolutely yes. Insurance agents often quote ‘maturity benefit’ as if it’s profit. XIRR reveals true return — usually 4-6% for endowment, less than PPF (7.1%). XIRR exposes whether the policy is a good investment.