XIRR in Mutual Funds

What is XIRR in Mutual Fund Investments?

Investors often have confusions over how to calculate their “real” returns, considering all the variations and amount redemptions etc. from their invested amount. However, is the answer always in terms of CAGR and simple IRR? Not at all!

What is XIRR?

Extended Internal Rate of Return or XIRR, a dynamic super-useful feature in Excel, is a way of calculating your personal returns. Most mutual fund investments are made through a Systematic Investment Plan (SIP). A SIP is a method of investing regular amounts into a scheme across different time intervals. So, multiple investments over different time intervals can make the calculation of your personal returns very ambiguous since more the variations in investments, the more your purchase price varies.

This is where XIRR comes into the picture. XIRR in Mutual Funds gives you the present value of your investment. XIRR is a precisely calculated rate, which when applied to your every investment gives you the specific evaluation. It plays a very crucial part in Mutual funds’ returns calculation, even when it isn’t always made in SIP. XIRR works in lump sum investments too, as unlike any other calculation tools this is your personal portfolio evaluation.
Let’s dig in deeper into why XIRR is needed for Mutual Fund Investments!

Uses of XIRR in Mutual Fund Investments

A perfect regular investment is ideal, but it is often observed that investments in the long haul are irregular and even, the cash flow in and out of the scheme varies. Sometimes, there are early withdrawals from a scheme or late deposits or sometimes, months in a row are skipped. In that case, one cannot rely on simple Interest Rate Return (IRR).
What we need to pay attention to are factors like the invested amounts and the respective times of their investments. The time and the amount may determine the output differently with each investment. This is where XIRR helps you by taking all the variations in consideration and presenting you with a consolidated return of your selected mutual fund schemes.

How to Calculate XIRR in mutual funds?

There are two versions of this.

First version: All you need is the Value of your investment, the date it was made on and the approximate returns, which will be referred to as “Guess” below.

This is the Excel function, XIRR formula = XIRR (value, dates, guess)

The first version of this goes:

Step I:

Make two columns. The left column should be titled “Date”, and as the name suggests, all the outflow/inflow dates are listed thereafter.

Step II:

In the right column, title it as “Value”. The redeemed amounts are written in positive as they are the transactions that are made to you, whereas the invested amount is written in negative since they are the flows from you.

Step III:

The very last row of the sheet should have the current value of your holding and the current date

Step IV:

Below it, you may use XIRR function “= XIRR (values, date, Guess)”

Step V:

Now, you have to insert values corresponding to the respective date. Say, we pick the first and the seventh value, and then we have to pick the first and the seventh date. It is put as “= XIRR (V1: V7, D1: D7, 5%). Here V1 is the value of the first investment, D1 is the date; V7 is the seventh investment and D7 is the date, while 5% is the approximate return.

Step VI:

Now press enter, and enjoy the ease of having a more precise analysis.

The second version isn’t much different, except it rules out “Guess” factor.

• Step1:

Transaction dates are listed at left.

• Step 2:

Transaction amounts are written to right. A negative sign before investments, and a positive sign before withdrawals is prefixed.

• Now, the formula in this version “=XIRR(V1: V7, D1: D7)*100

How is XIRR different from other calculations, namely CAGR?

However, the one question that we need to address is why XIRR is the super-feature in investments. We also have Compounded Annual Growth return (CGAR). CGAR is the parameter on which one should judge the mutual fund schemes to choose. The most prominent thing we all look for is the fund’s returns over a year, then three years and then five years. These are returns over regular time intervals and uniform in nature. It is these returns, that can be termed as “CAGR = ((Final Sum/Initial Amount) ^ (1/No. of years)) – 1”

As you would see calculating CAGR is way easier and seems to be the common metric found during comparisons between two schemes. Then, why do we need an XIRR?

Because real investments are never this uniform or regular, with the constant inflows and outflows, and common variations in the investment amount and the time.

If you were to calculate your personal portfolio using CAGR, you would need to apply the formulae for each investment made separately, and not to forget, redemptions too!

While CAGR is still the right way for assessing the mutual fund schemes that you are interested in, it shows you potential returns. However, XIRR in Mutual Funds remains your personal analysis, showing you specifically how your funds did.

Whenever this is a uniform investment, IRR or CAGR works fine. However, in real life scenarios, redemptions and investments go hand-in-hand and there are unpredicted withdrawals or deposits, IRR or CAGR don’t always give the best results. Trust XIRR in Mutual Funds’ analysis! In a way, you could say CAGR adjusted for real investments is XIRR.

Happy investing!