However, have you ever wondered what the right method to calculate the return on mutual fund investments is?

Investors can use two methods, namely (CAGR- Compound Annual Growth Rate) and XIRR (Extended Internal Rate of Return). The CAGR method does not allow calculating returns for multiple cash flows. However, by following the XIRR method, one can easily calculate mutual fund returns for multiple cash flows. 

Read on to know what is XIRR in mutual funds, its importance, and the calculation process!

XIRR, or Extended Internal Rate of Return, is a single rate of return that is used when investors make multiple investments at different points of time in an investment vehicle such as a mutual fund.

This single rate of return is applied to all transactions (including investment and redemption) to calculate the current rate of return and the current value of the entire investment.

XIRR is the real investment return.

[Source]

XIRR is important in mutual fund investment because investors can use this formula in an excel sheet and put data of uneven cash flow intervals and get correct results.

XIRR is a modified version of IRR with added flexibility. The formula of XIRR can assign particular dates to each cash flow and calculate returns accurately.

[Source]

While investing in a mutual fund, investors can put a lump sum amount that involves two cash flows. It means they will have a single cash outflow (in the form of investment) and a single cash inflow (in case of redemption). In such cases, calculating the return through the CAGR (which reflects the rate at which a mutual fund grows per year throughout an investment period) method is the correct way.

However, in cases where investors invest in mutual fund schemes through Systematic Investment Plans (SIP) or wishes to redeem units of mutual funds investment through a Systematic Withdrawal Plan (SWP).

Due to the multiple cash flow involved, one cannot use CAGR (as stated earlier) to calculate return from a mutual fund. Hence, using XIRR seems correct. Before delving deep into using XIRR, one must know about IRR.

[Source]

IRR (Internal Rate of Return) is a metric to calculate the return of a series of cash flows. Investors can use IRR to calculate returns of SIP, SWP, multiple redemptions, lump-sum investments with additional purchases, etc. However, IRR has certain limitations.

While calculating the IRR formula in Excel, it automatically assumes the time interval between with cash-flows is the same, which rarely happens throughout the tenure.

Let’s take an example!

Suppose an investor puts ₹4,000, ₹9,000, ₹5,000, ₹4,000, and ₹6,500 in SIPs over 5 years and earns ₹53,000 at the end of the tenure. Here, the return on investment is 22% (as per the IRR calculation method).

In the case of a mutual fund, investment occurs at an irregular interval (even in the case of SIP) due to a different number of days in a month, holidays etc. For uneven cash flow or irregular cash flow intervals, the calculation with IRR in the excel sheet will go on. It is here one can use XIRR and calculate the right value or return on mutual fund investment for multiple cash flows.

[Source]

Here is a step-by-step guide on calculating XIRR in excel,

Step 1 – Prepare a single column (Column A) for all purchases. Mark outflows as market negative (enter SIP figure of 5000) and inflows as a market positive. You have to include at least one positive and at least one negative value for the formula to work.

Step 2 – Select the next column for putting the corresponding date of the transaction.

Step 3 – Enter the prevailing value of all holdings and the date in the last row. (Against redemption date and column A, enter redemption amount column B – i.e. 31000) Make sure that the dates are not entered as Text. Enter them using the DATE function.

Step 4 – Next, use the XIRR function ‘= XIRR (values, date, Guess)’. XIRR (B1: B7, A1: A7)*100, date, Guess)

Step 5 - Choose values to a series of cash flow that corresponds to a payment schedule in dates, the column of dates refers to the first date of investment; guess is optional. If investors do not enter any value, excel automatically uses a value of 0.01.

Step 6 - The excel sheet will show 11.92% as a result.

Conclude your search for what is XIRR in a mutual fund and get correct results of mutual fund investment returns in case of uneven cash flow intervals.

[Source]