Data last verified: June 2026
How to Calculate XIRR in Excel: Step-by-Step Guide for SIP Returns
Learn how to calculate XIRR in Excel with our easy step-by-step guide. Master the XIRR formula, avoid common mistakes, and accurately track your mutual fund SIP returns.
Jashmin is a finance professional and founder of AbacusHand. She specialises in EMI & loan planning, income tax under old and new regimes, and SIP investment analysis for Indian households. Every calculator and article on AbacusHand is personally reviewed by her for accuracy.
Tracking the actual returns of your Systematic Investment Plan (SIP) is crucial for evaluating your mutual fund portfolio. While investment apps show you the XIRR automatically, many investors prefer to calculate it manually in Microsoft Excel to analyze custom cash flows, track multiple portfolios, or verify their app's calculations. If you've been searching for how to calculate XIRR in Excel, this step-by-step guide will show you exactly how to use the XIRR formula, structure your data, and avoid the common mistakes that lead to wrong results.
What is the XIRR Function in Excel?
Excel has a built-in financial function specifically designed for this: `=XIRR()`. XIRR stands for Extended Internal Rate of Return. Unlike the standard IRR function, which assumes cash flows happen at regular intervals (like exactly every month), the XIRR function allows for irregular intervals. This makes it the perfect tool for calculating mutual fund returns, as SIP dates might occasionally shift due to weekends or holidays, and you might make lumpsum top-ups at random times.
Step-by-Step Guide to Calculate XIRR in Excel
Follow these four simple steps to calculate your SIP returns accurately:
- Step 1: Create two columns in Excel. Label column A as 'Dates' and column B as 'Cash Flows'.
- Step 2: Enter all your investment dates in column A and the corresponding invested amounts in column B. Crucially, all investments (cash outflows) must be entered as NEGATIVE numbers (e.g., -5000).
- Step 3: In the next empty row, enter today's date in column A and the current total value of your portfolio in column B. This final value must be entered as a POSITIVE number (e.g., 75000).
- Step 4: Click on an empty cell and enter the formula `=XIRR(B1:B10, A1:A10)` (adjust the range to match your actual data rows) and press Enter. Format the cell as a percentage to see your annualized return.
Example: Calculating XIRR for a Monthly SIP
Let’s say you started a ₹5,000 monthly SIP on the 1st of every month from January 2025 to December 2025. On June 10, 2026, your current portfolio value is ₹78,500. Here is how you should structure your Excel sheet to get the correct XIRR formula result:
Sample Data Structure for Excel:
- Cell A1: 01-01-2025 | Cell B1: -5000
- Cell A2: 01-02-2025 | Cell B2: -5000
- Cell A3: 01-03-2025 | Cell B3: -5000
- ... (continue entering dates and -5000 for all 12 months)
- Cell A13: 10-06-2026 | Cell B13: 78500 (Note: This final value is positive)
In cell B14, you would enter the formula `=XIRR(B1:B13, A1:A13)` and press Enter. Excel will instantly calculate and display your annualized XIRR (approximately 12.4% in this scenario). You can use this exact method to calculate SIP returns in Excel for any number of installments.
Common Mistakes That Give Wrong XIRR Results
The XIRR calculation in Excel is highly sensitive to how your data is formatted. If your result looks like `#NUM!` or gives a wildly inaccurate percentage, check for these three common errors:
Avoid these pitfalls when using the Excel XIRR function:
- Forgetting the Negative Sign: This is the #1 mistake. All money going out of your pocket (SIPs, lumpsum investments) must be negative. Only the final withdrawal or current portfolio value should be positive.
- Incorrect Date Formats: Excel must recognize the entries in column A as actual dates, not text. If you typed dates as text, the formula will fail. Use the `DATE()` function or ensure your cells are formatted as 'Date'.
- Including Empty Cells or Zeroes: If you select a range that includes blank rows or rows with '0' investment, Excel treats '0' as a cash flow on that date, which will severely distort your XIRR calculation.
Pro Tip: If you are calculating returns for a single lumpsum investment (no SIPs), do not use XIRR. Instead, use the CAGR formula: `=(Ending Value / Beginning Value)^(1/Years) - 1` or use Excel's built-in `=RRI()` function.
Shortcut: Use an Online XIRR Calculator
While Excel is a powerful tool for detailed portfolio tracking, it can be tedious if you just want a quick answer. If you don't want to deal with date formats and negative signs, our free online XIRR calculator does the complex math for you in seconds. Simply enter your total invested amount, current market value, and investment duration to instantly find your annualized returns. You can also project your future wealth using our SIP calculator.
Skip the Excel formulas and find out exactly how much your SIPs have earned you annually.
Calculate XIRR NowFrequently Asked Questions
The exact formula is `=XIRR(values, dates)`. The 'values' argument should be a range of cells containing your cash flows (investments as negative numbers, final value as positive), and the 'dates' argument should be a corresponding range of cells containing the dates of those cash flows.
The `#NUM!` error usually occurs for two reasons: 1) Excel does not recognize your date entries as actual dates (they might be formatted as text), or 2) there are no negative cash flows (investments) in your data range. Ensure all investments are negative and dates are properly formatted.
While you technically can, it is not the standard approach. For a single lumpsum investment, it is much easier to calculate the CAGR (Compound Annual Growth Rate) using the formula `=(End Value / Start Value)^(1/Years) - 1`. XIRR is specifically designed for multiple, staggered cash flows like SIPs.
Yes, absolutely. In financial mathematics, cash outflows (money leaving your bank account to invest) must be represented as negative numbers, and cash inflows (the final portfolio value or withdrawals) must be positive. If you enter all numbers as positive, the XIRR formula will not work.