What is XIRR in Mutual Funds

What is XIRR in mutual funds?

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.

Importance of XIRR in 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 8^th 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 8^th 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 8^th of Every Month

As shown in illustration 1, When you invest Rs 10,000 on the 8^th 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 8^th 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 6^th 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 5^th 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.

Is XIRR a good measure?

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.

How to Calculate XIRR in an Excel Sheet?

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.

Step by step Process to Calculate XIRR in Excel

1. Enter SIP transaction dates in one column → Dates.
2. Enter SIP amounts in the adjacent column as per guidelines for cash outflow and inflow  → Values.
3. Remember to check for the redemption amount and date as the last entry in the table.
4. Use the XIRR formula by selecting the cells as per syntax.
5. The syntax is = XIRR (values, dates, [guess]).
6. Guess is an optional field and is assigned a default of 0.1 if nothing is entered. Since the rate of return is arrived at by an iterative process, XIRR runs through the calculation until the result is accurate within 0.000001 percent.

Points to consider while calculating XIRR in MS Excel?

1. The cash flows must be predicted and entered in full per scheme documents. If you are working this out midway through your journey of SIP, and not sure about the cash flows, you need to get in touch with your personal advisor or download your scheme’s fact sheet to get the details required. Fill the full series and get a better picture of your gains as a rate of return.
2. The dates must be accurately entered over the concerned time frame. If there is a cash inflow and outflow on the same date, you can use two entries to record the same separately. Do not use a comma to input two values in the same cell for an amount against a single date.
3. Format of date needs to be checked if it appears as text. In such cases, this formula will not work. 
4. The values for cash flows must have one negative and one positive value mandatorily.

5. Guidelines for cash outflow and inflow

Difference Between XIRR and CAGR

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.

Difference between XIRR and IRR

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.

Can We Use CAGR Instead of XIRR?

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.

What is the best XIRR in mutual funds?

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.

Overview

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.

Don’t miss out on this
out-of-the-world
banking experience

Don’t miss out on this out-of-the-world banking experience

Download now

✅ Open a Savings Account in 3 minutes

✅ Earn 1% cashback on purchases, and additional 10% on subscriptions

✅ Get spends categorised in real time. Manage your money better