How to calculate mutual fund returns using the XIRR formula

Mutual funds are the investment arm of a company or institutional body. After a mutual fund is set up, it is managed by a fund manager. If you are familiar with the concept of mutual funds, you must have heard about the concept of Return on Investment (ROI), which was first popularized by Benjamin Graham in his book “The Intelligent Investor”.

Equating a mutual fund’s annual return with its investment performance is a common practice among investors. However, the process is not as simple as it seems. A mutual fund’s return is determined by the investment returns of the funds it holds, which in turn are determined by its portfolio manager’s asset management decisions. The reason why many investors do not consider XIRR formula is that they confuse its meaning with that of a fund’s return.

Last weekend a friend of mine, Monica, came to my house to meet me. She’s an old friend of mine, and we met that day after a long hiatus. She told me that she is currently working for an Indian FMCG company after completing her MBA in HR last year. We talked a lot about things we had in common, our school days and how much our lives had changed over the last few years.

During our conversation, I learned that she had recently started investing in mutual funds. I remember in a phone conversation a year ago we were talking about the importance of investing in mutual funds to build wealth in the future. Since she got her job, I told her to invest in mutual funds that will eventually help her reach her financial goals. So I was very happy to hear that she was finally starting to work seriously on her personal finances.

Anyway, I was taking a sip of coffee when she suddenly asked me a surprising question. She asked me how to properly calculate the profit she makes from investing in mutual funds. I have to say that most of us focus on the absolute return we generate. Only a minority of the investor population is aware of the basic mechanism of relative calculation of our income. And so I found his question really intriguing.

How to measure the performance of a mutual fund portfolio

If you invest your money in a variety of securities, it stands to reason that portfolio return calculations are essential to evaluating the performance of your investments. Once you know how your portfolio is performing, you can decide to invest more, stay invested, or buy back your investments.

There are essentially two ways to calculate the return of a portfolio.

The first is the method of simple return or point-to-point return. With this method, you look at the initial value of the investment and the final value. However, what you are not taking into account here is when you made that investment and when you took it out.

Another method is to calculate the XIRR (extended internal rate of return) of your investment portfolio. It takes into account different investments and withdrawals at different intervals – between the first investment and the last redemption.

The first method is only easy to apply if you invest a lump sum in mutual funds. The XIRR method, on the other hand, can be used for a one-time investment and is also well suited if you opt for a systematic investment plan (SIP).

Simple return or return from one point to another

The simple return method provides an absolute return for your investment portfolio. This method requires only the initial net asset value (NAV) and the current NAV. It can be calculated as follows:

Sometimes your retention period is not a whole number (i.e. not exactly a year). In this case, you can calculate the yield with this formula:

The compound annual growth rate (CAGR) method can also be used instead of the ordinary rate of return method. The latter works in a similar way, and the result is also the same. The only difference is that even if your investment period is not exactly one year, your return is still calculated in one step. The CAGR formula is as follows:

Let’s take an example to understand how the simple return method works.

Imagine if you had the 23rd. In February 2017, I invested Rs 1 lakh in a mutual fund when the NAV was Rs 1 lakh. 10. For 20. March 2018, the NAV has increased to Rs. 35.

In this case, the single yield = {(35-10)/10} x 100 = 250%.

In contrast, the ordinary annual return = [{(1+2.5) ^ (365/390)}-1] x 100 = 323%.

I hope you can understand the above example. As we said, if you only want to follow a buy and hold investment strategy, then the Simple Return method is for you.

I explained the same thing to Monica that night. However, I later found out that she invests part of her salary in mutual funds through the SIP, i.e. regular investments. She also stated that she does not necessarily invest on a regular basis each month. So, of course, he can’t even use the IRR method to calculate his return.

Also read : What is the internal rate of return (IRR)? And how does it work?

Therefore, as discussed above, the XIRR method can be useful to calculate the return in their case. This is a method that allows you to calculate the return on your investment when you make different trades at different times.

Calculation of returns with XIRR in investment funds

