How to Calculate XIRR in Excel: The Ultimate Guide for SIP Investors
Most Indian investors look at their mutual fund statements and see three numbers: “Invested Value,” “Current Value,” and “Absolute Return.” While these provide a quick snapshot, they are often mathematically misleading for anyone doing a monthly SIP (Systematic Investment Plan).
If you invest ₹10,000 every month for 5 years, every installment spends a different amount of time in the market. The ₹10,000 you invested in 2021 has compounded for 60 months, but the ₹10,000 you invested last month has only been there for 30 days.
How do you calculate a single, precise annual return percentage for all these different timelines? The answer is XIRR (Extended Internal Rate of Return).
In early 2026, as Indian retail portfolios become more complex with multiple SIPs, SWPs, and top-ups, mastering XIRR is the only way to know if your mutual fund is actually beating a simple bank FD.
In this exhaustive 2000+ word guide, we will teach you exactly how to calculate XIRR in Excel and Google Sheets, deconstruct the math of “Time-Weighting,” and troubleshoot common calculation errors that frustrate even experienced investors.
1. Why CAGR Fails (and why XIRR is the Gold Standard)
CAGR (Compound Annual Growth Rate) is the perfect tool for a “One-Way” journey. If you invest a Lump Sum of ₹1 Lakh today and it becomes ₹2 Lakhs in 6 years, CAGR tells you your annual growth was exactly 12.25%.
However, CAGR assumes a single entry point. A SIP is a series of entries.
- The Conflict: If your portfolio shows a 20% Absolute Return, you might think you are a genius. But if most of that gain came from a one-time rally in the last 3 months, your “Annualized” performance might be much lower. Conversely, in a market correction, your XIRR might be positive even if your Absolute Return is negative.
- The Solution: XIRR accounts for the Date and Cash Flow Direction of every single rupee. It is the “True Rate” of your wealth creation.
2. A 3-Step Masterclass: XIRR in Excel & Google Sheets
To calculate XIRR, you need two columns of data: Dates and Cash Flows.
Step 1: The “Money Leaving Pocket” Column (Investments)
In Column A, enter the dates. In Column B, enter the amounts. CRITICAL RULE: All money leaving your bank account (Investments/SIPs) must be entered as Negative Numbers (-).
- A1: 01/Jan/2025 | B1: -10000
- A2: 01/Feb/2025 | B2: -10000
- A3: 01/Mar/2025 | B3: -10000
Step 2: The “Money Entering Pocket” Column (Redemptions)
If you withdrew money for a personal emergency, enter it as a Positive Number (+) on that specific date.
- A8: 15/Aug/2025 | B8: 25000 (Withdrawal)
Step 3: The “Closing Balance” (Current Value)
At the very bottom of your list, you must provide the Current Market Value of your entire portfolio as a Positive Number (+) using Today’s Date.
- A13: 16/Apr/2026 | B13: 145000 (Current Value)
3. Applying the Formula
Once your table is ready, click on an empty cell and type:
=XIRR(B1:B13, A1:A13)
- Values Range: All amounts from your first SIP to your current portfolio value.
- Dates Range: All corresponding dates.
Important: Format the cell as a Percentage (%) with at least two decimal places. If Excel returns a number like 0.1425, it means your XIRR is 14.25%.
4. Troubleshooting: Fixing the “#NUM!” and “#VALUE!” Errors
XIRR is a sensitive formula. Here is how to fix the 5 most common “Excel Tantrums”:
- The “All Positive” Error: If you don’t use the minus sign (-) for your investments, Excel cannot find a “Rate” and will return #NUM!. At least one value must be negative and one must be positive.
- The “Text Date” Error: If your dates are stored as text (e.g., ‘Jan 1st’), Excel will return #VALUE!. Ensure Column A is formatted as Short Date.
- The “Non-Chronological” Date: While XIRR can handle mixed dates, it is best practice to keep them in order. If your “Current Value” date is earlier than a SIP date, the formula will break.
- The “Empty Cell” Trap: Do not have empty rows between your data points. The range must be contiguous.
- The “Zero Transaction” Error: If you have zero values in the range, it can sometimes confuse the algorithm. Filter out any ₹0.00 entries.
5. Advanced Scenarios: SWP and Dividends
Scenario A: Systematic Withdrawal Plan (SWP)
If you are a retiree withdrawing from your corpus, XIRR is the only way to check your performance.
- Investments: Negative (-)
- Monthly SWP Payouts: Positive (+)
- Final Balance: Positive (+) XIRR will calculate the “Internal Return” adjusted for the money you’ve already spent!
Scenario B: Dividend Payouts (IDCW)
If you are in a “Payout” plan, every dividend that hits your bank account should be added as a Positive Number on that date. This ensures your XIRR reflects the “Total Shareholder Return.”
6. Benchmarking: Is 12% XIRR Good in 2026?
In the context of the Indian economy in 2026:
- Below 7%: You are failing to beat a Bank FD. You are essentially losing money after inflation.
- 8% - 11%: You are matching the Debt market. This is “Safe” but won’t make you a Crorepati.
- 12% - 15%: This is the “Wealth Creation Zone.” Most quality Index and Flexi-cap funds aim for this range.
- 18% - 25%: Typical of a roaring Bull market. Do not assume this will last forever; plan your retirement at a conservative 12% XIRR.
Calculate your potential for these benchmarks using our SIP Returns Calculator.
Frequently Asked Questions (FAQs)
1. What is the difference between IRR and XIRR?
IRR (Internal Rate of Return) assumes that your cash flows happen at regular fixed intervals (exactly 30 days apart). XIRR (Extended IRR) handles irregular dates (like a holiday on a Sunday or a mid-month top-up), making it 100x more accurate for real-world mutual funds.
2. Is the XIRR shown in my app (like Groww/Zerodha) accurate?
Generally, yes. However, apps usually use the NAV of T-1 or T-2 days. If the market is moving 2% a day, your app’s XIRR will fluctuate. Calculating it yourself in Excel gives you “Point-of-Execution” accuracy.
3. Does XIRR include the mutual fund’s expense ratio?
Yes. Since the NAV of a fund is calculated after deducting the expense ratio, the XIRR you calculate based on your NAV purchases automatically accounts for all fees.
4. Why is my XIRR much higher than my Absolute Return?
This happens when you have a small portfolio that starts performing very well recently. Because the “Time Factor” is small, the “Annualized Rate” (XIRR) shoots up. As your portfolio ages (5-10 years), XIRR and Absolute Return will start moving closer together.
5. How do I calculate XIRR in Google Sheets?
The formula is identical: =XIRR(B1:B13, A1:A13). Google Sheets is generally more forgiving with date formats than Excel, making it a better choice for beginners.
6. Do I need to include the Stamp Duty in my XIRR?
No. Since you are calculating based on the “Amount Debited” from your bank account (which already includes the 0.005% stamp duty), your XIRR is already “Net of Cost.”
7. Does XIRR show returns after tax?
No. Standard XIRR calculations are “Pre-Tax.” To find your post-tax return, you must manually deduct the 12.5% Long-Term Capital Gains (LTCG) tax from your final “Current Value” before running the formula.
Conclusion: Data vs. Intuition
In the world of finance, Intuition is a liar. Data is the truth.
You might feel like your fund is great because it grew by ₹50,000 this year, but if your XIRR is only 7%, you are underperforming the risk you are taking.
Mastering XIRR in 2026 allows you to:
- Audit your Fund Manager: Is the fund meeting its 12% target?
- Compare Strategies: Is your SIP beating your Lump Sum?
- Plan your Future: Use accurate historical XIRR to project your Retirement Corpus.
The most successful investors in India are not the luckiest—they are the most mathematically precise.
Ready to simplify the math? Skip the spreadsheet and use our professional SIP Returns Calculator to get instant XIRR projections and visualized growth charts today.
Disclaimer: HelpForFinance is an educational resource. XIRR is a mathematical representation of past performance and is not a guarantee of future returns. Mutual fund investments are subject to market risks.