XIRR is a mathematical formula that allows fixing a number on the gains/losses obtained from the amount invested in Mutual Funds. The gains/losses are expressed as a rate of return. This is the most adopted method to determine the extent to which the maturity value has yielded for you as an investor in this industry.
XIRR stands for Extended Internal Rate of Return. There are multiple rates-of-return techniques in the market such as Internal Rate of Return (IRR) and Compound Annual Growth Rate (CAGR). This article will help you understand why XIRR stands a cut above the rest when it comes to mutual funds.
XIRR helps to identify the rate of return from a particular Systematic Investment Plan (SIP). For example, as an investor, we input a certain amount of funds on the 8th of every month. Initially, you may think of it as a sum allocated every 30 days. This may be a fairly reasonable assumption to make, but when fund managers deal with higher quantum of funds and wish to know the accurate performance details of their funds, the industry adopts XIRR. Discrepancies arise from the fact that some months have only 28 days, while remaining months may be 30 or 31 days long. Over longer time periods, this would be a key concern.
IRR is the base on which XIRR is built. IRR finds the same rate of return without accounting for the minor differences in tenure on the 8th of every month. Furthermore, IRR holds valid given the cash flows for a SIP are regular. If cash flows are at irregular intervals, XIRR is the only choice.
Illustration 1: SIP at the 8th of Every Month
As shown in illustration 1, When you invest Rs 10,000 on the 8th of every month from April 2022 for 2 years, XIRR works out to be 23.01%. In contrast, the IRR value will be 1.74%. IRR will reveal the same value even if you changed the dates to 8th of April every year from 2022 to 2046. Eventually, IRR considers the time gap between each cash flow as constant. This is the reason for distinct values between IRR and XIRR.
Illustration 2: XIRR’s significance given uneven interval between cash flows
Let us say a mutual fund requires one to invest Rs 5000 or Rs 10,000 in five steps as per the above illustration. The 6th step refers to the pay-out by the Asset Management Company (AMC) for a sum of INR 50,000. This occurs at the end of 1 year after the 5th step. Over 6 years, IRR and XIRR arrive at the same value because both formulae annualise the returns based on an algorithm that treats a year as 365 days long.
Illustration 3: Reconstruct of Illustration 1 on an annual cash flow basis
Now consider illustration 1 as investment poured into a mutual fund on a yearly basis. This returns us an XIRR of 15.83% as reflected in illustration 3. This varies with the XIRR observed in illustration 1, which exhibited 23%. In illustration 1, we are considering monthly intervals, whereas in illustration 3, we are considering annual interval gaps. XIRR gives us a return as per the time frame considered, which is a vital value added.
As per illustration 3, the XIRR and IRR work out to be identical again. It is undertaken only to understand that the time gap must be carefully specified when using XIRR.
XIRR is an inbuilt function in Microsoft Excel, which helps simplify our effort. We need to prepare a table with the corresponding dates and cash flows before using the XIRR formula in excel whose step-by-step execution is outlined in the next section.
5. Guidelines for cash outflow and inflow
| Positive + Sign | Negative – Sign | |
| E.g. | Dividends, redemptions | SIP instalments, lump-sum purchases |
| Why? | Inflow of cash to investor | Outflow of cash from investor |
CAGR is used when we want to find out the rate of return from an initial investment to its maturity value. CAGR compares the initial and final points and does not account for multiple cash flows. The time gap is immaterial here as multiple cash flows are not applicable.
XIRR is adopted when we want to find out the rate of return by considering multiple cash flows over irregular time gaps.
Both IRR and XIRR are applied where there are multiple cash flows. IRR considers the time gap between each cash flow to be the same. This is rarely true in the case of mutual funds. In contrast, XIRR can adjust for discounts based on irregular gaps between cash flows. Hence, XIRR is used predominantly to identify the rate of return for mutual funds where each cash inflow or outflow can occur at irregular spacing.
In mutual funds, the rate of returns is best assessed using XIRR.
To evaluate the growth/degrowth of any investment in assets like land or gold, the CAGR will yield a rate of return because we have the purchase cost and current market price.
As straightforward as CAGR may seem, it does not work with mutual funds. In mutual funds, considering a series of cash flows, with typical outflows initially before redemption of the same at the end of the maturity, we are found with no investment value to begin with. If we tweak it to INR 10,000 based on illustration 1, and final maturity value as INR 3,00,000 as per the same illustration, we will get a very unrelated value that means little to us. The input is not really INR 10,000. Moreover, it is unfair to input INR 1,20,000 as an initial investment in the CAGR formula as the sum is input in instalments over months.
Illustration 6: CAGR and Path of Growth
Notice in above two examples within illustration 6, while calculating CAGR, the return rate remains the same because only two values are influential. The path of growth or degrowth in the NAV of any mutual fund remains neutral to the result. Notice the investment’s value fluctuating in the 2nd example tabulated above. CAGR is not sensitive to the market fluctuations. It takes a long view of the start and end over a time frame. Therefore, CAGR remains best used for evaluating the return on assets that have a single purchase value and a single selling value.
Illustration 7: CAGR’s limitation over the short term (i.e., less than or equal to 1 year)
Consider initial investment as INR 50,000 per illustration 7. The maturity is obtained at the beginning of 2023 or end of 2022. For shorter periods like one year, this translates to an increase or decrease in investment from the initial value multiplied by 100%. The compounding part of the CAGR is overlooked for periods lesser than equal to 1 year.
It would be one-sided to put a number on the best XIRR values in mutual funds. First and foremost, mutual funds have several variants in each fund house. Each variant is structured based on an underlying theme, industry, valuation, manager’s criteria, mix between debt and equity, etc. Going through the entire universe to arrive at a single number will not be an ideal benchmark to be followed.
Instead, as an investor, you should be aware of how your money will return for you in a few years’ time. You can try this XIRR technique in your own space or try out several free online calculators found on the websites of various mutual fund houses.
You could compare the XIRR with similar mutual funds and check if your investment decision is right or needs to be changed. What has been promised to you cannot always be true. This is when the reality check by XIRR will come in handy.
A good value for XIRR will be any return that is superior to base rates in the market such as fixed deposit rates. If you are even more aggressive and expect far superior returns, you can benchmark against NIFTY or SENSEX rate of growth in the past one year and check your SIP’s XIRR across the same timeline. If the returns exceed either of the above two categories, your funds are value accretive.
| CAGR | IRR | XIRR | |
| Expansion | Compound annual growth rate | Internal rate of return | Extended internal rate of return |
| What is it? | Rate of investment growth over a period with focus on initial and ending values only for investment | To calculate the returns from a series of cash flows given multiple transactions taking place at varying points of time which would all be considered at equal time intervals only | Helps to calculate rate of return given multiple transactions taking place at varying points of time with even or uneven cash flow intervals See Illustration 2 |
| Where do you use it? | Applicable where growth of investment is concerned. Single cash inflow and Final cash maturity is compared. Multiple cash flows, in or out, remain irrelevant in this context. | Applicable where multiple cash flows occur (in and out) | Applicable where multiple cash flows occur (in and out) and overcomes the limitation faced by IRR, i.e. treatment of uneven gap between cash flows |
| Excel formula at a glance | =( ( End Value of Investment/ Initial Value of Investment ) ^( 1/time period in concern ) ) – 1 | =IRR (cash flow cells in series) | =XIRR (cash flow cells in series, time frame cells in series) |
| Expansion of MS Excel formula | The way a mutual fund’s NAV changes over time is not considered. Final maturity value divided by initial investment can be taken as (a/b). This ratio is raised to the inverse of the time period, say n years, i.e. raised to the power of (1/n). Resultant value subtracts 1 to find out rate of return. | Cashflows must be mentioned in series i.e. a linear fashion of cells continually with corresponding dates in adjacent cells. Cash flows are positive during inflow and negative during outflow. | Assigns specific date for cash flows for more accurate return calculation Cashflows must be mentioned in series. Cash flows are positive during inflow and negative during outflow. |
| Limitations of technique | The way the Net Asset Value (NAV) of a Mutual Fund or your investment in any asset grows or falls over the time frame is not material. See Illustration 6. Technique does not work for short term, say 1-year period. The CAGR over 12 months is the same as that of 12 years. Hence, CAGR is usually taken for years across a time frame. See Illustration 7. | Time gap between each cash flow is assumed to be constant. See Illustration 1. Rate of return cannot be determined if the final redemption value is not entered. | Rate of return cannot be determined if the final redemption value is not entered. Rate of return cannot be determined if the final redemption value is not entered. |
CAGR is not particularly useful in evaluating the returns from a mutual fund due to its inherent structure, which compares the initial input investment and final maturity value. CAGR cannot consider the cash outflows across a time period as in the case of IRR or XIRR. However, it has its own significance in other financial areas to compare the rate of return between two asset classes such as gold or land for instance.
In the mutual funds space, IRR is a quick tool akin to CAGR. It is quick to find out and serves its purpose. It will not be as accurate as XIRR considering the limitations highlighted earlier. To sum up, XIRR takes IRR to another advanced level and flattens the limitations faced by IRR.
