The Extended Internal Rate of Return (XIRR) is a single rate of return that, when applied to each instalment (and any redemptions), yields the current value of the entire investment.

XIRR stands for the individual rate of return. It’s your real investment return.

XIRR is a tool for calculating returns on assets where many transactions occur at different times.

Calculating returns in the case of a SIP is a little more difficult because there are multiple investments (and therefore, multiple purchase prices) and various periods for each instalment. Mutual fund SIP returns are generally expressed in terms of XIRR.

Why does XIRR make sense in mutual funds?

If you spend Rs 4,000, Rs 9,000, Rs 5,000, Rs 4,000, and Rs 6,500 in SIPs over five years and earn Rs 53,000 at the end of the tenure, your return on investment is 22%.

The IRR is the resultant amount. This concept is used to determine how much you have received from your savings in the case of evenly distributed spending.

However, unlike mutual funds, contributions are generally not spread equally as they are in mutual funds. When it comes to mutual funds, you usually invest and redeem at irregular intervals.

It will result in cash inflows and outflows at various points in time. In this case, in addition to the amount invested, the length of time spent on the investment is also important in determining the result.

You may apply the principle of XIRR here.

So, when your cash flows (investments or redemptions) are distributed over a period, XIRR is a good feature to use to measure returns.

If you’re investing in mutual funds via SIP or lump sum or redeeming via SWP or lump-sum, XIRR will handle all scenarios and help you measure a combined return based on the timings of your investments and withdrawals.

Step-by-Step process to calculate XIRR in excel

1. Make a single column for all of your purchases. Outflows, such as investments and acquisitions, will be marked as market negative, while all inflows, such as redemptions, will be marked as market positive.
2. Attach the transaction’s accompanying date to the next column.
3. Mention the prevailing value of your holding and the date in the last row.
4. In Excel, use the XIRR function, which looks like this: = XIRR (values, date, Guess).
5. Choose values for a series of cash flows that correspond to a payment schedule in dates, where the date columns represented the date when the first investment was made and when the cash flows were obtained. The Guess parameter is optional (if you do not put any value, excel use a value of 0.1).

XIRR is an excellent tool for calculating real-world investment returns. CAGR is important to consider when choosing a mutual fund, but XIRR is crucial when evaluating your investment returns.

IRR is used for investments when cash flows are uniformly distributed in time, although most investments are not as evenly spaced as in the case of mutual funds.