FINBIZTOOLS
What Is XIRR?
Understanding the method
Use negative signs for money you invest and positive signs for money you receive. Include the current portfolio value as a hypothetical receipt on its valuation date if the investment has not been sold. XIRR discounts dated amounts until their net present value is zero. Some cash-flow patterns have multiple answers or none. The FinBizTools implementation rejects multiple-sign-change series after combining same-day flows rather than selecting an arbitrary rate.
Worked example
An investment of −₹10,000 on 1 January 2026 and a receipt of ₹11,000 on 1 January 2027 are 365 days apart and produce 10% XIRR. An extra investment halfway through the year changes the calculation.
Formula and definitions
Find r such that Σ CFᵢ / (1 + r)^((dateᵢ − first date)/365) = 0. The displayed percentage is 100 × r.
How to use FinBizTools
- Collect statements with actual transaction dates.
- Enter contributions as negative and withdrawals as positive.
- Add the ending valuation as a positive flow on its actual valuation date.
- Check signs, duplicate transactions and dates before calculating. If the tool reports ambiguity, do not invent a substitute result.
Open the tools
Download related templates
Common mistakes and limitations
The tool uses actual UTC days divided by 365, including leap years. It supports up to 200 entries and bounded numerical solving. XIRR measures the entered history; it does not forecast returns.
Why a simple return misleads with staggered investments
Consider an investor who invested on three different dates and checked the value at the end of 2026:
| Date | Cash flow | Meaning |
|---|---|---|
| 1 January 2025 | −₹50,000 | First investment |
| 1 July 2025 | −₹30,000 | Top-up |
| 1 January 2026 | −₹20,000 | Second top-up |
| 31 December 2026 | +₹1,15,000 | Current value (hypothetical sale) |
Total money invested is ₹1,00,000 and the value is ₹1,15,000, an absolute gain of 15%. Treating that as a two-year CAGR gives about 7.24% per year, but that figure is too low because only half the money was invested for the full two years; ₹20,000 was invested for just one year. Solving for the rate that makes the present value of all four flows equal to zero gives an XIRR of about 8.81% per year. XIRR credits each rupee only for the time it was actually invested.
Calculating XIRR in Excel or Google Sheets
Spreadsheets have a built-in XIRR function. Put the amounts in one column and the dates in the next, using negative numbers for money paid in and positive numbers for money received or the current value. Then enter =XIRR(B2:B5, C2:C5). The result is a decimal, so format the cell as a percentage. The XIRR Calculator follows the same sign convention and also lists each flow, which makes it easier to spot a mistyped date. The SIP Investment Planner template has a ready-made cash-flow table you can adapt.
Using XIRR for a monthly SIP
A SIP is the most common reason to use XIRR. Each monthly instalment is a negative flow on its debit date, and today’s portfolio value is a single positive flow on today’s date. If you have redeemed part of the holding, enter the redemption as a positive flow on the day the money was received. Dividends paid out to your bank account are positive flows; dividends that were reinvested are not separate flows because they never left the investment. When a fund is switched to another fund, either treat both funds as one portfolio or record the switch as a withdrawal from one and an investment in the other — but do the same thing consistently.
Reading the result sensibly
- Compare like with like. XIRR is an annualized rate, so it can be compared with a fixed deposit rate or another portfolio’s XIRR over a similar period.
- Be careful with short periods. Annualizing a few weeks can produce extreme numbers. A 2% gain over 30 days annualizes to roughly 27%, which says little about what will happen over a full year.
- Remember what is excluded. Taxes paid outside the investment, advisory fees paid separately and inflation are not captured unless you add them as flows.
- XIRR is history, not a forecast. A high XIRR over a strong market period does not predict future returns.
Common errors that distort XIRR
- Forgetting the final valuation, which leaves only negative flows and no solution.
- Entering every flow with the same sign.
- Using the order date instead of the date money actually left or reached your account.
- Double-counting a transaction that appears on both a bank statement and a fund statement.
- Mixing several unrelated investments, which produces a blended rate that describes none of them.
XIRR, IRR and CAGR compared
IRR assumes cash flows occur at equal intervals, such as exactly once a year. XIRR uses the actual dates, so it handles irregular SIP debits, missed months and one-off top-ups. CAGR is the special case of a single investment and a single ending value; if there are no intermediate flows, CAGR and XIRR over the same dates give essentially the same answer.
Frequently asked questions
Why is the final valuation necessary?
It closes the cash-flow series by treating the remaining investment as a hypothetical receipt on the valuation date.
Sources to review
Primary references checked during preparation on 16 September 2026. Recheck before publication, especially for rules and regulated products.
Disclaimer
This guide is educational. Examples are illustrative, not financial, tax or legal advice. Verify important decisions and current requirements. Read the FinBizTools Disclaimer.