XIRR in Mutual Funds

XIRR is for Extended Internal Rate of Return. It is a financial measure that helps you see how well your market-linked assets are doing. It figures out the yearly rate of return for both regular and irregular investments that have more than one cash flow. XIRR is a more accurate measure because it takes into consideration the exact time and amount of cash flows.

What is XIRR?

The full form of XIRR is Extended Internal Rate of Return. It is a way to figure out how much money you made on investments when there are many transactions happening at various times. XIRR is not the same as the Compound Annual Growth Rate (CAGR), which only works for investments with one cash flow.

People often use XIRR to figure out how much money they make from their market-linked funds, such as XIRR in Mutual Funds, XIRR in Unit Linked Insurance Plan (ULIP), and XIRR in NPS (National Pension Scheme).

What are Multiple Cash- Flows in XIRR?

The XIRR rate of return is calculated by considering the irregular cash flows made at different intervals. These transactions cover the following investments:

Investments through a Systematic Investment Plan (SIP),
Withdrawals made through Systematic Withdrawal Plans (SWP),
Additional purchases of units
Returns deposited in your fund,
Redemption of your fund

NOTE: Policybazaar has an online SIP calculator to get the estimated returns on SIP investment.

IRR vs. XIRR

The Internal Rate of Return (IRR) and Extended Internal Rate of Return (XIRR) are both used to calculate the performance of your investments, but they differ by the cash flows they handle:

Internal Rate of Return (IRR): It takes into account all the transactions, but assumes they occur at equal intervals of time. IRR is suitable for investments with consistent cash flows and is not accurate for investments that have cash flows that aren't steady.
Extended Internal Rate of Return (XIRR): It considers both the amount and timing of cash flows, along with the exact date of their occurrence. The XIRR gives a better picture of assets with cash flows that don't happen on a regular basis.

Feature	IRR (Internal Rate of Return)	XIRR (Extended Internal Rate of Return)
Definition	Rate of return on an investment where Net Present Value (NPV) is zero	Rate of return on investments with irregular cash flows
Cash Flow Timing	Assumes regular, periodic cash flows (e.g., annually)	Accounts for cash flows occurring at irregular intervals
Use Case	Best for investments with regular cash flow intervals	Ideal for investments with varying cash flow dates
Calculation Method	Uses a fixed interval for calculations	Uses exact dates for each cash flow
Accuracy	Less accurate with irregular cash flows	More accurate with irregular cash flows
Example	Regular Cash Flows: Initial Investment: -₹10,000 Year 1: +₹3,000 Year 2: +₹3,000 Year 3: +₹3,000 Year 4: +₹3,000 Year 5: +₹3,000	Irregular Cash Flows: Initial Investment: -₹10,000 (on 01-Jan-2020) Year 1: +₹2,000 (on 15-Feb-2021) Year 2: +₹5,000 (on 10-July-2022) Year 3: +₹4,000 (on 22-Nov-2023) Year 4: +₹7,000 (on 05-May-2024)

Why to Calculate XIRR in a Mutual Fund/ ULIP Fund?

It is essential to calculate XIRR in a mutual fund and ULIP fund for several reasons, some of which are as follows:

To get a more accurate measure of your returns for investments with irregular cash flows, as you invest and withdraw multiple times in mutual funds and ULIPs.
XIRR is a versatile tool that can be applied to any of the best investment plans, considering various cash flow patterns.
To track the performance of your investments over time and make better financial decisions.

How to Calculate XIRR?

You can use an Excel spreadsheet to calculate the XIRR return, which can handle data with multiple cash flows happening at irregular intervals. These cash flows can be gains/ returns/ redemption (positive numbers) or SIP/ deposits (negative numbers).

XIRR Formula in Excel:

XIRR in Excel = XIRR (cash flows, dates)

Where:

Cash Flows: The range of cells that contain the cash flows
Dates: The range of cells that contain the dates of the cash flows

Steps to Calculate XIRR in Excel:

Step 1: List all your cash flows in one column as per below-

Inflows as positive (+)
Outflows as negative (-)

Step 2: Write the dates in a column for each of the respective cash flows.
Step 3: Click on a cell where you want the XIRR result to appear.
Step 4: Enter the formula: =XIRR(cashflow_range, dates_range)
Step 5: Press Enter.

The Excel software will show the XIRR rate of return for the given cash flows as a percentage.

Illustration to Calculate XIRR in Excel:

