What is XIRR in Mutual Funds

Optimise your mutual fund investments with XIRR – the key to assessing returns across systematic investment plans (SIPs). Elevate your financial strategy today.
What is XIRR in Mutual Funds
3 mins read
21-May-2024

XIRR, also known as the Extended Internal Rate of Return, is a metric used to calculate the return on investment for mutual fund investments. It is a mathematical formula used to measure the annualised return on investments that involve investments made and returns received at multiple time intervals. XIRR in mutual funds is a useful tool for investors to evaluate the performance of their investments. It takes into consideration the multiple investments and withdrawals made by the investor at different time intervals. In simple terms, XIRR is a method that calculates the returns on investments that happen at different points in time.

What Is XIRR?

XIRR considers various cash inflows and outflows in its formula. It calculates the annual average return for each installment and adjusts them to provide an overall average annual rate of return for all your investments.

This calculation is especially useful for estimating the return on your systematic investment plan (SIP), where you regularly invest in a mutual fund scheme.

Additionally, if you opt for the Systematic Withdrawal Plan (SWP), you can utilise XIRR to gauge your overall return. SWP allows you to make regular withdrawals of a predetermined amount at fixed intervals. Using XIRR in this context helps in evaluating the effectiveness of your investment strategy and the returns generated over time.

Understand how XIRR is accurate with an example

Let us explore the accuracy of XIRR with an example. Imagine you initiated a monthly SIP of Rs. 8,000 in a mutual fund plan and maintained it for 4 years. Assume that after numerous fluctuations, your total investment swelled to Rs. 7.5 lakh at the end of the 4-year period.

In this scenario, your initial Rs. 8,000 contributions were invested for 4 years, totaling 48 months. The annual return for the first month's contribution will differ because it was invested for the longest duration. As each contribution remains invested for varying periods, their respective CAGR also fluctuates. Calculating the CAGR for each contribution of a mutual fund plan could be complex to analyse.

Hence, to simplify matters, all these CAGRs are amalgamated and adjusted to a common CAGR. This adjusted CAGR is depicted as the XIRR of a mutual fund plan.

How XIRR works in mutual funds?

XIRR calculates the annualised return on investments by considering the cash flows in and out of the mutual fund investment at different points in time. These cash flows can be investments made by the investor, the income earned from the mutual fund investment and the withdrawals made by the investor.

Why is XIRR or Extended Internal Rate of Return important?

XIRR is a useful tool for computing returns, especially when dealing with irregular investment patterns over time. It allows you to precisely determine the return amount rather than relying on estimated returns based on compounding. With XIRR, you can assess whether your investment portfolio is delivering satisfactory returns or not. By applying the XIRR formula to each SIP payment or liquidation, you can accurately evaluate the overall value of your investment. This formula assigns specific dates to each cash flow (inflow and outflow), ensuring precise return calculations.

What is a good XIRR in mutual funds?

The good XIRR for mutual fund investments depends on several factors, such as investment goals, risk tolerance, and time horizon. A good XIRR for a mutual fund investment should be at least higher than the inflation rate. It is essential to check the XIRR of a mutual fund against its benchmark index and the average returns of similar mutual funds.

XIRR formula

The formula of XIRR is as follows:

XIRR = (NPV(Cash Flows, r) / Initial Investment) * 100

How to calculate XIRR in mutual funds?

You must enter the transactions (additional purchases, SIP/SWP instalments, redemption) and the related dates in the designated area of an MS Excel sheet in order to compute XIRR for mutual funds. The statement of account that the AMC (Asset Management Company) will send you will include information about these transactions.

You can use the following formula in MS Excel to determine a mutual fund's XIRR:
"=XIRR (values, dates, guess)"

Here, you must enter cash inflows (dividends, SWP, redemptions) as positive values and cash outflows (lump-sum purchases and SIP payments) as negative values (i.e., place a minus sign before the amount).

The entry "Guess" is optional. It is by default taken to be 0.1.
If you have not yet redeemed your mutual fund units, you must enter the current investment value along with the NAV (Net Asset Value) of your mutual fund investment to compute XIRR.

Transactions like dividend reinvestment should be excluded because they don't result in actual cash flows. Moreover, switches should be considered a form of redemption when determining the XIRR at the level of schemes. But switches are irrelevant in the computation if you are computing XIRR at the portfolio level for mutual funds.

Here’s the formula for calculating XIRR in excel:

XIRR formula in excel is: = XIRR (value, dates, guess)

Here's a breakdown of how to use it:

