What is XIRR?
XIRR (Extended Internal Rate of Return) is the most accurate method to calculate returns on investments with multiple, irregular cash flows. Unlike CAGR which assumes a single investment, XIRR considers the exact timing and amount of each investment and withdrawal to compute the true annualized return.
XIRR is essential for calculating returns on SIPs, mutual funds with additional purchases, stocks bought at different times, or any investment portfolio with multiple transactions.
XIRR vs CAGR: When to Use Which?
| Scenario | Use CAGR | Use XIRR |
|---|---|---|
| Single lumpsum investment | ✓ | Can use |
| SIP (Systematic Investment) | ✗ | ✓ |
| Multiple purchases at different times | ✗ | ✓ |
| Partial withdrawals/redemptions | ✗ | ✓ |
| Dividend reinvestment | ✗ | ✓ |
How XIRR is Calculated
XIRR finds the rate (r) that makes the Net Present Value (NPV) of all cash flows equal to zero:
Where:
- Cash Flow: Negative for investments, positive for redemptions
- r: The XIRR rate we're solving for
- Date: Date of each cash flow
- First Date: Date of the first cash flow
How to Enter Cash Flows
- Investments (Outflows): Enter as negative amounts or select "Invest" type. These are amounts you paid.
- Redemptions (Inflows): Enter as positive amounts or select "Redeem" type. Include dividends received and current portfolio value.
- Current Value: Always add your current portfolio value as a positive inflow with today's date.
- Dates: Use actual transaction dates for accuracy.
XIRR Calculation Example
You invested ₹10,000/month for 12 months starting Jan 2025, and current value is ₹1,35,000:
| Date | Cash Flow | Type |
|---|---|---|
| 01-Jan-2025 | -₹10,000 | Investment |
| 01-Feb-2025 | -₹10,000 | Investment |
| ... | ... | ... |
| 01-Dec-2025 | -₹10,000 | Investment |
| 10-Jan-2026 | +₹1,35,000 | Current Value |
Result: XIRR = ~22.5% (Total invested: ₹1,20,000, Current: ₹1,35,000, Absolute: 12.5%)
Why XIRR is Higher/Lower Than Expected
- XIRR > Absolute Return: Because later investments had less time to grow, XIRR annualizes the return considering timing.
- Your XIRR ≠ Fund's CAGR: Fund returns assume Day 1 lumpsum. Your returns depend on when you invested.
- Market timing impact: Investing more during market lows = higher XIRR; during highs = lower XIRR.
Frequently Asked Questions (FAQs)
What is a good XIRR for SIP in mutual funds?
For equity mutual fund SIPs over 5+ years, XIRR of 12-15% is considered good. Index fund SIPs typically show 10-12%, while actively managed mid/small cap funds can show 14-18% with higher volatility.
How to calculate XIRR in Excel?
Use =XIRR(values, dates) where values is the range of cash flows (negative for investments, positive for redemptions) and dates is the corresponding dates. Example: =XIRR(A1:A10, B1:B10).
Can XIRR be negative?
Yes, XIRR will be negative if your current value is less than your total investment. This indicates your investment has lost money on an annualized basis.
Why is my SIP XIRR higher than fund's 1-year return?
SIP XIRR considers the timing advantage of rupee cost averaging. Your recent investments had less time to grow but were made at various prices, potentially lowering your average cost. Fund's return assumes lumpsum at start.
How often should I check my XIRR?
For long-term SIPs, check XIRR quarterly or annually. Short-term XIRR can be volatile and misleading. Focus on 3-5 year XIRR for meaningful performance assessment.