When you invest in mutual funds through SIP, you can make multiple investments at different times. In addition, you may have to surrender some of your shares if you need money at some point. I think you have already understood that it is a bit difficult to calculate the return of investment through SIPs.

In these cases, the XIRR approach is the most appropriate way to evaluate the performance of your mutual fund investments. Now let’s see how we can use MS Excel to calculate the XIRR.

How do I calculate the XIRR with MS Excel?

Microsoft Excel has a financial function, XIRR, which can be used to calculate the performance of a mutual fund portfolio. The extended formula for the XIRR formula in MS Excel is as follows: = XIRR (value, data, assumption)

Now let’s look at the step-by-step process you can follow to calculate the XIRR in Excel:

Step one: Open MS Excel and enter the transaction data in a column.

Step two: Continue with the next column. You must include your cash flow figures (investments made, dividends received and income from repayments).

Step three: In the first column, enter the current date (directly below the last date you entered) and in the column next to it, the number of the current value of the mutual fund that corresponds to the date you entered in the previous step.

You must now use the XIRR [= XIRR (value, data, assumption)] function in MS Excel to find the XIRR of your investment.

To perform the calculation, you must first select the values that represent the series of incoming and outgoing cash flows. Next, you need to select the data in the Data column. Finally, you cannot select anything in the option rates. If you leave the field blank, MS Excel uses a default value of 0.1 (10%).

Example to demonstrate the use of the XIRR function in MS Excel:

Suppose you want to subscribe to seven monthly SIPs of Rs. each. 6.000. The SIP dates start on 01/01/2019 and end on 07/01/2019. The due date was 31.07.2019 and the amount to be refunded was ₹ 43000.

You can write the record in MS Excel as follows:

As you can see in the chart above, your cash flows occur at different intervals. So we need to use the XIRR function to calculate the return as the net result of these inputs and outputs.

Quick tip: Remember to put a minus sign in front of the numbers representing the investment.

Now in the first column, B, enter the dates of the transactions. Then go to the column next to it, which is С. Here you need to enter the SIP numbers -6,000. Then go back to column B and enter the due date. By this date, in column C, the refund amount of 43,000.

Then go to the bottom cell where you placed 43,000. To calculate the required XIRR, type =XIRR(C3:C10, B3:B10) and press the Enter key on your keyboard. As a result of this transaction you will receive an XIRR of 7.84%.

I used the same example on my laptop to explain the XIRR calculation process to Monica. At the time, she understood how this technique could be used to calculate the performance of mutual fund investments. I hope that’s clear to you too now.

So, what are you waiting for? Open MS Excel and try to use this function with hypothetical numbers to calculate the IRR. If you are an active mutual fund investor, try to calculate the XIRR of your current portfolio.

Final thoughts

If you choose mutual funds with a growth pattern and plan your SIPs at regular intervals, you can certainly opt for the IRR (internal rate of return) method to estimate your return. In reality, however, this can be somewhat difficult. When we invest in mutual funds, we do not always follow a buy-and-hold strategy. Since our savings include debits and credits related to investment fund transactions, we have no choice but to apply XIRR to calculate investment returns.

In addition, you may of course value the CAGR, as this metric is useful to you in selecting a mutual fund. However, when it comes to evaluating your personal investment portfolio, XIRR is always a better choice.

So if you have a series of money inflows and outflows that occur over time (including withdrawals, dividends, investments and transfers), the best way to calculate the return is to use the XIRR. In this article, we have tried to make you understand that XIRR is much more effective in calculating mutual fund portfolio returns than IRR and CAGR.

I wish you the best of luck in investing in mutual funds. And have fun investing!

Frequently Asked Questions

How is mutual fund XIRR calculated?

XIRR is calculated by using the daily net asset value (NAV) of the fund.

What is a good XIRR for mutual fund?

XIRR is a term used in the stock market to refer to a stock that is trading at a price that is below its intrinsic value.

How does XIRR formula work?

XIRR formula works by providing a natural source of calcium and magnesium. It is a natural, plant-based blend of calcium and magnesium.