For any type of investment, the main objective is to focus on its returns. These returns could be in the form of a capital gain or income or both. You might be familiar with the term CAGR (Compound Annual Growth Rate), which is simply the average yearly growth rate of an investment over a period of time. In other words, it calculates your returns from one point (while investing) to the other (your return).
However, there are investment options like the Systematic Investment Plan (SIP) where you invest small amounts regularly, usually monthly, over a period of time. In that case, you can’t use the CAGR method. That’s because there are multiple investments with different time periods for each. To calculate your returns effectively for such types of investment plans, you need another way that is called Extended Internal Rate of Return (XIRR).
What is XIRR?
In simple words, the XIRR meaning in mutual funds is to calculate returns on investments where multiple cash flow/transactions occur at different times. This cash flow could either be inflow or outflow of money, and it’s okay if the dates are not strictly followed after a specific interval.
Calculating mutual fund returns
Let’s take an example of how to calculate a SIP return using XIRR.
Assume you decided to start an SIP and you’re ready to invest Rs. 1,000 for 13 months, where the total value of your investment including the expected returns would be Rs. 15,000. Now, rather than explaining to you a typical complex formula to calculate XIRR, you can simply open an Excel sheet where this formula is inbuilt, and all you have to do is enter your values. See the picture below.
To calculate XIRR using Excel, you have to make two columns in an Excel sheet; one with the amount of cash flow – SIP/SWP (Systematic Withdrawal Plan) installments or additional purchases, redemptions, etc. In the other column, enter the corresponding dates, the details of which would be given on the statement of accounts sent by the fund house.
Now to use the Excel XIRR formula, follow these steps:
- Click on the formula tab on the ribbon bar
- Select the financial category, then scroll down and search for XIRR
- Once you choose the XIRR formula, a dialogue box like this will appear
- The Excel formula for XIRR is:
Formula =XIRR(values, dates,[guess]),
In which,
Value = This is the array of values that represent the series of cash flows
Dates = This is a series of dates that correspond to the given values
Guess = It is optional data, which you can type your initial guess for XIRR value, if skipped, Excel takes the default value of 10%.
To use this formula, you have to select the complete column of all the transactions data for the value data, and then select the complete dates column for the dates section and press enter. And, there you have your answer.
Things that can cause an error in your calculation
While entering the data in these Excel columns, remember that all the outflow transactions must be entered with a negative value, and cash-inflows must be entered as positive numbers. Also, the dates are typed as 10-01-2021 and not 10.01.12. To calculate XIRR for your mutual fund investment, if you have not yet redeemed your units, you will need to enter the current investment value and the date of Net Asset Value (NAV). Reinvestment of dividends is not included in any column.
Just like CAGR, with the help of a compounding formula, XIRR provides you with the average returns of a specific scheme, which can be used to make a side-by-side comparison to the other investment options available.
