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 Unovest and instantly know your folio wise as well as total portfolio XIRR.
Here’s how to upload your mutual fund portfolio on Unovest in 1 click. Once you upload, just proceed to view the report and know the XIRR.
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?
Read your article on how to measure one’s portfolio returns
and it was was very good and I came to know the difference between CAGR and XIRR-was explained very well
However,I have a query with regards to XIRR calculation.When returns of an SIP based investment are too much negative the excel formula for XIRR shows an error and it does not show the correct value
Thanks for the comment. Can you share your calculation sheet with me at email@example.com ?
Your article brings clarity in calculating return on portfolio.
However i have a doubt with XIRR calculation. For instance in order to know YTD growth of a portfolio can we calculate XIRR from 31-3-2018 to 31-3-2019 & from 31-3-2019 to 31-3-2020 individually and then geometrically chain those two return? Would it be the correct way of calculating returns ?
Harsh, I think you are referring to the geometric mean return measure, which more commonly is also known as the CAGR.
I am not sure about the YTD part though. Assuming, one starting investment amount only, YTD return should ideally be the absolute return.