The main purpose of investing is to grow your hard-earned money and get a good return on it. You may earn a return in many ways by capital appreciation or in the form of receiving dividend income. When it comes to bank deposits and traditional forms of fixed-income investments, calculating the return becomes easy as we use either simple interest or compound interest methods to know the return. When it comes to market-linked investments like mutual funds, returns are generally measured using CAGR (compounded annual growth rate) and XIRR (extended internal rate of return) methods. Generally, CAGR is used to measure the returns from lump sum mutual fund investments. XIRR method is used for systematic investment plans as these mutual fund investments would have multiple cash flows. XIRR can also be used for lumpsum investments if it involves additional purchases and multiple redemptions. Let’s know more about the Extended internal rate of return or XIRR.
XIRR meaning: Extended internal rate of return or XIRR in mutual funds is your personal rate of return that lets you know the actual return on your investment. Mathematically, XIRR meaning is a single rate of return applied to every transaction (purchase and redemption). XIRR in mutual funds is a method used to calculate the return on investments when there are multiple transactions in a single folio. For example, you have invested in a monthly systematic investment plan for the next twelve months on the 10th of every month. In this case, there will be twelve transactions happening on different dates for different net asset values. Hence, XIRR is used to get the most accurate return on your investment.
You can use Microsoft Excel to calculate the extended internal rate of return or XIRR in mutual funds as it has the built-in function. Specifically, for systematic investment plans that involve cash flows every month, XIRR gives you an accurate return on your investment. Each SIP purchase has a different NAV and investment date.
Here is a step-by-step calculation of XIRR using Microsoft excel
Let’s understand this with an example. Let’s say you have completed 12 installments of the SIP investment (INR 2,000 per month) that was started last year. Your total investment is INR 24,000 and let’s say the current value of your investment is INR 26,000. This is how it looks when you calculate XIRR for this using the spreadsheet.
SIP purchase | 10-06-2021 | -2000 |
SIP purchase | 10-07-2021 | -2000 |
SIP purchase | 10-08-2021 | -2000 |
SIP purchase | 10-09-2021 | -2000 |
SIP purchase | 10-10-2021 | -2000 |
SIP purchase | 10-11-2021 | -2000 |
SIP purchase | 10-12-2021 | -2000 |
SIP purchase | 10-01-2022 | -2000 |
SIP purchase | 10-02-2022 | -2000 |
SIP purchase | 10-03-2022 | -2000 |
SIP purchase | 10-04-2022 | -2000 |
SIP purchase | 10-05-2022 | -2000 |
Current Value | 09-06-2022 | 26000 |
XIRR (In percentage) | 15.84 |
XIRR is the only method that can give you an actual rate of return when the investment involves multiple transactions and uneven cash-flow intervals. Basically, XIRR is the aggregation of multiple CAGRs (compounded annual growth rates) of all the transactions. While using the XIRR function in Microsoft Excel, it is important to enter the transactions in the order and ensure to enter cash outflows in negative values and all the cash inflows in positive values.
Actual returns are the only way to measure your investment performance. Hence, it is crucial to know the return measures used for each investment.