1. Prepare Your Data:

  • Create a table with three columns:
    • Date: Enter the dates (in year-month-day format) of your mutual fund transactions (purchases, redemptions, dividends).
    • Cash flow: Indicate the amount of each transaction. Use positive values for investments (purchases) and negative values for withdrawals (redemptions, dividends).
    • Units (Optional): If available, include a column for the number of units purchased/redeemed in each transaction. This can help visualize your investment activity.

2. Locate the XIRR Function:

  • Navigate to the formula bar in your Excel spreadsheet.
  • Click on the "Insert Function" button (usually denoted by Σfx).
  • In the search bar, type "XIRR" and select the function from the list.

3. Enter the Function Arguments:

  • The XIRR function requires two arguments:
    • Values: This refers to the range of cash flow values in your table. Select the entire cash flow column (excluding headers).
    • Dates: This refers to the range of dates corresponding to each cash flow. Select the entire date column (excluding headers).

4. Calculate the XIRR:

  • Once you've entered the arguments, press Enter. Excel will calculate the XIRR and display the annualised rate of return for your investment.

Example: Calculating XIRR for a Mutual Fund Investment

Let's say you invested Rs. 10,000 in a mutual fund on January 1, 2023, and then made additional investments of Rs. 5,000 each on July 1, 2023 and January 1, 2024. You also received a dividend of Rs. 1,000 on July 1, 2024. Here's how to calculate the XIRR:

1. Prepare your data table:

Date

Cash Flow (Rs.)

Units (Optional)

Jan 1, 2023

10,000

100

Jul 1, 2023

5,000

50

Jan 1, 2024

5,000

50

Jul 1, 2024

-1,000 (Dividend)

-


2. Locate the XIRR function and enter arguments:

=XIRR(B2:B5, A2:A5) (Replace B2:B5 and A2:A5 with your actual data ranges)

3. Calculate the XIRR:

Press Enter. Excel will display the XIRR value, which represents your annualized rate of return for this investment.

Step by Step process to calculate in Excel

The XIRR function in excel is a valuable tool for investors holding mutual funds with multiple transactions. It helps determine the internal rate of return (IRR) for these investments.

What are the benefits of calculating XIRR in a mutual fund

Below are the key advantages of utilising XIRR in assessing mutual funds or ULIP funds:

  • Precision: XIRR offers superior accuracy in gauging returns compared to CAGR by factoring in the timing of all cash flows. This proves crucial for both ULIP and mutual funds, where investments are often made periodically.
  • Steadiness: XIRR provides consistent return measurements, even when cash flows are irregular, unlike CAGR, which may mislead with irregular cash flows.
  • Adaptability: XIRR proves versatile in calculating returns for any investment, irrespective of cash flow patterns, making it an adaptable tool for evaluating investment performance.
  • Clarity: XIRR ensures transparent return evaluations by relying on actual cash flows, facilitating easy comprehension of investment performance.
  • Pertinence: XIRR holds relevance in assessing returns for ULIP funds and mutual funds by incorporating associated fees and expenses. Hence, it offers a more accurate estimation of anticipated returns.

What is XIRR in NPS?

XIRR can also be used to calculate the return on investment for investments made in the National Pension Scheme (NPS). It calculates the annualised rate of return on investment and takes into consideration the contributions made and the returns earned at different time intervals.

What is XIRR in NPS?

XIRR can also be used to calculate the return on investment for investments made in the National Pension Scheme (NPS). It calculates the annualised rate of return on investment and takes into consideration the contributions made and the returns earned at different time intervals.

What is the Difference between XIRR and CAGR?

Here are some of the differences between the XIRR and CAGR:

 

CAGR

XIRR

Definition

It is an absolute annualised return

It is an average annualised return

Calculation focus

Initial value, investment tenure, and final value

Every inflow and outflow of cash

Suitability

Ideal for lump-sum investments

Ideal for investments like SIPs with multiple inflows and outflows

Calculation method

Compounded annual growth rate

Average return generated by every cash flow throughout the investment tenure


Limitations of XIRR

Just as there are undeniable benefits, the XIRR return method also presents certain drawbacks.

Let us explore them:

  1. Dependence on accurate cash flow data: XIRR relies on precise and comprehensive cash flow data, encompassing both the date and amount of each cash flow. Inaccurate or incomplete data can compromise the accuracy of the XIRR calculation.
  2. Sensitivity to minor data alterations: XIRR is a highly sensitive metric, susceptible to even minor changes in the cash flow data. This sensitivity can pose challenges when comparing different investments or relying solely on XIRR returns for decision-making.
  3. Limited applicability to certain investment types: While ideal for investments featuring irregular cash flows, such as private equity or real estate investments, XIRR may not be suitable for those with regular cash flows, like bonds or annuities.

