Abhishek has been investing in mutual funds for many years now through SIPs as well as lumpsum. However, he is not sure how he should measure the return on his investments.
His current statement tells him that in about 8 years, the total invested amount is 2.31 lacs and the current market value is 4.10 lacs. He wonders if his return is 70%.
Of course, this his return but only the total one. Typically, returns are expressed as per year or annualised. A commonly used measure of this is CAGR or compounded annual growth rate.
The formula for CAGR =((Current Value/Original Investment)^(1/no. of years)) -1.
For Abhishek, using the above formula, the CAGR of his investment is 7.4% year on year.
Now there is an issue with CAGR. It is best suited for measuring point to point annualised returns of a single investment transaction. So, if Abhishek wants to know the return of his first investment that he made, he can use CAGR.
However, 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.
What is XIRR?
As mentioned before, XIRR or Extended Internal Rate of Return is a method to calculate returns on investments where there are several transactions happening at different points in time.
Take the case of Abhishek’s portfolio. He made an initial lump sum investment. He also had an ongoing Systematic Investment Plan or SIP where a particular amount of money was invested every month.
XIRR is a better form of evaluation for this kind of investment cash flow.
How do you calculate XIRR?
Calculating XIRR is easy with MS Excel.
Enter all your transaction values and dates in two columns of an excel file. At the bottom of this table, enter the current date and current value of the portfolio. Then apply the XIRR formula. This is an inbuilt formula in MS Excel.
In the table, all red color numbers are the investment amounts. The minus numbers indicate a cash outflow. Rs. 4.10 lacs is the current market value of these investments as on Jan 15, 2017.
Now, we will apply the XIRR formula. In one of the cells below the table, enter =XIRR and a formula tip will appear, which reads something like this:
=XIRR(values, dates, [guess])
So, there are 3 inputs that you need for calculating XIRR – values, dates and a guesstimate return. We already have the first two in the excel file.
To supply the necessary information into the formula, select all the invested amounts and the current value figure for values and dates for the dates. Ignore the guess.
Hit enter and you get the result. The XIRR is 11.4%. Compare this with the CAGR of 7.4%. Abhishek’s investments are doing much better than what was indicated by CAGR.
An even simpler way to know XIRR of your mutual fund portfolio
You can simply upload your CAMS consolidated transaction statement onto many of the platforms available today for MF portfolio tracking.
Final thoughts
XIRR is your personal rate of return. It is your actual return on investments. The returns shown by mutual fund factsheets are different and apply to the fund and not you.
From an application point of view, any investment portfolio with multiple cash flows happening over a period can benefit from calculation of XIRR. It could be your ULIP, endowment or money back policy, your stock market portfolio, your EPF, PPF or NPS account too. In fact, you can put all of them together to know your portfolio return.
So, let us ask you, what is your portfolio return?
Comments