XIRR in Mutual Fund

Saving money for the future is something that the majority of people look after. To achieve this, they go through several means of investing and saving money, like doing some Fixed Deposits or Recurring Deposits in the Banks, investing in the Stock Market, Investing in precious metals like Gold, creating savings account in some bank, investing in the mutual fund, or investing in some business on an individual level. Out of these all, one of the very widely used approaches for investing money is investing in mutual funds.

You might have invested a certain amount of money every year in the mutual funds, and now, after 5 years, or 10 or 15 years, you want to redeem your investments. At that time, we always tend to calculate how much had we invested, and what is the return, so that you can know how much we gained through the mutual funds, throughout these years. But if we look at the absolute return, to look for the growth, it does not give us a clearer picture. It shows the growth of your investment without taking the time period into consideration. It is simply the percent difference between the money before investing and the money that you got now.

XIRR in Mutual Fund

What is XIRR? or XIRR Full Form

XIRR stands for Extended Internal Rate of Return, and it is one of the popular measures for Mutual Funds returns. When it comes to measuring the returns in the case of multiple cash flows, XIRR is the measure that is to be used to calculate the growth of your investment. There is something called CAGR as well, or Compound Annual Growth Rate, which is the rate at which your investment grows per annum, over the period of investments, assuming annual compounding. But the CAGR cannot be used in the case of multiple cash – flows. Here, we can use the principle of XIRR.

The investment also might take place in some different means, like for one time, when you invest a lump sum amount and a one-time redemption. This just involves one cash outflow and one cash inflow. But when it comes to investing in investment instruments SIPs (Systematic Investment Plans), there are multiple cash flows. Here, it is not relevant to use CAGR.

Basically, XIRR is a single rate of return, which when applied to your every installment(and redemption if applicable) would get us the current value of the total investment. It is the actual return on the investment.

One should understand that the XIRR should be used for calculating the return in the situations of multiple cash flows in the investment.

When we calculate the growth of our investment over a period of time, usually the resultant value here is known as the Internal Rate of Interest. This is used for calculating the growth of the investment in case of the investments in equally spaced time.

But it is not the case often. The investments are not evenly spaced. The cash outflow and inflow may be in an irregular period of time. In such cases, in addition to the amount invested, the time of the investment also has significance here, to get a certain outcome.

XIRR can be a good function when we are required to calculate the returns on an investment with multiple cash flows, spread over a period of time. XIRR can take care of these situations and helps us calculate the return by taking the time of investments and withdrawals into consideration.

How to calculate XIRR?

You would have become aware of how the Extended Internal Rate of Return is good for calculating the returns over the investments, which consist of multiple cash flows, spread over a period of time.

Now, let’s have a look at how can we calculate the XIRR so that we can get the return on the investments. This can be very easily calculated using Microsoft Excel. There is an inbuilt function in Microsoft Excel for calculating the XIRR.

XIRR Formula

The formula for XIRR is =XIRR(value, dates, guess)

You can follow this step-by-step process to calculate the XIRR in Microsoft Excel –

• Step 1: Enter all your transactions into one column. All the cash outflows like investments, and purchases, would be marked as negative, while all the cash inflows like redemptions, would be marked as positive.
• Step 2: In another column, mention the dates of the respective transactions.
• Step 3: In the last row of your data, you have to mention the current value of your holding and the current date in the date column.
• Step 4: Now, you have to use the XIRR function to calculate the returns, which is something like this – = XIRR(value, dates, guess). Here, the guessing parameter is optional, and if you do not put anything here, Excel uses a value of 0.1

In this way, you can get the XIRR for your investments, which involve multiple cash flows, spread over a period of time. You can use the above-mentioned formula in Microsoft Excel, to get the value for XIRR.

Let’s have a look at an example, which tries to demonstrate the same thing –
For this example, we are going to consider a 6 months SIP.

• Let’s consider that the amount for the SIP was Rs. 6000
• The investment start date – is 01/01/23
• The investment end date – is 01/06/23
• Maturity amount – Rs. 37150

Let’s say that the cash flow is something like this, as mentioned in the above table –

As you can see, in the above table, the cash flows occur at different intervals of time. Remember that here, the negative values are defining the cash outflows(investments or purchases), and the positive values are defining the cash inflows(redemption).

In the last row, and in the second column, you can see that we are getting the return percentage as 11.86%. All you have to do is use the XIRR function, first select the values, and then select the corresponding dates as the second value.

The guessing parameter is optional, and if we do not give it, it is considered 0.1. Also, we have to multiply the value by 100. In this way, the formula becomes something like this –

=XIRR(values, dates, guess) * 100

So, using the above function in Microsoft Excel, you can simply calculate the returns on your investments. In this way, we have understood the concept of XIRR, and how is it useful for calculating the returns when your investments involve multiple cash flows, spread over a period of time.

Conclusion

In this article, we have seen what is XIRR, which stands for Extended Internal Rate of Return. It is the single rate of return, which when added to every installment, gives us the current amount. You can use the function XIRR in Microsoft excel, to calculate the XIRR.

In this way, you can get the growth of your investment. Note that we do not promote or recommend any way or any means of investments. Investments are subject to market risk. This article is just for informational purposes.

FAQ –

Q: What is the full form of XIRR?
Ans: The full form of XIRR is the Extended Internal Rate of Return.

Q: What is the full form of CAGR?
Ans: The full form of CAGR is Compound Annual Growth Rate.

Q: When should we use XIRR?
Ans: When our investment involves multiple cash flows, we should use XIRR to calculate the growth of an investment.