XIRR Guide: How to Calculate True Returns on SIPs and Irregular Investments

The SIP Return Fallacy: Why CAGR Lies to You

In the modern era of mutual fund investing, nearly every salaried Indian has an active Systematic Investment Plan (SIP). We diligently set up auto-debits for the 5th or 10th of every month, letting our money flow into equity mutual funds. But when it comes to measuring how well our money is actually performing, we hit a mathematical wall. Most investors rely on the standard Compound Annual Growth Rate (CAGR) to compare their mutual funds against fixed income options like FDs or PPF. However, using CAGR for an SIP portfolio is a critical mathematical error. CAGR is designed strictly for a single, one-time transaction—one entry point and one exit point. In an SIP, you have multiple entry points occurring monthly over several years. If you try to calculate CAGR on your total SIP, the formula will treat it as if your entire capital was invested from day one, resulting in a return figure that is heavily understated. To find the true, accurate annualized rate of return on your systematic investments, you must use **XIRR (Extended Internal Rate of Return)**.

This comprehensive guide details the mechanics of XIRR, breaks down its complex mathematical equations, provides detailed worked examples, compares XIRR with CAGR, explains how to calculate it in Excel or Google Sheets, and outlines how it applies to real-world mutual funds. Check your portfolio's true growth using our interactive XIRR Calculator alongside this guide.

The Mathematics of XIRR Explained

XIRR is an extension of the Internal Rate of Return (IRR). While standard IRR assumes that all cash flows occur at equal time intervals (e.g., exactly once a year), XIRR is designed for **irregular time intervals**, tracking the exact calendar date of every single transaction. XIRR is defined as the discount rate that makes the Net Present Value (NPV) of all cash flows equal to zero.

The mathematical equation solved by XIRR is:

0 = ∑ [CF_i / (1 + R)^{(d_i - d₁) / 365}]

Where:

• CF_i: The cash flow amount for transaction i. Inflows (withdrawals, dividends) are positive (+), while outflows (investments, SIP debits) are negative (-).
• d_i: The exact date of the transaction i in the `DD-MM-YYYY` format.
• d₁: The date of the first transaction (the starting point).
• R: The XIRR (Internal Rate of Return) we want to solve.

Because the rate R appears in the exponent and is summed across multiple periods, this equation cannot be solved algebraically. Instead, it is solved using numerical iteration methods (like the Newton-Raphson method) until the sum converges to zero. Check how this fits with long-term goals in our Goal-Based SIP guide.

Worked Example #1: The 3-Month SIP Return Paradox

Let's run a simple, highly detailed worked example to understand the difference between CAGR and XIRR. Suppose Sagar decides to start a short-term monthly SIP of ₹10,000. He makes exactly three monthly contributions, and then sells his entire portfolio on the fourth month. Here are the exact transactions:

• Date 1 (01-Jan-2026): Invests ₹10,000 (Cash Flow = -₹10,000)
• Date 2 (01-Feb-2026): Invests ₹10,000 (Cash Flow = -₹10,000)
• Date 3 (01-Mar-2026): Invests ₹10,000 (Cash Flow = -₹10,000)
• Date 4 (01-Apr-2026): Redeems entire portfolio for ₹32,000 (Cash Flow = +₹32,000)

Let's look at the absolute return, CAGR, and XIRR for Sagar's portfolio:

1. Absolute Return:

Total invested = ₹30,000. Profit = ₹2,000. Absolute Return = (2,000 / 30,000) × 100 = 6.67%.

2. Standard CAGR:

Treating it as a single block of ₹30,000 growing to ₹32,000 over 3 months (0.25 years):
CAGR = [(32,000 / 30,000)^{(1 / 0.25)}] - 1 = (1.0667)⁴ - 1 = 29.46% per year.

3. The True XIRR (Annualized):

Using the XIRR formula, we find the discount rate that balances these transactions:
0 = -10,000 + [-10,000 / (1 + R)^31/365] + [-10,000 / (1 + R)^59/365] + [32,000 / (1 + R)^90/365]
Solving this numerically gives R = 47.93% per year!

Why the massive difference? CAGR assumes the entire ₹30,000 was working in the market for all 3 months. But Sagar's second ₹10,000 was in the market for only 2 months, and his third ₹10,000 was in the market for only 1 month! To make a ₹2,00,000 profit on money that was in the market for an average of just 2 months, the fund had to compound at an astounding annualized rate of 47.93%! This shows how CAGR heavily penalizes your investments by ignoring the time they actually spent in the market. To learn about compounding frequencies, read our simple vs compound interest guide.

Worked Example #2: High-Growth Equity Mutual Fund (5-Year SIP)

Let's look at a long-term scenario. Ramesh starts a monthly SIP of ₹5,000 in a small-cap mutual fund for 5 years (60 months). Let's see his wealth accumulation and return metrics assuming a 15% long-term XIRR:

1. Monthly Contribution: ₹5,000/month
2. Total Capital Invested: ₹3,00,000
3. Final Portfolio Value: ₹4,48,408
4. Absolute Return: [(4,48,408 - 3,00,000) / 3,00,000] × 100 = 49.47%
5. XIRR: **15.00% per year** (True annualized return)
6. Calculated CAGR (False Metric): If we incorrectly treat it as a lump-sum of ₹3L growing to ₹4.48L over 5 years: CAGR = [(4,48,408 / 3,00,000)^(1/5)] - 1 = 8.37% per year.

The Takeaway: If Ramesh looked at CAGR, he would think his small-cap fund performed at a terrible 8.37%—which is barely better than a bank Fixed Deposit—and might be tempted to sell! But in reality, his true annualized compounding return was a stellar 15.00%. This proves why XIRR is mandatory to avoid making wrong portfolio decisions. Plan your tax savings to fuel these high-growth funds using our Section 80C guide.

CAGR vs XIRR: The Core Battle

Pro Tips to Calculate and Track Your XIRR

• How to use Excel/Google Sheets XIRR: Set up two columns. In Column A, write the exact dates of all transactions in `MM/DD/YYYY` format. In Column B, write the corresponding amounts. **Crucial rule:** Write all investments (outflows) as negative numbers (e.g., -5000) and your current portfolio value on the last date as a positive number (e.g., 448408). Use the formula `=XIRR(B1:B61, A1:A61)`. Multiply by 100 to get the percentage!
• Always Keep the Current Date updated: When calculating XIRR, the very last entry must represent your current portfolio value with today's date to ensure the calculations are accurate.
• Factor in Partial Redemptions: If you withdraw money from your fund, write it as a positive cash flow on the date of withdrawal. The XIRR formula will automatically factor in the redemption and give you the correct net return. Check take-home pay structures in our take-home salary calculator.

Frequently Asked Questions