XIRR for SIP returns
XIRR (Extended Internal Rate of Return) is the correct method to compute annualised returns on SIP investments, properly accounting for the time-value of each periodic instalment. Unlike CAGR (which assumes single-investment-single-withdrawal), XIRR handles the multiple-cash-flow nature of SIPs.
Why XIRR for SIPs
SIP investing involves:
- Multiple monthly investments: Each at different NAV.
- Different holding periods per instalment: Earliest instalment has longest holding period; latest has shortest.
- Different time-value of cash flows: Money invested 5 years ago vs 1 year ago.
CAGR over the full SIP period would treat all the invested money as if it were a single lump-sum at SIP start, materially misstating the actual return.
XIRR formula
XIRR is computed iteratively:
- Define all cash flows (investments as negative, redemptions as positive).
- Define corresponding dates.
- Find the annualised rate such that the net present value of all cash flows equals zero.
Excel/Google Sheets:
=XIRR(values, dates)
Worked example: 5-year SIP
Rs 10,000 monthly SIP from January 2020 to December 2024 + final value at December 2024:
- 60 instalments of -Rs 10,000 each (on 1st of each month).
- Final redemption value: Rs 9,50,000 on 31 December 2024 (estimated based on assumed 15% CAGR equity-fund performance).
Cash flow table
| Date | Amount |
|---|---|
| 1 January 2020 | -10,000 |
| 1 February 2020 | -10,000 |
| … | … |
| 1 December 2024 | -10,000 |
| 31 December 2024 | +9,50,000 |
XIRR computation
Excel formula: =XIRR(A1:A61, B1:B61)
XIRR ≈ 17.5% per year.
Comparison with CAGR
CAGR-style computation:
- Total invested: Rs 6,00,000.
- Final value: Rs 9,50,000.
- Gross return: Rs 3,50,000 / Rs 6,00,000 = 58.3%.
- Annualised over 5 years using CAGR: (1.583)^(1/5) - 1 = 9.6%.
The CAGR understates the actual annualised return because it doesn’t account for the rupee-cost-averaging.
Practical interpretation
For SIP investors, XIRR represents:
- The annualised return that justifies each monthly investment.
- The effective rate of return per rupee per year.
- The performance metric most accurate for SIP-based portfolios.
When to use CAGR for SIPs
CAGR can still be useful for:
- Comparing SIP vs lump-sum (where lump-sum has natural CAGR).
- Quick approximation: Where exact XIRR is not critical.
For accurate SIP performance measurement, XIRR is the appropriate metric.
Real-world considerations
Direct-plan platform reporting
Most major direct-plan platforms (Zerodha Coin , Groww , Kuvera , ET Money ) and AMC platforms report SIP returns using XIRR.
Tax statements
Tax statements typically report capital gains by lot, not XIRR. Both are useful but for different purposes:
- XIRR: Investment performance measurement.
- Tax statements: Tax computation.
Multi-scheme XIRR
For investors with multiple SIPs across schemes:
- Per-scheme XIRR for individual fund performance.
- Combined XIRR for total portfolio performance.
The Consolidated Account Statement (CAS) provides per-scheme data needed for XIRR calculation.
See also
- Mutual funds in India
- SIP
- CAGR vs XIRR
- SIP tax FIFO
- SWP
- STP
- Rolling trailing returns
- Sharpe ratio
- Consolidated Account Statement (CAS)
- Step-up SIP
External references
References
- Excel/Google Sheets XIRR function documentation.
- CFA Institute curriculum on IRR and XIRR.