XIRR In Mutual Funds: Meaning, Types & Importance Explained

What is XIRR?

XIRR stands for Extended Internal Rate of Return. Any investment’s main goal is to generate returns. Returns could take the shape of income, capital growth, or perhaps both. Compound annual growth rate (CAGR) and XIRR are the two most used metrics for mutual fund returns. CAGR, as the name suggests, is the rate of growth of your investment, assuming annual compounding, during the investment period. While XIRR in mutual funds can be applied for multiple cash flows, CAGR or point-to-point returns are not applicable or cannot be employed in the case of multiple cash-flows. When there are many transactions occurring at various periods, the XIRR formula is used to determine investment returns.

Why Does it Make Sense for Mutual Fund Investments?

If your spending is evenly spaced out throughout time, you can use this principle to calculate how much money you have made from your investments. Nevertheless, assets are typically not distributed as uniformly as you saw above for mutual funds. With mutual funds, you frequently make investments and redeem them at random times. At various times, it will result in cash inflows and outflows. In this kind of situation, in addition to the quantity invested, the timing of such an investment also gains relevance to produce a particular result. Use the extended internal rate of return notion here (XIRR). So, when your cash flows (investments or redemptions) are dispersed over time, XIRR is a good function to calculate returns. In the case of mutual funds, XIRR can handle all those eventualities and assist you in calculating a consolidated return taking timings of your investment and withdrawals into consideration. You can invest using SIP or lump-sum, or redeem using SWP or lump-sum.

XIRR Calculation

MS Excel makes calculating XIRR simple. A built-in function of XIRR calculator in Excel can be used to determine XIRR. For computing the annualised yield for a schedule of cash flows happening at irregular intervals, Excel’s XIRR function is more potent. Excel’s XIRR formula is: = XIRR (value, dates, guess) The steps are as follows for Excel:

1. Transactions to be entered in one column and all outflows such as purchases  to be marked negative and inflows like redemption market positive
2. Add corresponding date of transaction to next column
3. Mention current value of holding in last column
4. XIRR function in excel to be used here
5. Select values to a series of cash flows corresponding to a schedule of payments.

Example of How to Use the XIRR Function in Excel

To illustrate the use of the XIRR function in Excel, particularly for computing the return on an investment made through a Systematic Investment Plan (SIP) with irregular intervals, let’s modify the given example. We will calculate the internal rate of return for a six-month SIP investment with the following details:

• SIP Amount: ₹ 6000
• Investment Start Date: 02/01/2017
• Investment End Date: 02/06/2017
• Redemption Date: 02/07/2017
• Maturity Amount: ₹ 36000

We will use the modified cash flow set in the table below, representing the amounts invested or redeemed on specific dates. Note that investments (cash outflows) are represented with a negative sign, while the redemption (cash inflow) is positive.

Steps to Calculate XIRR in Excel:

1. Open Excel and prepare two columns: one for dates and one for cash flows associated with those dates.
2. In the ‘Date’ column, enter the dates when the SIP investments were made and the date of redemption. In the ‘Cash Flow’ column, enter the corresponding amounts invested (as negative values) and the maturity amount (as a positive value). Your table should look like the above table.
3. After entering the data, use the XIRR function to calculate the internal rate of return. Click on an empty cell where you want the result to be displayed.
4. Enter the XIRR formula: =XIRR(B2:B7, A2:A7). Here, B2:B7 represents the range of your cash flows, and A2:A7 represents the range of corresponding dates.
5. Press Enter to calculate the internal rate of return for your SIP investment.

Remember, XIRR function returns the result as a decimal, so you might want to format it as a percentage to make it easier to read. The calculated return rate reflects the annualised yield on your investment, considering the specific timing of each cash flow.

Can We Use CAGR Instead for Calculating Returns?

When we want to invest in a mutual fund, we first look at its results over the previous three, five, etc. years. These returns are point-to-point returns and are denoted by the acronym CAGR, or compound annual growth rate. CAGR is easily determined by applying the following formula: CAGR = ((Ending Amount/Beginning Amount)^(1/No. of years)) – 1 This common statistic is used to estimate investment returns in many mutual funds. While calculating CAGR for a mutual fund is simple, doing it for individual investments can be a little challenging.

Importance of XIRR in Mutual Funds

Thankfully, you can use the XIRR method in Excel sheets to handle irregular cash-flow times. Simply said, XIRR is IRR modified to allow for the flexibility of assigning precise dates to individual cash flows, making it a considerably more accurate method of calculating returns. You only need to enter the transactions (SIP/SWP instalments, additional purchases, redemptions, etc.) and the relevant dates to find out the XIRR for mutual funds. You can use the XIRR formula in an excel sheet to obtain these transactional facts from the statement of account that the fund firm sent.

Conclusion 

We covered the significance of XIRR in mutual funds in this post, as well as how to utilise it to estimate various cash flows, such as SIP, SWP, extra purchases, dividends, partial redemptions, etc. CAGR is a term that is well-known among investors. Similar to CAGR, XIRR in MF employs the idea of compounding; in fact, you may consider XIRR to be an accumulation of CAGRs. If your mutual fund investments have multiple cash flows, you must use XIRR. FAQs

How much XIRR is considered good?

A good XIRR is an arbitrary number that varies from person to person. Nonetheless, analysts have noted that an XIRR of 11–12% for equities funds and 7-8% for debt mutual funds is regarded as good.

Does XIRR assume daily compounding?

The XIRR function assumes a stream of equally spaced, annual cash flows, but the XIRR function returns the effective annual rate with daily compounding.

What is the importance of XIRR?

Extended Internal Rate of Return is referred to as XIRR. It can be used as a tool to calculate the returns on your investments in mutual funds at erratic periods. You might determine the current value of your whole investment by using the XIRR calculation to each SIP instalment or liquidation, if any.

Does XIRR return an annual rate?

An annualised return is known as an XIRR. The annualised return shows how much an investment would earn over time if it were compounded annually.