XIRR Calculator India 2026
Calculate true annualized return with 4 modes โ True XIRR (add custom date + amount rows with Newton-Raphson solver), SIP XIRR Quick Mode (vs CAGR comparison), XIRR vs CAGR vs Absolute Return, and What-If Analyser (reverse-solve target XIRR). Covers the XIRR formula, Excel/Sheets guide, and negative XIRR meaning.
What Is XIRR (Extended Internal Rate of Return)?
XIRR (Extended Internal Rate of Return) is the annualized rate of return that accounts for the exact timing and amount of every cash flow in your investment. It is the most accurate metric for measuring returns on SIPs, portfolios with multiple transactions, and any investment where money enters or exits at irregular intervals.
Unlike simple returns or CAGR (which assumes all money was invested at once), XIRR mathematically weights each cash flow by its actual holding period. This makes it the gold standard for Indian mutual fund investors who predominantly use SIPs.
XIRR Formula & Newton-Raphson Method
XIRR finds the rate r that makes the Net Present Value (NPV) of all cash flows equal zero:
Where: Ci = Cash flow amount (negative for investments, positive for redemptions), di = Date of cash flow, d0 = Date of first cash flow, r = XIRR
This equation cannot be solved algebraically because r appears as an exponent for multiple terms. The Newton-Raphson iterative method is used:
- Start with an initial guess (typically 10%)
- Calculate NPV at this rate
- Calculate the derivative (slope) of NPV with respect to rate
- Adjust: rnew = rold − NPV / NPV′
- Repeat until NPV ≈ 0 (within 0.000001 tolerance)
This typically converges in 10–20 iterations. Excel, Google Sheets, and our calculator all use this method internally.
XIRR vs CAGR vs IRR โ Complete Comparison
| Factor | XIRR | CAGR | IRR |
|---|---|---|---|
| Full Name | Extended Internal Rate of Return | Compound Annual Growth Rate | Internal Rate of Return |
| Cash Flows | Multiple, irregular dates | Single invest + single final value | Multiple, regular intervals |
| Date Handling | Exact dates (day-level) | Only start & end dates | Equal-period intervals |
| Formula | NPV = 0 (Newton-Raphson) | (FV/PV)1/n − 1 | NPV = 0 (periodic) |
| Best For | SIPs, portfolios, SWPs | Lump sum, FD, PPF | EMIs, annuities |
| Result | Annualized % | Annualized % | Per-period % |
| Accuracy for SIP | Most accurate | Misleading (understates) | Only if equal intervals |
How to Calculate XIRR for SIP โ Worked Example
12-Month SIP of โน10,000/month โ Current Value โน1,35,000
| Date | Cash Flow | Type |
|---|---|---|
| 10-Apr-2025 | −โน10,000 | SIP Installment 1 |
| 10-May-2025 | −โน10,000 | SIP Installment 2 |
| 10-Jun-2025 | −โน10,000 | SIP Installment 3 |
| ... (9 more monthly installments) ... | ||
| 10-Mar-2026 | −โน10,000 | SIP Installment 12 |
| 30-Mar-2026 | +โน1,35,000 | Current Portfolio Value |
CAGR = (1,35,000/1,20,000)1/1 − 1 = 12.5% (treats all โน1.2L as invested on day 1)
XIRR = ~25.1% (correctly weights each installment’s holding period)
How to Use XIRR in Excel & Google Sheets
Step-by-Step
- Column A: Enter dates of all investments (SIP dates) in DD/MM/YYYY format
- Column B: Enter amounts — negative (−) for investments, positive (+) for redemptions/current value
- In the last row: Enter today’s date + current portfolio value as positive
- In any empty cell:
=XIRR(B2:B13, A2:A13) - Format the cell as Percentage
#NUM! error? (1) Ensure at least one negative and one positive value, (2) Check dates are valid (not text), (3) Try adding a guess: =XIRR(B2:B13, A2:A13, 0.1), (4) Use DATE(year,month,day) function for dates.
When to Use XIRR vs CAGR โ Decision Guide
| Scenario | Use This | Why |
|---|---|---|
| Monthly SIP in mutual fund | XIRR | Multiple cash flows at different dates |
| One-time lump sum investment | CAGR | Single investment, single value |
| SIP + additional lump sums | XIRR | Irregular amounts and dates |
| FD maturity calculation | CAGR | Known start amount and maturity |
| SWP with withdrawals | XIRR | Multiple outflows at different dates |
| Comparing fund managers | XIRR | Time-weighted gives true performance |
| Entire portfolio tracking | XIRR | Combines all assets, all transactions |
Negative XIRR โ What It Means & When to Worry
A negative XIRR simply means your current portfolio value is less than what you invested. Common scenarios:
| Scenario | Typical XIRR | Action |
|---|---|---|
| New SIP (1–6 months) in falling market | −10% to −30% | Normal; continue SIP (rupee cost averaging) |
| SIP 1–2 years during bear market | −5% to −15% | Normal; historical data shows recovery in 3–5 years |
| SIP 3+ years still negative | −5% to −10% | Review fund selection; consider switching to index fund |
| Individual stock crashed | −30% to −80% | Evaluate fundamentals; consider booking loss for tax harvesting |
What Is a Good XIRR for Indian Mutual Funds?
| Fund Category | 5-Year XIRR (Typical) | 10-Year XIRR (Typical) |
|---|---|---|
| Large Cap / Index (Nifty 50) | 10–14% | 11–13% |
| Mid Cap | 14–20% | 13–17% |
| Small Cap | 15–25% | 14–18% |
| Flexi Cap | 12–18% | 12–15% |
| Balanced Advantage | 8–12% | 9–11% |
| Debt / Liquid | 5–7% | 6–8% |
| ELSS (Tax Saver) | 12–18% | 12–15% |
Common XIRR Mistakes
- Wrong sign convention — Investments MUST be negative, current value MUST be positive. Swapping signs gives wrong results or #NUM! error.
- Missing the final value row — The last row must be today’s date + current portfolio value. Without it, XIRR cannot converge.
- Using CAGR for SIPs — CAGR treats all your SIP money as invested on day 1, massively understating actual performance. Always use XIRR for SIPs.
- Over-interpreting short-period XIRR — XIRR of 50% over 2 months doesn’t mean 50% annual return. It’s an annualized projection that may not sustain.
- Ignoring dates — XIRR is date-sensitive. A 1-day error in recording can change results, especially for short holding periods.
- Not including dividends/bonuses — If your fund pays dividends or you received bonuses, include them as positive cash flows on their payment dates for accurate XIRR.