In a hurry? Read this summary:

• XIRR (extended internal rate of return) shows the real return by timing every cash inflow and outflow, not just average growth.
• For uneven SIPs, XIRR reveals the true outcome.
• Favoured for accuracy and consistency, XIRR handles withdrawals, dividends and partial redemptions.

XIRR in mutual fund

When you invest a fixed amount paid at regular intervals over the course of the year, you can easily calculate the compound annual growth rate (CAGR) on your investment. However, when an investment involves varying cash inflows (such as SIP or cash outflows such SWP, dividends or any partial redemptions) it’s a lot harder to get true annual returns.

This is where XIRR (extended internal rate of return) can be a helpful tool for investors.

What is XIRR in mutual funds?

XIRR, or Extended Internal Rate of Return, is a financial metric that helps investors understand the annualized rate of return for a series of cash flows that occur at irregular intervals. The way investments are structured in a SIP makes it difficult to clearly understand the rate of return, however XIRR simplifies the process. It takes into account the dates of investment and withdrawal, the timing of the cash flow, and any fees and charges associated with he investment. These factors give investors a better picture of how their investment has performed.

XIRR is a useful metric for investors who favour mutual funds/SIPs, but it may also make sense for private equity and venture capital investments where cashflow may vary.

How to find XIRR for mutual funds?

The mathematical formula for calculating XIRR in mutual funds is expressed as:

This formula is complex and may not be practical for layman investors looking to evaluate their portfolio. Fortunately, you can use spreadsheets like Microsoft Excel to easily find XIRR for your SIPs.

Find XIRR using excel

The following are the steps to calculate XIRR in excel

Step 2: Enter the respective dates in another column

Step 3: In the last row, enter the current value of the holding and the current date

Step 4: Use the XIRR formula in Excel: =XIRR(values, date, guess).

Here, values contain the range of transactions, and dates contain the range of dates on which these transactions happened. The guess is optional, and Excel picks the default 0.1 value for it if nothing is entered.

XIRR in Excel: 4 quick steps

Example of XIRR in excel

Let’s say you have made the following SIP at the given dates.

In excel, do the following:

In column B2, add -8000 (negative because it’s a cash outflow)

In column B3, add -6000

In column B4, add -5000

In column B5, add -9000

In column B6, add 6000

In column B6, add 35000

Do a similar exercise for dates and add them in column A for the respective transactions, as shown below in the image.

Now use the XIRR formula.” =XIRR(B2:B7, A2:A7,0.1) ” and hit enter to get the XIRR. To get the XIRR in percentage, format the cell to a percentage data type.

The XIRR is 69.59%.

Benefits of using XIRR in mutual funds

Using XIRR comes with its share of benefits.

• Accuracy: XIRR provides a great deal of accuracy for all the cash flows, factoring in their exact timings.
• Steadiness: It always provides a consistent return measurement, even when cash flows are irregular, unlike CAGR, which may mislead with wrong returns for irregular cash flows.
• Adaptability: It provides versatility in calculating returns with any investment type, irrespective of the cash flows’ irregularities, making it a reliable tool to evaluate investment performance.
• Clarity: XIRR provides transparent return evaluations by counting on actual cash flows.

Try our free SIP calculator.

How does XIRR compare to similar metrics?

XIRR is not the only metric investors may use to evaluate returns, but it’s usually the one that offers the most accurate results. Learn how XIRR compared to other options like IRR, CAGR, etc.

• Annualized Return (CAGR): CAGR (Compound Annual Growth Rate) shows the annual growth rate of an investment over a given term. The limitation of this metric is that it assumes the investment compounds and there are no additional withdrawals or contributions. It’s suitable for lump sum investments but not SIPs.
• Internal Rate of Return (IRR): IRR is the discount rate that makes the net present value (NPV) of all cash flows equal to zero. IRR can be used in projects where cash flows happen at fixed intervals, but if intervals vary, IRR becomes inaccurate. IRR can be used for SIPs, but XIRR provides a more precise result.
• NPV (Net Present Value): NPV tells you the current value of all future cash flows, discounted at a chosen rate (often a required rate of return). NPV can help you determine how much your investment is worth, but it does not give you an annualized rate. Additionally, you must know or assume a discount rate.

Ultimately, if your investment involves irregular deposits or withdrawals, XIRR is the gold standard. It reflects your true, annualized return, accounting for both timing and size of every cash flow and offers the most complete picture for investors.

To learn more, connect with our relationship managers or visit SC Invest and start building your mutual fund portfolio.