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.
XIRR is a very useful tool for calculating returns. There are series of investments in a SIP and sometimes one can redeem a little from their investments and sometimes several months’ investments are skipped as one has the option to pause their SIPs. Calculating the returns, in this case, gets easy.
Calculating XIRR Using Excel
All one needs is the date on which the transaction happened and the amount of transaction.
The excel function for XIRR is as follows-
XIRR = XIRR (value, dates, guess)
Where;
Value is the transaction amounts- Investment and redemption.
The Date is the date on which the transaction happened.
Guess is the approximate returns.
Let us understand this with the help of an example
Suppose Amit has started a SIP on 1 Jan 2021 with Rs 1,000 and invests Rs 1,000 per month for 14 months.
Though Amit is not regular with his instalments, and his instalment summary is as follows:
SIP Date | Amount |
10-01-2021 | -1,000.00 |
10-02-2021 | -1,000.00 |
13-03-2021 | -1,000.00 |
13-03-2021 | -1,000.00 |
14-05-2021 | -1,000.00 |
14-06-2021 | -1,000.00 |
15-07-2021 | -1,000.00 |
15-08-2021 | -1,000.00 |
16-09-2021 | -1,000.00 |
16-10-2021 | -1,000.00 |
17-11-2021 | -1,000.00 |
17-12-2021 | -1,000.00 |
17-01-2022 | -1,000.00 |
17-02-2022 | 14,500.00 |
XIRR | 19.60% |
The redemption value received by Amit on 17 Feb, 2021 is Rs 14,500.
Let’s incorporate XIRR to find the returns earned by Amit.
We find that Amit has earned a return of 19.60% on his investment.
It’s important to note here that cash flows are inconsistent, the payment of instalments by Amit is made at irregular intervals; hence XIRR needs to be applied.
Why XIRR is used for Mutual Funds?
Investment cash flows, be it in or out, they are never evenly spaced out. Sometimes, there are late deposits or early withdrawals. A couple of months are skipped in a row, and in such cases calculating the return from investment becomes difficult. While calculating returns, amount invested and time determines the output differently.
Similarly, for Mutual Fund investments be it SIP, lump sum or withdrawals through SWP or lump sum XIRR will help in calculating the returns upon considering all the irregularities.
Similarities and differences between XIRR and CAGR
Particulars | CAGR | XIRR |
Meaning | CAGR helps an investor to gain insights into the growth of his corpus over time. | XIRR helps an investor to find out the average return of all the cash flows into his investment. |
Usage | Mostly used for a lump sum. | It takes into account the multiple inflow and outflow occurring in an investment. In short, calculation of returns when SIP and SWP plans are opted becomes easier. |
Return calculation | It measures the annualized return for lump sum investment. | It measures the annualized return for periodic investments (regular as well as irregular). |
Summing up
These little tricks and strategies might help you differentiate yourself from many others and help you fast-track your financial goals. It enables you to look deeper into your investment and interpret them better. Having offered the right methods and tools, it always drives your ability to decide logically and not be carried by sentiment. So, ensure you do that part of yours right and leave the rest aside.
Always have your spread sheets ready and do the maths before investing.
Invest wisely!
