XIRR in Mutual Funds

We invest a certain amount every year in some financial instruments like mutual funds. After 5 years, 10 years or 15 years, we may want to redeem our investments.

At that time, we usually calculate the money which we originally invested with the maturity amount that we received to understand how much did we gain. But do you think this is the right method to check how much did we gain?

Returns have always been the basic benchmarks for investors while going for any investments. These indicate how much the fund has lost or gained during a particular investment duration.

You may come across returns expressed in a variety of ways and nomenclatures.

What Is XIRR?

Mathematically,

XIRR is that single rate of return, which when applied to every installment (and redemptions if any) would give the current value of the total investment.

XIRR is your personal rate of return. It is your actual return on investments.

XIRR stands for Extended Internal Rate of Return is a method used to calculate returns on investments where there are multiple transactions happening at different times.

In the case of SIP, since there are multiple investments (therefore multiple purchase prices) and different time periods for each installment, calculating returns is a bit tricky. Returns on mutual fund SIP is commonly done in terms of  XIRR.

Why Does it Make Sense for Mutual Fund Investments?

Suppose you invest SIPs of ₹ 4000, ₹ 9000, ₹ 5000, ₹ 4000 and ₹ 6500 in 5 years and receive ₹ 53,000 at the end of 5 years then your return on investment is 22%.

The resultant value is known as IRR. This concept is used to find out how much have you earned out of your investments in case of spending that is equally spaced in time.

But usually, investments are not as evenly spaced as you saw above in case of mutual funds. In the case of mutual funds, you tend to invest and redeem investments at irregular intervals.

It will cause cash inflows and cash outflows at different points in time. In this type of case, in addition to the invested amount, the time of such investment also assumes significance to yield a certain outcome.

Here you may use the concept of Extended Internal Rate of Return (XIRR).

So, XIRR is a good function to calculate returns when your cash flows (investments or redemption) is spread over a period of time.

In the case of mutual funds, if you are investing using SIP or lumpsum or redeeming through SWP or lump sum, XIRR can take care of all those scenarios and helps you calculate a consolidated return considering timings of your investment and withdrawals.

Calculation

XIRR can be easily calculated using Microsoft Excel. Excel provides an inbuilt function to calculate XIRR.

XIRR is a more powerful function in excel for calculating the annualized yield for a schedule of cash flows occurring at irregular periods.

XIRR formula in excel is:= XIRR (value, dates, guess)

Step by Step Process to Calculate in Excel

1. Enter all your transactions in one column. All outflows like investments, purchases will be market negative while all inflows like redemption’s while are marked positive.
2. In the next column add the corresponding date of the transaction
3. In the last row mention the current value of your holding and the current date
4. Now Use XIRR function in excel which is something like this =XIRR (values, date, Guess)
5. Select values to a series of cash flows that corresponds to a schedule of payments in dates and date columns stand for the date when the first investment was made and when the cash flows were received, guess parameter is optional ( if you do not put any value Excel use a value of 0.1.

Example of How to Use the Function in Excel

For this calculation you need is with an example of six-month SIP. Let

SIP amount = ₹ 5000

SIP investment dates = start-01/01/2017, end-01/06/2017

Redemption date = 01/07/2017

Maturity amount = ₹ 31000

Assume we have a set of cash flows like those in the table below :

In the above table, the cash flows are occurring at irregular intervals. Here, you can use XIRR function to compute the return for these cash flows. Remember to include the ‘minus’ sign whenever you invest money.

Open an excel sheet and follow these steps :

• In column A, enter the transaction dates on the left side.
• In column B, enter SIP figure of 5000 as a negative figure as it’s an outflow cashflow.
• Against the redemption date (Column A), enter the redemption amount (Column B) (31000).
• In the box below 31000, type in: “ =XIRR (B1: B7, A1: A7)*100 ” and hit enter

XIRR value of 11.92 % will be display as a result.

Can We Use CAGR Instead for Calculating Returns?

When we want to invest in a mutual fund first we check its returns of the past 3 years, 5 years, etc. These returns are point to point returns and are called Compounded Annual Growth Rate or CAGR.

Read whether you should invest based on past returns

For example, 3-year returns of 12 % for a mutual fund X will mean that ₹10,000 invested exactly 3 years ago have now become ₹14,049.28.

CAGR can be simply calculated by using the formula :

CAGR = ((Ending Amount/Beginning Amount)^(1/No. of years)) – 1

This typical metric is used in calculating returns of investment in a lot of mutual funds. While it is easy to calculate CAGR for a mutual fund but for personal investments it becomes a little tricky.

Let’s say you make a monthly SIP of ₹ 10,000 for 3 years. So, you have invested in 36 installments and at the end of 3 years, your portfolio value is ₹ 400000.

At the end of the 36 months if you want to calculate your portfolio return you will have to calculate CAGR for 35 months, 34 months, 33 months for different investments you made, and hence quite complicated.

XIRR makes this simpler by calculating one return for your investments. So, if you are looking to calculate returns on your mutual fund investments XIRR might be the right way to go.

Final Thoughts

As you can see from the above examples, XIRR is the right way to find out your investment returns in real life. CAGR is important to check for the selection of a mutual fund but XIRR is critical to evaluate the returns you got from your investments.

And IRR is used for investments in case of cash flows that are equally spaced in time, but usually, investments are not as evenly spaced as you saw above in case of mutual funds.

So, when there is a series of investments being made over time, including transactions such as withdrawals, dividends, switch, etc. the better way to calculate the return is with XIRR. XIRR works much better for calculating returns from your mutual fund as compared to IRR and CAGR.

Happy investing!