Enter the cash flows in the Excel sheet as mentioned in the above steps. Here is an example:

A	B
Date	SIP Amount
01/01/2023	-20000
01/02/2023	-500
10/10/2023	-300
02/04/2023	10000
17/05/2023	27000
05/06/2023	105
08/07/2023	-500
01/08/2023	10000
10/09/2023	4500
14/10/2023	-1000
XIRR =	8.664488108

The XIRR is 8.66% p.a. which represents the internal rate of return for your cash flows, expressed as a yearly percentage.

XIRR Vs CAGR

CAGR, like XIRR, is a key metric that can be used to estimate the rate of return for your market-linked investments easily. A quick definition of both is as follows-

XIRR (Extended Internal Rate of Return): It calculates the annualized return on investments with irregular cash flows on different dates.
CAGR (Compound Annual Growth Rate): It measures the mean annual growth rate of your investment over a specific period.

The key differences between XIRR and CAGR are mentioned in the table below:

Feature	XIRR	CAGR
Full form	Extended Internal Rate of Return	Compound Annual Growth Rate
Definition	Rate of return on investments with irregular cash flows.	Annual growth rate of an investment over a specified period.
Cash Flow Timing	Consider cash flows occurring at irregular intervals.	Assumes a single investment with no intermediate cash flows.
Calculation	It takes into account the exact timing and amount of cash flows, which makes it good for investments and withdrawals that aren't always the same.	It assumes a constant rate of growth over a specified period, regardless of cash flow timing.
Applicability	Applicable on investments with multiple cash flows.	Applicable on investments with a single cash flow.
Accuracy	Assumes a single investment with no intermediate cash flows.	Accurate for measuring consistent annual growth.
Example	Irregular Cash Flows: Initial Investment: -₹10,000 (on 01-Jan-2020) Year 1: +₹2,000 (on 15-Feb-2021) Year 2: +₹5,000 (on 10-Jul-2022) Year 3: +₹4,000 (on 22-Nov-2023) Year 4: +₹7,000 (on 05-May-2024)	Single Investment Growth: Initial Investment: ₹10,000 Value after 5 years: ₹16,000

Wrapping It Up

XIRR (Extended Internal Rate of Return) is a valuable metric for evaluating the performance of ULIP and mutual funds. This method accounts for the time and amount of your investments by considering both inflows and outflows. XIRR offers a more accurate representation of an investment fund's performance, helping you to make informed decisions about your investments.

Frequently Asked Questions

What is a good XIRR rate?

The definition of a good XIRR rate depends on a number of factors, including the asset class, the investment horizon, and your risk tolerance. However, as a general rule of thumb, an XIRR of 11-12% is considered good for equity funds, and 7-8% is considered good for debt funds.
Is XIRR better than CAGR?

XIRR is considered to be a better measure of investment returns than CAGR, especially for investments with irregular cash flows, such as SIPs. However, CAGR is easier to calculate and understand, so it is often used as a more general measure of investment performance.
What is the formula for XIRR?
The XIRR formula is: XIRR(values, dates, [guess])
where:
- Values: An array of cash flows, where a negative value represents an outflow and a positive value represents an inflow.
- Dates: An array of dates corresponding to the cash flows.
- Guess: An optional argument that provides an initial guess for the XIRR. If omitted, Excel will use a default value of 10%.
What is the major difference between IRR and XIRR?

The major difference between IRR and XIRR is that IRR assumes that all cash flows in your investment fund occur at the end of the period, while XIRR takes into account the actual timing of the cash flows. This makes XIRR a more accurate measure of the true returns of an investment, especially for investments with irregular cash flows.
What is XIRR?

XIRR means Extended Internal Rate of Return for irregular cash flows. It is a financial metric used to calculate the return on an investment when cash flows occur at uneven intervals.
How is XIRR different from IRR?

IRR and XIRR both help you to assess the performance of your investments. However, the IRR function assumes cash flows happen at regular periods, while, XIRR considers uneven cash flows at different time intervals. This makes the XIRR more versatile for real-world scenarios.
What are the benefits of using XIRR?

The benefits of XIRR are that it provides a more accurate picture of investment performance when cash flows are irregular and helps compare investments with different cash flow patterns.
What are the disadvantages of XIRR?

The main disadvantage of XIRR is that it assumes all cash flows are reinvested at the Internal Rate of Return (IRR), which might not always be true. It may not provide you a solution if cash flows have a specific pattern.