Last Updated on May 25, 2022 by Neera Bhardwaj
Everyone invests because there are potential returns you can earn from it. As an investor, it is important that you seek out answers to these questions before investing in any financial product:
- How much return can I generate?
- Is there a scope for regular income?
- How much will my capital appreciate?
In the case of a lump sum or one-time investments, the CAGR (Compound Annual Growth Rate) is the most-trusted method to calculate the rate at which your investment sum will grow. However, CAGR doesn’t hold any good when multiple investments are involved, like with SIP. So to edge past this problem, XIRR turns out to be the best resort. In this article, we dive deep into what XIRR is and its significance.
Table of Contents
What is XIRR?
XIRR denotes ‘Extended Internal Rate of Return.’ XIRR is that rate of return to which, when applied to all your instalments and redemptions during the tenure of the investment, gives the present value of your investment.
The CAGR tends to even out irregularities over time and presents only an average rate of return. It does not compute the returns in investments where multiple cash flows are involved. This is the premise for the introduction of the XIRR. In investments where there are multiple investments involved, like with Systematic Investment Plans (SIPs) and additional purchases to the portfolio, or in cases where multiple outflows are involved, like Systematic Withdrawal Plans (SWP), dividend payouts, and partial redemptions, XIRR is used to calculate the return of the investments.
XIRR is a modified version of IRR as it holds good even when inflows and outflows are inconsistent, i.e., not periodic. XIRR presents a more true value of your investment than CAGR does.
Significance of XIRR
Let’s suppose you have a SIP in a mutual fund due on the 3rd of every month. There may be times you’ve been late to pay your instalments or times you skipped it. We need to pay attention here because each instalment is invested for an irregular period, and the value of the investments vary with the NAV of the fund, hence the returns too will be varied. For instance, the first instalment of your SIP may yield 10% market-related returns in a year, the next instalment may see only 5%. Should the market be on a downtrend, the 3rd instalment may even experience negative returns!
When you use CAGR to calculate the returns from your mutual fund investments, fluctuations get ironed out and what you get is an average figure indicative of the returns. It may not be conclusive. Click To TweetWhen you use CAGR to calculate the returns from your mutual fund investments, these fluctuations get ironed out and what you get is an average figure indicative of the returns. It may not be conclusive.
Therefore, to calculate the returns on such investments that are erratically spaced, XIRR is used. XIRR takes all the variations and discrepancies into account and yields a consolidated return.
How to calculate XIRR?
You don’t need to hire a financial advisor or a Chartered account for the job; you can do it yourself! However, you should know all the transactions like SIP redemptions and additional purchases along with the date of the transactions. Skipping any of the transactions or considering incorrect dates will lead to erroneous calculations.
In excel, there’s an inbuilt function that goes by the name ‘ XIRR.’ You can calculate the returns using this function on an excel sheet. You may enter the transactions of your mutual fund recorded at different dates and use the XIRR function.
Procedure to calculate XIRR
- Create an excel sheet and enter the record date for each mutual fund transaction in the first column.
- Enter all the transaction amounts in the second column.
- Note: all the cash outflows, i.e., instalments, and additional purchases, will bear a negative sign, indicating that you have parted with the funds. And all the inflows, i.e., redemptions, will bear a positive sign.
- In the last row of the second column, you need to enter the redemption value; it will be the current value of your investment.
- Now type “=” followed by “XIRR” to use the XIRR function in excel. Then select the range of the cash flows followed by the range of transaction dates as separated by a comma.
The formula for the XIRR function is =XIRR (values, date, Guess)
Let us understand this with the help of an example:
Suppose Mr. A has started a SIP on 1 Jan 2021 with Rs 15,000 and invests Rs 15,000 per mth for 6 mths.
Though Mr. A is is not regular with his instalments, and his instalment summary is as follows:
1 Jan 2021 – 15,000
3 Feb 2021 – 15,000
2 Mar 2021 – 15,000
1 Apr 2021 – 15,000
8 May 2021 – 15,000
6 Jun 2021 – 15,000
The redemption value received by Mr. A on 1 Jul 2021 is Rs 93,000.
Let’s incorporate XIRR to find the returns earned by Mr. A.
We find that Mr. A has earned a return of 15.03% on his investment.
It’s important to note here that cash flows are inconsistent, the payment of instalments by Mr. A are made at irregular intervals; hence XIRR needs to be applied.
Conclusion
When cash flows in investments tend to be irregular, XIRR is generally considered a better measure of the returns than the CAGR. Even if we assume that in a particular case, the investments are regular, still there are factors like additional purchase and partial redemption that tend to make your investments unevenly spaced. Thus, to understand the worth of your investment, XIRR as a tool that enables you to access the actual returns yielded by your investments every year becomes important. This takes precedence in cases of SIP investment, and may also help to assess past returns of mutual funds so that you may make the right pick of funds to invest in.