Could CAGR be preferable for determining returns?

When considering an investment in a mutual fund, we typically assess its performance over the past several years, such as three or five years. These performance figures represent compounded annual growth rates (CAGR), indicating the annualised growth of an investment over a specified period.

Evaluate whether past returns should influence your investment decisions.

This commonly used metric is employed to calculate investment returns across many mutual funds. While calculating CAGR for mutual funds is straightforward, it can be slightly more complex for personal investments.

Conclusion

As you can see from the examples above, XIRR is the best approach to calculating your real-world investment returns. CAGR is crucial to consider when choosing a mutual fund, but XIRR is critical when evaluating the returns on the investments you make. And IRR is employed for investments with equally spaced cash flows in time, but most investments are not as evenly spaced as you saw above in the case of mutual funds.

So, when a series of investments is made over time, involving transactions such as withdrawals, dividends, switching, and so on, XIRR is a superior approach to compute the return. XIRR is a far better tool for calculating mutual fund returns.

Calculate your expected investment returns with the help of our investment calculators
 

Investment Calculator

SIP Calculator

Lumpsum Calculator

Step Up SIP Calculator

Brokerage Calculator

FD calculator

Mutual Fund Calculator

Frequently asked question

What is XIRR in mutual funds?

XIRR is an extended internal rate of return which is a method to calculate your returns on mutual fund investment at irregular periods of intervals.

How is XIRR Calculated?

XIRR can be calculated easily by using an inbuilt Excel function. Its formula is "=XIRR (values, dates, guess)".

How XIRR is different from absolute return?

Absolute return is determined by calculating the total return based on the future value of the investment and the initial investment amount, without considering the timing of cash flows. On the other hand, XIRR also evaluates investment returns, but it takes into account both the timing and the amount of cash flow.

How much XIRR is good for mutual funds?

There isn't a specific XIRR percentage that defines "good" for mutual funds. It varies based on individual investment goals and risk tolerance.

Is XIRR better than CAGR?

XIRR (Extended Internal Rate of Return) and CAGR (Compound Annual Growth Rate) serve different purposes. XIRR is more versatile for irregular cash flows, while CAGR is simpler and more suitable for consistent growth rates.

Is XIRR compounded annually?

XIRR calculates the annualised rate of return for investments with irregular cash flows, which may occur at varying intervals. It is not limited to annual compounding.

Why does XIRR in mutual funds make financial sense?

XIRR provides a comprehensive measure of the performance of mutual fund investments, considering both inflows and outflows over time, offering a more accurate assessment of returns.

What are the limitations of XIRR?

Limitations of XIRR include sensitivity to the timing and frequency of cash flows, potential for inaccurate results with certain types of investments, and complexity for some users.

What does XIRR of 10% mean?

An XIRR of 10% signifies the average annualized return generated by an investment over its entire period, considering both inflows and outflows of cash, which amounts to 10% annually.

Is 100% XIRR good?

A 100% XIRR indicates that the investment has doubled in value annually on average. While it may seem impressive, it is crucial to assess it within the context of risk and investment objectives.

Can we convert XIRR to CAGR?

XIRR and CAGR are different metrics, each serving distinct purposes. While both represent annualised returns, XIRR considers irregular cash flows, while CAGR focuses on a single initial and final value. Hence, they cannot be directly converted.

Show More Show Less

Bajaj Finserv App for All Your Financial Needs and Goals

Trusted by 50 million+ customers in India, Bajaj Finserv App is a one-stop solution for all your financial needs and goals.

You can use the Bajaj Finserv App to:

  • Apply for loans online, such as Instant Personal Loan, Home Loan, Business Loan, Gold Loan, and more.
  • Explore and apply for co-branded credit cards online.
  • Invest in fixed deposits and mutual funds on the app.
  • Choose from multiple insurance for your health, motor and even pocket insurance, from various insurance providers.
  • Pay and manage your bills and recharges using the BBPS platform. Use Bajaj Pay and Bajaj Wallet for quick and simple money transfers and transactions.
  • Apply for Insta EMI Card and get a pre-approved limit on the app. Explore over 1 million products on the app that can be purchased from a partner store on No Cost EMIs.
  • Shop from over 100+ brand partners that offer a diverse range of products and services.
  • Use specialised tools like EMI calculators, SIP Calculators
  • Check your credit score, download loan statements and even get quick customer support—all on the app.

Download the Bajaj Finserv App today and experience the convenience of managing your finances on one app.

Do more with the Bajaj Finserv App!

UPI, Wallet, Loans, Investments, Cards, Shopping and more