EN
XIRR (Extended Internal Rate of Return) is a financial formula used to calculate the annualised yield of an investment portfolio that involves multiple cash flows (deposits, withdrawals, or dividends) made at irregular, non-periodic intervals.
Standard IRR assumes cash flows occur at strictly regular intervals (such as monthly or annually). XIRR eliminates this limitation by factoring in the exact calendar dates of every transaction.
This article explains how XIRR works in mutual funds.
Key Takeaways
- XIRR stands for Extended Internal Rate of Return (XIRR).
- It is employed when investments and withdrawals fall on separate dates.
- XIRR is quite beneficial in estimating SIP returns as well.
- It takes into account the timing and value of each financial flow.
- This gives a result which is expressed as an annualised % return.
- It can also be utilised for lump sum payments, further purchases, and redemptions in investments.
- XIRR measures the return on the investor's real cash flows, not just the advertised performance of the fund.
What is XIRR in a Mutual Fund?
XIRR is a way to determine the annualised return of a set of investments and withdrawals made on different dates.
In mutual funds, an investor can invest through:
- Periodic or monthly SIPs
- Lump sum investing
- Additional purchase
Since these transactions may not occur at regular intervals, XIRR provides a realistic technique for calculating the overall return.
In the calculation, each investment is treated as a cash outflow, and each withdrawal or the current value of the investment is a cash inflow. It then determines the rate at which these cash flows even out on an annualised basis.
How Does XIRR Work?
XIRR is rather clear fundamentally. It needs two essential inputs:
- The worth of every cash flow
- The date on which each cash flow takes place
Let’s assume an investor makes three investments:
- ₹10,000 on Jan 1
- ₹15,000 on March 15
- ₹20,000 on June 20
Later, on December 31, the investor checks the portfolio's worth; it is ₹50,000.
Each investment was held in the market for varying durations. The June investment had less time to expand than the January investment. XIRR breaks this difference into the annualised return calculation.
This makes it more suitable than a basic rate-of-return calculation for investments made at different times.
XIRR Function
XIRR is based on the mathematical notion of finding the rate at which the present value of all cash flows is zero.
It can be written as:
XNPV = Σ [Cash Flow / (1 + XIRR)^(Days/365)] = 0
In practice, investors usually do not need to perform this calculation themselves. Spreadsheet applications like Microsoft Excel and Google Sheets can calculate XIRR automatically.
In Excel, the syntax is:
=XIRR(values, dates, [guess])
Where:
- Values are investments and withdrawals.
- Dates are the respective transaction dates.
- Guess is an optional starting return for the calculation.
Understand XIRR in Mutual Funds with an Example
Take the following example:
|
Date |
Transaction |
Cash Flow |
|
January 1, 2025 |
Investment |
-₹10,000 |
|
April 1, 2025 |
Investment |
-₹10,000 |
|
July 1, 2025 |
Investment |
-₹10,000 |
|
January 1, 2026 |
Current Value |
+₹34,000 |
-
Net Cash Invested: ₹30,000 across three tranches over the year.
-
Final Valuation: ₹34,000 after 12 months, yielding a total absolute gain of ₹4,000.
-
XIRR: 17.90%
Step-by-Step Breakdown
-
Time Weighting: Each investment remains invested for a different period. In this example, the first ₹10,000 is invested for 12 months, the second for 9 months, and the final tranche for 6 months. A simple absolute return calculation of ₹4,000 ÷ ₹30,000 = 13.3% does not accurately reflect the annualised return.
-
Solving the Equation: The XIRR calculation considers the timing and amount of each cash flow. Using the XIRR formula, the discount rate that brings the net present value (NPV) of all cash flows to zero is 17.9%.
-
Spreadsheet Implementation: In Microsoft Excel or Google Sheets, enter the dates in one column (for example, A2:A5) and the corresponding cash flows in another column (for example, B2:B5). Then use XIRR (B2:B5, A2:A5)
Mutual Fund Investment: XIRR Computation Types
Based on the cash flow pattern, XIRR can be used in various investment situations.
1. XIRR of SIP: This is true for an investor who makes periodic contributions through a Systematic Investment Plan.
Each SIP instalment is treated as a separate cash outflow, and the current portfolio value represents the final cash inflow.
2. Lump Sum XIRR: CAGR is usually used for one investment and redemption. XIRR can also be used to compute returns for a lump-sum investment when there are other transactions.
For one ultimate value with a single investment, XIRR and CAGR might give similar results if you measure the time period correctly.
3. Multiple Investments XIRR: An investor can purchase mutual fund shares at irregular intervals when extra money is available.
For example: Investments can be made in January, May, August and November with varying amounts. XIRR may roll these transactions into a single annualised return.
4. XIRR with Withdrawals: XIRR can also accommodate partial redemptions.
Each time an investor withdraws money from a mutual fund while keeping the remaining units, it is considered a positive cash flow. The last positive cash flow also includes the value of the remaining portfolio.
CAGR vs XIRR
XIRR and CAGR are both annualised measures of return but are appropriate in different circumstances.
|
Feature / Basis |
XIRR (Extended Internal Rate of Return) |
CAGR (Compound Annual Growth Rate) |
|
Transaction Structure |
Multiple, irregular cash flows permitted |
Single initial investment and single final value |
|
Cash Flow Dates |
Can occur on any irregular date |
Assumes a fixed, single investment period |
|
SIP Calculation |
Highly suitable and accurate |
Less suitable for multiple periodic additions |
|
Partial Withdrawals |
Accommodates multiple withdrawals easily |
Difficult to calculate directly without manual adjustments |
|
Primary Metric |
Annualised personal investor return |
Annualised asset growth rate |
Usually, CAGR is easier to apply when there is a single initial investment and a single final value. XIRR is more useful in cases where several investments or withdrawals occur on separate dates.
Why Do You Need XIRR?
XIRR provides investors with a more personal perspective on investing success.
1. Measures actual investors' returns
A mutual fund might show a one-year or three-year return, but it doesn’t mean you made that return, because you might have bought it at different times.
XIRR estimates the return based on your transactions.
2. Good for comparing investments
An investor with multiple mutual fund schemes might compare their annualised returns using XIRR. This can provide more insight into the performance of various assets depending on the investor’s actual cash flows.
3. Handles odd transactions
Investment doesn’t always come on a timetable. XIRR can handle varying dates and transaction volumes.
4. Helps Monitor Long-Term SIP Performance
Return calculation might become complicated manually for long-running SIPs. XIRR simplifies this by adding together all the instalments and their periods to give one annualised rate.
Absolute Return vs XIRR
Absolute return just looks at how much the investment has gained or lost as a percentage.
For example, if ₹1 lakh becomes ₹1.2 lakh, the absolute return is 20%. But this number doesn’t tell you how long it took to get that return. A 20% return in one year and a 20% return in five years are quite different investment results.
XIRR annualises the returns and also accounts for the dates of distinct cash flows.
Limitations and Risks to Keep in Mind
-
Past performance indicator: A high historical XIRR does not guarantee that the mutual fund will replicate similar returns in the future.
-
Investor-specific variance: Two people invested in the exact same fund can have entirely different XIRRs based on their individual investment timing.
-
Short-term volatility: Over brief horizons, XIRR can appear artificially inflated or distorted by abrupt market movements.
-
Not a standalone metric: XIRR should always be evaluated alongside the fund's underlying asset allocation, expense ratio, investment objective, and risk profile.
Conclusion
XIRR is a useful way to calculate annualised returns when you make multiple investments or withdrawals on different dates. It is especially helpful for SIPs and irregular investments because it considers both the amount invested and the time each investment stays invested.
