Last weekend, one of my friends, Monika, came to my house to meet me. She is an old friend and we met after a long time that day. She told me that she is currently working in an Indian FMCG company after having completed her MBA in HR last year. We discussed a lot about general stuff, our school days and the extent to which our lives have undergone changes over the last few years.
As our conversation gradually progressed, I came to know that she has recently started investing in the Mutual Funds. I remember that a year back, we discussed on a call regarding the importance of investing in Mutual Funds for creating wealth in the future. Ever since she got her job, I have been telling her to invest in the Mutual Funds which would ultimately help her to achieve her financial goals. And that’s why I felt really pleased to know that she has finally started taking her personal finance seriously.
Anyways, I was just taking a sip of coffee when all of a sudden, she fired a wonderful question at me. She asked me how could she correctly calculate how much returns she is making from her Mutual Fund investments. I must tell that most of us focus on the absolute returns that we are generating. Only a minority of the investing population emphasizes on the underlying mechanism to compute our returns in relative terms. And therefore, I found her question truly charming.
How to measure returns on your Mutual fund portfolio?
If you have invested your money in a variety of securities, it is logical to say that the computation of portfolio returns is indispensable for evaluating the performance of your investments. This is because once you know the performance of your portfolio, you can decide whether you want to invest more, continue investing, or redeem your investments.
There are primarily two ways of computing your portfolio returns.
The first one is ‘Simple Return’ or ‘Point to Point return’ method. In this method, you consider the starting value of your investment and the ending value. However, here you do not consider when you made such investments and when did you withdraw.
The other method is by calculating XIRR (extended internal rate of return) of your investment portfolio. It takes into consideration multiple investments and withdrawals occurred at different time intervals– between the first investment and the last redemption.
The former method can be easily applied only if you are investing in Mutual Funds on a lump sum basis. On the other hand, the XIRR method can be applied for lump sum investing and it is also extremely suitable if you have opted for Systematic Investment Plan (SIP).
Also read: SIP or Lump sum – Which one is better?
Simple Return or Point to Point return
Simple Return method gives the absolute return on your investment portfolio. This method only requires your initial Net Asset Value (NAV) and the present NAV. It can be calculated as:
Now, sometimes it might be possible that your holding period may not be in a complete number (i.e. not perfectly a year). In that case, you can compute returns using this formula:
Further, instead of using the Simple Return method, one can also opt for the CAGR (Compound Annual Growth Rate) method. The latter works in a similar way and the result is the same too. The only difference is that, even if your investment time period is not perfect one year, it will still calculate your returns in a one step. The formula for CAGR is:
Let us take an example to understand how the Simple Return Method works.
Imagine you have invested Rs. 1 lakh in a Mutual Fund scheme on February 23, 2017, when its NAV was Rs. 10. As of March 20, 2018, its NAV has spiked to Rs. 35.
In this case, Simple Return = {(35-10)/10} x 100 = 250%.
On the other hand, Simple Annualized Return = [{(1+2.5) ^ (365/390)}-1] x 100 = 323%
I hope the above example is clear to you. As we already discussed earlier that if you’re simply going to follow ‘buy and hold’ investing strategy, then the Simple Return method would work well for you.
I also explained the same to Monika that evening. However, later what I came to know was that she invests a portion of her salary in Mutual Funds via SIP route i.e. periodic investments. Moreover, she also said that she doesn’t necessarily invest at regular intervals every month. So naturally, she can not even use IRR method for calculating her returns here.
Also read; What is Internal Rate of Return (IRR)? And How Does it Works?
So as discussed previously, the XIRR method can be useful to calculate returns in her case. This is a method that you can use to compute returns on your investments when you are carrying out multiple transactions at diverse points of time.
Calculating returns using XIRR in Mutual Funds
While investing in mutual funds via SIP, you can make multiple investments at different points of time. Further, it might also happen that you redeem some of your units if you required some cash at any particular time. And that’s why I guess by now you would have understood that it is going to be a little tricky to calculate your returns when you are investing via SIPs.
In these cases, the XIRR approach is the most appropriate measurement of the returns that you make on your Mutual Fund investments. Now, let’s understand how you can make use of MS Excel to calculate XIRR
How to use MS Excel to calculate XIRR?
Microsoft Excel provides a financial function called XIRR which you can use to calculate your Mutual Fund portfolio’s rate of return. The expanded formula of XIRR formula in MS Excel is: “= XIRR (value, dates, guess)”
Let us now discuss the step by step process that you can follow to compute XIRR in Excel:
Step 1: Open MS Excel and enter the dates of your transactions in a single column.
Step 2: Go to the next column. Here you have to mention the figures of your cash flows (investments made, dividends received and redemption proceeds).
| Date | Transaction (Rs) |
| 01/01/19 | -5,000 |
Step 3: Go to the first column and mention the current date (just below the last date mentioned by you) and put the figure of your present value of Mutual Fund in the adjacent column corresponding to the date mentioned by you in the earlier step.
| Date | Transaction (Rs) |
| 01/01/19 | -6,000 |
| 04/02/19 | -6,000 |
| 31/07/19 | 13,000 |
| XIRR (%) | ? |
Now you have to use the XIRR function [“= XIRR (value, dates, guess)”] in MS Excel to find the XIRR for your investments.
To perform the calculation, you have to first select the values which are the series of cash inflows and outflows. Then, you have to select the dates from the dates column. Finally, in the ‘guess’ parameter you may not select anything for it. If you keep it blank, MS Excel is going to use the default value which is 0.1 (10%).
Example to demonstrate the use of XIRR function in MS Excel:
Let us assume that you are going to make seven monthly SIPs of Rs 6000 each. The SIP dates start on 01/01/2019 and it ends on 01/07/2019. Let the date of redemption be 31/07/2019 and the maturity amount is ₹ 43000
You can write the data set on MS Excel in the following manner:
As you can see, in the table given above, your cash flows are taking place at different intervals. So, here, we have to use XIRR function for computing the rate of return as a net result of these receipts and payments.
Quick Note: Do not forget to put the ‘minus’ sign before the figures which represent an investment of money.
Now, in the first column i.e. B, please enter dates of transactions. After that, go to the adjacent column, i.e. C. Here, you have to enter the SIP figures of -6,000. Next, again, go to column B and put the redemption date. Against that date, put the redemption figure of 43,000 in Column C.
Next, go to the cell below where you have put 43,000. For calculating the required XIRR, type “=XIRR(C3:C10, B3:B10)” and press ‘Enter’ key on your keyboard. You will get XIRR as 7.84% which will be displayed as a result of this operation.
I used the same example on my laptop to explain the process of XIRR computation to Monika. By this time, she had understood how she can apply this technique in calculating the returns on her Mutual Fund investments. I hope this is also clear to you guys now.
So, what are you waiting for? Open MS Excel and try this function with hypothetical figures to calculate IRR. In case you are an active Mutual Fund investor, try calculating the XIRR of your current portfolio.
Closing Thoughts
In case, if you choose growth scheme Mutual Funds and plan your SIPs at regular intervals, you can definitely opt for the IRR (Internal Rate of Return) method to evaluate your returns. However, in reality, this might be a little difficult to execute. We don’t always follow the ‘buy and hold strategy’ in Mutual Fund investments. As our savings witness debits and credits off and on with regard to our mutual fund transactions, we have no other way but to apply XIRR in computing our returns on investments.
Furthermore, you can, of course, give importance to CAGR as this parameter is useful for you in making a selection of your Mutual Fund scheme. However, when it comes to evaluating your personal investment portfolio, it is XIRR which is always more helpful.
So, if you have a series of cash inflows and outflows occurred over time (which includes withdrawals, dividends, investments, and transfers), the best way of computing the rate of returns is by using XIRR. Through this article, we have tried to make you understand how XIRR works far better for computing returns of your Mutual Fund portfolio in comparison with IRR and CAGR.
Best wishes for your mutual fund investment journey. And happy investing!
