Understanding XIRR for mutual fund returns

We help you understand what is XIRR for mutual funds, the difference between XIRR and CAGR, how to calculate it and why you should use XIRR

Kavya Balaji   /   October 16, 2020
Understanding XIRR for mutual fund returns

When you think of investments, you think about the returns you get from them. This could be either income from investments such as dividends or the capital appreciation you get. When it comes to mutual funds, you can calculate your returns using XIRR or CAGR. While CAGR is Compounded Annual Growth Rate, XIRR is Extended Internal Rate of Return (XIRR).

So, whatís the difference between CAGR and XIRR?

CAGR is a point to point return for an investment that uses only the value of the investment at the beginning and end of the investment time to calculate the annual returns. It is the rate at which your investment grows every year during a particular investment period. So, CAGR uses annual compounding for your investment returns.

Hereís how it is calculated. The first column here shows the year in consideration and the second column is the value of your investment in that year.

YearInvestment value
01000
11200
21250
31400
41750
51900
CAGR13.70%

The formula for calculating your investment return is

CAGR = ((final value/initial value) ^1/number of years) – 1

Understand that CAGR assumes that at the end of each year, the earnings from your investment are reinvested. Another point is that CAGR represents steady gains from your investments every year.  This is not possible in real life as mutual funds are market linked and their gains vary every year. And the most important point is that CAGR doesnít account for inflows and outflows from an investment. It takes only the initial and final investment values for calculating average annual return from your investment.

Why XIRR is better than CAGR

This is why XIRR is more useful. XIRR takes into account multiple cash flows involved in an investment. It helps you calculate returns based on transactions that took place at different times.

So, what are these multiple cashflows? For most investments, there are only two cashflows Ė one is the initial investment and the other is the redemption amount. However, for mutual funds there are multiple cashflows such as Systematic Investment Plan (SIP), additional investments, dividends, Systematic Withdrawal Plan (SWP) or partial redemptions. So, cashflows are purchases as well as redemptions. There might be times when you skip investments or make investments from your bonus. All these cashflows need to be taken into consideration when returns from your mutual fund is calculated. This makes the calculation of your returns very ambiguous. XIRR helps here as it considers all of the cashflow transactions for calculating returns from mutual fund investments and provides the present value of your investment. XIRR will work for all your one-time investments that have fewer cashflows too. XIRR uses the time value of money concept for calculating returns from your investment.

Now, why should I use XIRR and not IRR?

Internal Rate of Return or IRR helps investors calculate mutual fund returns after considering SIP, SWP, purchases or redemptions. So, it does consider all the cashflows of an investment. Hereís how it is calculated on Microsoft Excel. Write down all your cashflows in a column. All cash outflows will have a minus sign at the front. Once you have provided all the cashflows on the excel sheet, provide the present value of your investment. Then, below that give the formula =+IRR (select all cashflows).

However, IRR assumes that the time between consecutive cashflows is the same. It doesnít consider the dates on which the investments are made. This isnít always so. For instance, the time line between dividend payments are not the same. So, the mutual fund returns may not be right. That is why you need to use XIRR. XIRR is a modification of IRR where you can assign specific dates to the individual cashflows. You can use Microsoft Excel to calculate XIRR for your mutual fund investments. You can make use of your mutual fund account statement sent by the fund house for finding out the transactions that are relevant for your investments.

Hereís how XIRR is calculated on Microsoft Excel.

Provide all the dates on which you had cashflows on the excel. Then, in the second column, give the cashflow as on that date. Once you have entered all the cashflows, provide the present value of your investment. The formula for XIRR is =+XIRR (cashflows, dates). In this example, we have taken a scenario where you are investing in mutual fund using SIP and your monthly debit is Rs. 3,000. You make a one-time investment in October along with your SIP. You need to know the value of your investment as of 16th October. Provide that value on the excel to get the return for your mutual fund investment.

Note that to calculate XIRR, you need to provide all your cashflows such as SIP, one time purchases as negative values (just put a minus sign before the amount). You will need to provide the cash inflows including dividends, redemptions, SWP as positive values. Once you have provided all the cash flows on the excel, you will need to give the present value of your investment as on the date on which you want to calculate the return for your investment. Note that transactions such as dividend reinvestment do not have actual cashflows. So, donít include those in your return calculation.

How to calculate XIRR if there are switch transactions?  

When you are calculating the returns for a particular mutual fund scheme, you will need to consider switch transactions as purchases or redemptions based on the scheme for which you are calculating returns.  However, if you are calculating the overall return from all your mutual fund investments, then, switches will not be relevant.

So, when you have multiple cashflows such as SIP, SWP, STP, dividends, part redemptions etc. and the timelines that the mutual fund transactions were made is inconsistent, then, XIRR is the right way to calculate returns from your mutual fund investments.

Got doubts on calculating annual returns from your investments? Get in touch with your consultant at Wealthzi.com.

Tags: , , ,

Join Our Newsletter
Email
Name