Calculate the Extended Internal Rate of Return (XIRR) for Mutual Fund SIPs, multiple lump sums, and irregular cash flows.
+14.82%
Annualized compound rate over 3.0 years (1,096 days)
| # | Date | Type | Cash Flow (₹) |
|---|
When evaluating an investment where you make a single lump-sum deposit on day one and redeem it years later, measuring your return is straightforward using CAGR (Compound Annual Growth Rate).
However, real-world investing is rarely a single lump-sum event. In mutual fund SIPs, stock portfolios, and retirement accounts, you make periodic monthly contributions, irregular bonus top-ups, dividend reinvestments, and occasional partial redemptions.
Because each individual cash flow is invested at a different date and held for a different period of time, traditional formulas fail:
XIRR calculates the annualized internal rate of return (r) by finding the exact discount rate that sets the Net Present Value (NPV) of all past and present cash flows to zero:
Where:
Because this equation contains non-linear fractional exponents across dozens of dates, it cannot be solved with standard elementary algebra. Financial calculators solve it numerically using the Newton-Raphson iteration:
Consider an investor who makes 3 separate investments into an equity fund, receives 1 dividend payout, and evaluates their total portfolio balance on January 1, 2026:
| Transaction Date | Description | Cash Flow Type | Amount (₹) | Holding Days |
|---|---|---|---|---|
| 01-Jan-2024 | Initial Lump-sum Deposit | Outflow (-) | -₹1,00,000 | 0 Days (Base) |
| 01-Jul-2024 | Mid-year Top-up | Outflow (-) | -₹50,000 | 182 Days |
| 01-Jan-2025 | Annual Top-up | Outflow (-) | -₹50,000 | 366 Days |
| 01-Jul-2025 | Dividend Received | Inflow (+) | +₹8,000 | 547 Days |
| 01-Jan-2026 | Current Portfolio Valuation | Inflow (+) | +₹2,60,000 | 731 Days |
Analysis of Results:
Choosing the wrong return metric can distort your financial assessment. Here is when to use each formula:
| Return Metric | Multiple Cash Flows? | Considers Dates & Time? | Annualized? | Best Used For |
|---|---|---|---|---|
| XIRR (Extended IRR) | Yes (Irregular) | Yes (Exact Calendar Days) | Yes (% p.a.) | Mutual Fund SIPs, STPs, SWPs, Stock Portfolios, Real-world Multi-cashflow Assets. |
| CAGR (Compound Annual Growth) | No (Single Lump-sum) | Yes (Years) | Yes (% p.a.) | Fixed Deposits, Gold/SGB, Single Buy-and-Hold Stocks, Index Benchmarks. |
| IRR (Internal Rate of Return) | Yes (Strictly Periodic) | Fixed intervals only (Yearly/Monthly) | Yes (% p.a.) | Private Equity, Real Estate Projects with uniform annual cash flows. |
| Absolute Return | No | No (Time Ignored) | No (Total %) | Short-term trades (< 6 months) to see raw profit percentage. |
A common misconception among beginner investors occurs when reviewing XIRR for a fresh SIP that has only been active for 1 to 3 months.
Best Practice: For investment tenures under 12 months, rely primarily on Absolute Return (%) and Net Gain in Rupees. Once your SIP crosses 12 months, XIRR becomes the single most accurate reflection of your portfolio's compounding rate.
An XIRR number only has meaning when compared against relevant opportunity costs and asset class benchmarks:
| Long-Term Portfolio XIRR | Performance Rating | Context & Comparison |
|---|---|---|
| > 15.0% p.a. | Outstanding | Significantly outperforms the broader market (Nifty 50 TRI ~12.5%). Top-quartile active equity funds. |
| 12.0% to 15.0% p.a. | Very Healthy | In line with historical Indian equity market long-term averages. Wealth doubles every 5 to 6 years. |
| 9.0% to 12.0% p.a. | Moderate | Typical for conservative hybrid/balanced funds or debt-heavy portfolios. Beats inflation by 4%–6%. |
| 6.0% to 9.0% p.a. | Conservative | Comparable to Fixed Deposits and Debt Funds. Capital protection focus with modest real growth. |
| < 6.0% p.a. | Underperforming | Fails to beat Indian retail inflation (CPI ~5%–6%). Portfolio requires asset rebalancing. |
Both Microsoft Excel and Google Sheets have a built-in financial function for computing XIRR:
Step-by-step setup:
A2:A13).B2:B13). Enter all investments as negative numbers (e.g. -10000) and your current portfolio valuation or final redemption as a positive number (e.g. 140000).=XIRR(B2:B13, A2:A13).#NUM! error, verify that: (1) You have at least one negative value and at least one positive value, (2) The earliest date is in the first row, and (3) All dates are formatted as valid calendar dates rather than text strings.
XIRR (Extended Internal Rate of Return) is an annualized return metric designed for investments involving multiple cash inflows and outflows occurring on irregular dates. While CAGR only measures point-to-point growth between a single start date and end date, and Absolute Return measures simple un-annualized profit percentage, XIRR calculates the exact internal rate of return by accounting for the precise date and rupee magnitude of every deposit, withdrawal, and current portfolio valuation.
XIRR solves for the annual discount rate (r) that sets the Net Present Value (NPV) of all cash flows to zero: Sum of [ C_i / (1 + r)^((d_i - d_0) / 365) ] = 0. In this equation, C_i represents each cash flow amount (investments are entered as negative values and redemptions or current valuations are positive), d_i is the transaction date, and d_0 is the initial investment date. Because this polynomial equation cannot be solved algebraically, it is calculated using the Newton-Raphson numerical iteration method.
In a mutual fund SIP, each monthly installment is invested at a different date and different market NAV price. The first installment remains invested for the full tenure, while the most recent installment has only been invested for a single month. Applying simple CAGR would erroneously treat all installments as if they were invested on day one. XIRR accurately annualizes the return of each individual installment based on its exact holding duration, providing the true composite performance of your SIP.
XIRR always annualizes returns over a 365-day basis. If you invest ₹10,000 and make a ₹500 gain in just 10 days (a 5% absolute return), the mathematical formula projects that 5% gain over all thirty-six 10-day periods in a year, producing an astronomical annualized XIRR of over +400%. For investment durations under 12 months, Absolute Return and Net Gain in rupees are more practical metrics, while XIRR becomes highly reliable for durations exceeding 1 year.
In Microsoft Excel or Google Sheets, use the native formula: =XIRR(values_range, dates_range, [guess]). The values range must contain at least one negative cash flow (investment outlay) and at least one positive cash flow (redemption or current valuation), while the dates range must contain valid corresponding calendar dates.
A good XIRR depends on the underlying asset class. For Indian broad-market equity mutual funds (such as Flexi-cap, Large-cap, or Nifty 50 Index funds), a long-term 5 to 10-year XIRR between 12% and 15% p.a. is considered excellent as it comfortably outpaces inflation (5%–6%) and fixed deposits (6.5%–7.5%). For hybrid/balanced funds, a 10%–12% XIRR is typical, while debt mutual funds target 6.5%–8% XIRR.
Yes, XIRR can be negative. A negative XIRR indicates that your overall portfolio's current market value plus any realized redemptions is less than the total sum of money you invested, resulting in an annualized net loss. For example, an XIRR of -8.5% means your invested capital has contracted at an annualized pace of 8.5% across your holding period.
Mutual fund NAVs (Net Asset Values) are published daily after deducting the fund management expense ratio, so XIRR automatically factors in all fund management fees. However, XIRR does not automatically deduct investor-level transaction exit loads or capital gains taxes (LTCG / STCG) unless you explicitly input your net post-tax, post-exit-load redemption amounts into the cash flow table.