Mutual fund investing often involves more than one transaction. You may invest through an SIP, add a lumpsum amount later, pause contributions or make a partial redemption. Since each transaction happens on a different date, comparing only the amount invested with the current value may not show the complete return picture.
This is where XIRR in mutual funds becomes useful. XIRR is commonly expanded as Extended Internal Rate of Return. It calculates the annualised return from an investment after considering the amount and exact date of every investment, redemption and current portfolio value.
For investors looking for the XIRR meaning in mutual fund investing, it refers to the return associated with their own investment journey. A mutual fund XIRR can differ from one investor to another, even where they hold the same scheme.
Table of Contents
What is XIRR in mutual funds?
XIRR is a method used to calculate the annualised return from investments involving multiple cash flows on different dates. It considers the amount of every transaction and the period for which that amount remained invested.
This makes XIRR relevant for SIPs, additional investments, partial redemptions and systematic withdrawals. Each SIP instalment is invested on a different date and may be purchased at a different NAV. The first instalment has more time in the market than the most recent one. XIRR accounts for these differences and gives one annualised rate based on the investor’s actual transactions.
A cash flow is money moving into or out of an investment. In a mutual fund, this can include an SIP instalment, a lumpsum purchase, a redemption, a withdrawal or the current value of units still held.
Key Takeaways
- XIRR calculates the annualised return from investments and withdrawals made on different dates.
- It is particularly useful for SIPs because every instalment remains invested for a different period.
- Investments are entered as negative cash flows, while redemptions or current portfolio value are entered as positive cash flows.
- XIRR reflects an investor’s personal return experience and is different from a scheme’s published CAGR.
- XIRR should be assessed alongside risk, costs, taxation, investment horizon and the scheme’s objective.
XIRR formula
XIRR is the rate that makes the Net Present Value of all dated cash flows equal to zero. It can be represented as:
Σ [Pᵢ / (1 + XIRR)^((dᵢ – d₁) / 365)] = 0
Where:
- Pᵢ is each investment, redemption or current portfolio value
- dᵢ is the date of each cash flow
- d₁ is the date of the first cash flow
The formula accounts for the exact number of days between transactions. Since the calculation involves repeated estimation, investors usually use Excel’s XIRR function or an online calculator instead of calculating it manually.
Why XIRR is useful for mutual fund investors
XIRR helps investors understand the return associated with their personal cash-flow pattern. It is more relevant than a simple return percentage when money has been invested or withdrawn on different dates.
It can help investors track returns from SIPs, additional purchases and redemptions. It also shows why two investors in the same mutual fund may have different results. Their investment dates, contribution amounts and withdrawal patterns may not be the same.
XIRR is a return measure, not a measure of risk. It should not be the sole basis for selecting or exiting a mutual fund scheme.
How to calculate XIRR in Excel
Excel has a built-in XIRR function for cash flows that are not necessarily periodic.
1. List the transaction dates
Enter the date of every SIP instalment, lumpsum investment, redemption or withdrawal in one column. Add the valuation date or redemption date as the final date.
2. Enter each cash flow
Enter investments as negative values because money is leaving the investor’s account. Enter redemptions and current portfolio value as positive values because they represent money received or the value of units held.
| Transaction | How to enter it in Excel |
| SIP instalment or lumpsum investment | Negative value |
| Partial or full redemption | Positive value |
| Current portfolio value on the valuation date | Positive value |
Step 3: Apply the XIRR formula
Use the following formula:
=XIRR(values, dates, [guess])
- Values refers to the range containing the cash-flow amounts.
- Dates refers to the range containing the corresponding transaction dates.
- Guess is optional. Excel uses 10% by default if no value is entered.
For example, if dates are in cells A2 to A7 and cash flows are in cells B2 to B7, use:
=XIRR(B2:B7,A2:A7)
Format the result as a percentage to view the annualised XIRR.
Use IRR when cash flows occur at regular intervals, such as every month or every year. Use XIRR when transactions occur on different dates. For mutual fund investors with SIPs, lumpsum investments, redemptions or withdrawals, XIRR is generally more relevant because it considers the actual transaction dates
XIRR calculation example in Excel
Prakhar, a software engineer in Pune, invests ₹8,000 each month in a mutual fund from February to June. On 1 July, he redeems his investment for ₹44,500.
| Date | Cash flow |
| 01-Feb-24 | ₹-8,000 |
| 01-Mar-24 | ₹-8,000 |
| 01-Apr-24 | ₹-8,000 |
| 01-May-24 | ₹-8,000 |
| 01-Jun-24 | ₹-8,000 |
| 01-Jul-24 | ₹44,500 |
Roshan enters the dates in one Excel column and the cash flows in another. He then uses the formula =XIRR(B2:B7,A2:A7).
The final amount of ₹44,500 is entered as a positive value because it represents money received on redemption. If Roshan had remained invested, he would enter the current portfolio value as a positive amount on the date of calculation instead.
The figures shown are for illustrative purpose only.
Points to note when calculating XIRR in Excel
Before using the XIRR function, check the following:
- Enter every investment as a negative value.
- Enter every redemption or current portfolio value as a positive value.
- Ensure that every cash flow has a corresponding valid date.
- Include all relevant transactions for the period being reviewed.
- Use the current portfolio value only once, as the final positive cash flow, if the investment is still active.
- Ensure that the values and dates ranges contain the same number of entries.
- Include at least one negative and one positive cash flow.
If Excel displays a #NUM! error, review the cash flows and dates. The error may occur where the function cannot identify a result from the information entered.
How to use Bajaj AMC’s online XIRR calculator
Bajaj AMC’s online XIRR calculator can help investors calculate a return across multiple transactions.
- Enter the date and amount of the first investment.
- Add each subsequent investment date and amount.
- Use the option to add more transactions where required.
- Enter the returns date, which may be the redemption date or the date on which the portfolio is valued.
- Enter the redemption amount or current value of the holdings.
- Select the option to calculate XIRR.
The result reflects the annualised return associated with the dates and amounts entered.
The calculator is an aid, not a prediction tool. It may provide only an indicative picture.
XIRR vs CAGR: Key differences
XIRR and CAGR are both annualised return measures, but they suit different investment patterns.
| Basis of comparison | XIRR | CAGR |
| Cash flows | Considers multiple investments and withdrawals | Uses a beginning value and an ending value |
| Timing | Considers the exact date of each transaction | Does not account for interim cash flows |
| Suitable for | SIPs, additional investments and partial redemptions | A single lumpsum investment held for a fixed period |
| Use in mutual funds | Measures an investor’s personal return experience | Commonly used to present scheme returns for defined periods |
CAGR is useful where an investor makes one investment and remains invested without adding or withdrawing money. XIRR is more appropriate where cash flows occur on different dates.
How to interpret XIRR
A higher XIRR does not automatically indicate that one mutual fund is more suitable than another. The result needs to be assessed alongside the investment period, fund category, risk level, costs and the investor’s financial objective.
XIRR can fluctuate sharply over short periods because it annualises the return. For a brief holding period, absolute return can sometimes be easier to interpret alongside XIRR.
XIRR also does not separately account for inflation, taxation, exit loads or market volatility. These factors may affect the investor’s actual outcome and should be considered separately.
FAQs
What is the XIRR full form in mutual fund investing?
The XIRR full form in mutual fund investing is Extended Internal Rate of Return. It is an annualised return measure that considers the dates and amounts of multiple investments, withdrawals and the current or redemption value.
What does a 10% XIRR mean?
A 10% XIRR means that the investments, withdrawals and current value or redemption value entered in the calculation equate to an annualised return of 10%. It does not indicate that the investment will earn 10% every year going forward.
Can XIRR be negative?
Yes. XIRR can be negative when the investment’s current value or redemption value is insufficient to recover the cash outflows on a time-adjusted basis.
Is XIRR suitable for short-term investments?
XIRR can be calculated for short-term investments, but the annualised result may move sharply over brief periods. For a short holding period, absolute return can sometimes be easier to understand alongside XIRR.
Why can XIRR differ for two investors in the same mutual fund?
XIRR can differ because each investor may invest different amounts on different dates and may redeem at different times. XIRR reflects the individual investor’s cash-flow pattern rather than only the scheme’s performance.
Why does XIRR change over time?
XIRR can change as new investments are added, withdrawals are made or the current portfolio value moves with the market. It may also change because the time period between the investor’s transactions continues to increase.
