What is XIRR in Mutual Funds?
XIRR is a method of calculating and representing mutual fund annual returns when several cash flows (investments and withdrawals) are involved.
XIRR, or Extended Internal Rate of Return, is the most accurate way of looking at mutual fund SIP (Systematic Investment Plan) returns since SIPs involve multiple transactions.
In this article, we help you understand XIRR with simple explanations and examples. Let’s first understand why XIRR is required in the first place.
Why is XIRR required in mutual funds?
XIRR is required because other return representation methods are insufficient to represent annual returns when several transactions or cash flows are involved.
Absolute return, the simplest return representation method, considers only the final and initial investment values. The formula for absolute return doesn’t have a provision to include multiple transactions.
CAGR, or Compounded Annual Growth Rate, represents the annual returns of a single investment (without any interim transactions) for a specific period.
However, if multiple transactions are involved, CAGR cannot account for them. So, we need XIRR to accurately represent the annual returns of a series of cash flows.
How is XIRR calculated for mutual funds?
Unlike calculating absolute return or CAGR using a simple formula, calculating XIRR is not straightforward.
Here is what you need to calculate XIRR in Microsoft Excel or Google Sheets:
- Dates of transactions
- Transaction values (negative for cash outflow: like a SIP, and positive otherwise)
Steps to calculate XIRR:
- Organise the required data properly. Put all the dates in one column and the transaction value in the adjoining column.
- Ensure that the transaction values are properly defined. Make sure that inflows are positive and outflows are negative.
- In the cell where you want the XIRR type, “=XIRR” to get prompts for what data to input. After typing “=XIRR”, you must select all the values (or transaction values) and then select all dates.  is an optional field; you need not define it.
- Hit enter to get the XIRR of your cashflows/transactions.
Let’s use an example and demonstrate the XIRR calculation process.
Example: XIRR calculation using XIRR formula
Suppose you had a 6-month mutual fund SIP of Rs. 20,000 on the 1st of every month from 1st Jan to 1st June. You made a partial withdrawal on 12th April of Rs. 8,000, and the value of your investments on 10th June is Rs. 1,12,893. So the transactions look like:
|SIP instalment||1 Jan 2023||-₹20,000||Outflow|
|SIP instalment||1 Feb 2023||-₹20,000||Outflow|
|SIP instalment||1 March 2023||-₹20,000||Outflow|
|SIP instalment||1 April 2023||-₹20,000||Outflow|
|Partial withdrawal||12 April 2023||₹8,000||Inflow|
|SIP instalment||1 May 2023||-₹20,000||Outflow|
|SIP instalment||1 June 2023||-₹20,000||Outflow|
|Present value||10 June 2023||₹112,893||Inflow|
The SIP installments have negative values because they are outflows. The partial withdrawal and present value as of 10 June have positive values because they are considered as inflows.
Once you organise the data in this manner, calculating XIRR is a matter of using the XIRR function:
XIRR = (values, dates, [guess])
Don’t use XIRR in the short term
While the right metric to measure mutual fund SIP annual returns, XIRR is very sensitive in the short term or periods shorter than 1 year.
This is easy to understand with an example.
Let’s say you invest Rs. 10,000 on 1 October 2023 as your first SIP instalment and check the value of your investment on 5 October 2023. Suppose the investment gained a very modest Rs. 50 and the value of your investment is not Rs. 10,050.
|SIP instalment||1 Oct 2023||-₹10,000||Outflow|
|SIP instalment||5 Oct 2023||₹10,050||Inflow|
As you can see, the XIRR comes out to be 57.64%. This is not a realistic indicator of the expected long-term returns from mutual funds.
Over the long term, equity funds are expected to grow at low double digits, and debt funds are expected to grow at high single digits annually.
Because of how sensitive XIRR is in the short term, it is quite standard for investment apps/platforms to show the absolute return instead of XIRR when your portfolio’s age is less than 1 year.
If your app/platform doesn’t do this, make sure you don’t consider your young portfolio’s volatile XIRR as your benchmark for future long-term returns.
Frequently Asked Questions about XIRR
What is the full form of XIRR?
XIRR stands for Extended Internal Rate of Return and represents the annual return of a series of irregular cash flows or transactions like a mutual fund SIP.
Is XIRR better than CAGR?
XIRR is better than CAGR because while XIRR can represent the annual returns when multiple transactions are involved, CAGR can represent the annual returns of a single (one-time) investment only.
What does negative XIRR mean?
Negative XIRR means that your annual investment return is negative or less than zero because the present value of your investment is lower than the amount you invested.
What is the difference between IRR and XIRR?
IRR or Internal Rate of Return assumes that the cash flows are regular (or occurring a specific frequency like yearly) whereas XIRR is used to calculate annual returns for irregular cash flows.
What is a good XIRR for mutual funds?
A good XIRR depends on the mutual fund. Generally speaking, equity funds will tend to have a higher XIRR than debt funds over the long term. However, equity funds are also riskier than debt funds and may not be suitable for everyone.