“The best investment you can make is in yourself” – Warren Buffett
In very simple words, investing is an activity carried out to multiply your money. You need to know whether your money has grown, by how much it has grown, and how the investment has performed as compared to other assets available in the market. The performance metric that helps you figure out all these are called ‘return.’
When you invest a lump sum amount, it’s pretty straightforward to ascertain the returns. You invest today, and you withdraw it after a specified period. The compounded annual growth rate (CAGR) method directly gives you the rate at which your money has grown during the period.
But what about SIP investments, where investment is made in installments? The CAGR method cannot be used here. The best tool that we can use in this case is the XIRR. The XIRR full form in a mutual fund is ‘Extended Internal Rate of Return.
What is the Need for XIRR?
In a Systematic Investment Plan (SIP), the investment duration of each installment is different. The first installment may remain invested for 60 months, while the last one gets only a month to grow. It would not be correct to use the CAGR method, which simply averages out the cash flows and assumes a one-time investment.
But the XIRR method is extremely handy when there are multiple cash flows, and transactions are made on different dates. E.g., if you choose a monthly SIP in an equity mutual fund scheme and the SIP runs for five years, there may be dividend inflows or partial redemptions in between. XIRR helps you to correctly ascertain the returns by considering these variations.
Meaning of XIRR
The XIRR meaning in mutual funds is significant. To understand XIRR, you first need to know the application of the Internal Rate of Return (IRR). IRR is commonly used to determine returns for any investment having a series of cash flows. But the drawback of IRR is that it can compute returns only when the cash flows are evenly spaced in time (the gap between each monthly SIP installment is exactly 30 days).
But the actual situation is quite different. Not every month has exactly 30 days. If the SIP due date is a holiday, the next business day is taken into account. You need a better version of IRR to calculate returns. So XIRR is just a modified version of IRR that has the flexibility to account for irregular cash flows in an investment.
Calculation of Returns Using XIRR Method
The XIRR formula is simple. It can be easily calculated using Microsoft Excel. MS Excel has a list of formulae to perform mathematical calculations. XIRR is listed under financial functions.
E.g., Mr. P chooses to invest in the XYZ equity mutual fund scheme through a monthly SIP of Rs.5,000 for one year (12 installments). He withdraws a sum of Rs.64,350 at the end of the term. What is the return he earns on his investment?
SIP starts on April 01, 2020
SIP ends on March 01, 2021
Monthly SIP Amount: Rs.5,000 (12 installments)
Total Amount Received on March 05, 2021: Rs.64,600
To calculate XIRR, open an Excel sheet and follow these steps:
Step 1: Column A has the serial numbers. In column B, enter the exact SIP dates one below the other
Step 2: In column C, enter the amount Rs.5,000 in negative to indicate a cash outflow
Step 3: Against the withdrawal date (March 05, 2021), enter the total amount received by Mr. P, i.e., Rs.64,350
Step 4: The XIRR formula in Excel is: XIRR (values, dates, [guess]). In the cell below the maturity amount, type as follows:
= XIRR (C1:C13, B1:B13)*100 and press Enter
| A | B | C |
| Serial No. | Dates | Cash flow |
| 1 | 1-Apr-2020 | -5000 |
| 2 | 1-May-2020 | -5000 |
| 3 | 3-Jun-2020 | -5000 |
| 4 | 2-Jul-2020 | -5000 |
| 5 | 1-Aug-2020 | -5000 |
| 6 | 4-Sep-2020 | -5000 |
| 7 | 1-Oct-2020 | -5000 |
| 8 | 5-Nov-2020 | -5000 |
| 9 | 1-Dec-2020 | -5000 |
| 10 | 2-Jan-2021 | -5000 |
| 11 | 3-Feb-2021 | -5000 |
| 12 | 1-Mar-2021 | -5000 |
| 13 | 5-Mar-2021 | 64,350 |
| XIRR | 16.07% |
As soon as you press Enter, the answer 16.07% gets displayed on the sheet
Note:
- Actual SIP dates need not be the first of every month. They can be different.
- The SIP amount represents a cash outflow to the investor and must be shown as negative. The maturity amount is a cash inflow, and hence it is shown as positive.
- If the investment is not yet withdrawn, you can use the current market value in place of the maturity amount to determine the XIRR.
- Please refer to the mutual funds’ account statement sent by the fund house to all its investors. Enter the transaction details as shown in this statement to get the correct results for your XIRR calculation.
- XIRR meaning is extended IRR. As simple IRR calculations can be erroneous when cash flows are irregular, XIRR must be used for calculations.
Proper Use of CAGR Method
The Compounded Annual Growth Rate (CAGR) is a very popular method used for the computation of mutual fund returns. But it is useful only in the case of lump sum investments that do not entail multiple interim cash flows.
E.g., Ms. R gets a bonus of Rs.50,000 in October 2016 and immediately invests the amount in the BCD equity mutual fund scheme. After six years, in October 2022, she decides to withdraw the investment that is presently worth Rs.1.10 lakh. If she wants to know the performance of the scheme, she can use the CAGR method.
CAGR = [(Final Value/Initial Value) ^ (1/n) – 1] * 100 (n is number of years)
= [(110,000/50,000) ^ (1/6) – 1] * 100
= 14.04%
So the initial investment of Rs.50,000 has grown at an annual rate of ~14% over six years to give Ms. R an amount of Rs.1.10 lakh.
Summing it Up!
The XIRR full form, meaning, formula & application are quite simple to understand for anybody. You should be fully invested in the investment process to make the best use of your hard-earned money. That’s precisely the reason behind quoting Warren Buffet’s golden words in the beginning. The more you learn, the more you can earn!
