How to Calculate XIRR for Irregular Cash Flows
SIPs, lump sums, partial exits, dividends—real investments are messy. Here's how to get accurate returns.
Why Irregular Cash Flows Matter
Real-world investing is rarely clean. You add money when you have it, withdraw when you need it, receive dividends on random dates. Simple percentage returns don't work because:
- ₹10,000 invested for 5 years ≠ ₹10,000 invested for 5 months
- The same profit means different returns depending on when you invested
- Multiple additions and withdrawals make standard formulas useless
XIRR (Extended Internal Rate of Return) solves this by calculating your true annualized return while respecting the exact timing of every cash flow.
The XIRR Formula Explained
XIRR finds the annual rate r that makes this equation true:
0 = C₁/(1+r)d₁/365 + C₂/(1+r)d₂/365 + ... + Cₙ/(1+r)dₙ/365
Where:
- C = Cash flow amount (negative for money out, positive for money in)
- d = Days from the first cash flow
- r = The annualized return rate (what we're solving for)
Don't worry about the math! Excel's XIRR function or tools like TrueXIRR handle this automatically. What matters is understanding how to set up your cash flows correctly.
Step-by-Step: Setting Up Your Cash Flows
Step 1: List Every Transaction
Create two columns: Date and Amount
| Date | Amount (₹) | Type |
|---|---|---|
| 01-Jan-2022 | -50,000 | Initial investment |
| 15-Mar-2022 | -25,000 | Added more |
| 01-Aug-2022 | +10,000 | Partial withdrawal |
| 15-Nov-2022 | +2,500 | Dividend received |
| 01-Jan-2024 | +85,000 | Current value |
Step 2: Follow the Sign Convention
Negative (−) = Money OUT
- • Initial investment
- • SIP installments
- • Additional lump sums
- • Any money you put in
Positive (+) = Money IN
- • Partial/full redemptions
- • Dividends received
- • Current portfolio value
- • Any money you take out
Step 3: Include the Final Value
Critical: Your last entry should be the current portfolio value with today's date (positive number). This represents "if I sold everything today."
Step 4: Apply the XIRR Formula
In Excel or Google Sheets:
Where B2:B6 is your amounts column and A2:A6 is your dates column.
For our example above, XIRR = 18.34% annually.
Real-World Scenarios
Scenario 1: Monthly SIP
You invest ₹10,000 monthly for 12 months. Current value is ₹1,35,000.
| Date | Amount |
|---|---|
| 01-Jan-2023 | -10,000 |
| 01-Feb-2023 | -10,000 |
| 01-Mar-2023 | -10,000 |
| 01-Apr-2023 | -10,000 |
| 01-May-2023 | -10,000 |
| 01-Jun-2023 | -10,000 |
| 01-Jul-2023 | -10,000 |
| 01-Aug-2023 | -10,000 |
| 01-Sep-2023 | -10,000 |
| 01-Oct-2023 | -10,000 |
| 01-Nov-2023 | -10,000 |
| 01-Dec-2023 | -10,000 |
| 01-Jan-2024 | +1,35,000 |
Total invested: ₹1,20,000 | Current value: ₹1,35,000 | Simple return: 12.5%
XIRR = 22.67%
XIRR is higher because later SIPs had less time to grow.
Scenario 2: Tax Loss Harvesting
You sold and rebought on the same day to book losses for tax purposes.
| Date | Amount | Action |
|---|---|---|
| 01-Apr-2022 | -1,00,000 | Buy |
| 15-Mar-2023 | +85,000 | Sell (book loss) |
| 15-Mar-2023 | -85,000 | Rebuy same day |
| 01-Apr-2024 | +1,20,000 | Current value |
XIRR = 9.54%
XIRR correctly tracks the real cash flows despite the mid-journey sale/rebuy.
Scenario 3: Dividend Stocks
Include dividends as positive cash flows to capture total return.
| Date | Amount | Type |
|---|---|---|
| 01-Jan-2022 | -2,00,000 | Buy ITC shares |
| 15-Jun-2022 | +6,000 | Dividend |
| 15-Dec-2022 | +6,500 | Dividend |
| 15-Jun-2023 | +7,000 | Dividend |
| 15-Dec-2023 | +7,500 | Dividend |
| 01-Jan-2024 | +2,40,000 | Current value |
XIRR = 16.83% (including dividends)
Without dividends, XIRR would show only ~9.5%. Dividends added 7%+ to your actual return!
Common Edge Cases
Q: What if I have multiple stocks?
Calculate XIRR separately for each stock. For portfolio-level XIRR, combine all cash flows and use total current value.
Q: What if I haven't sold anything yet?
Use current market value as the final positive cash flow. XIRR will calculate returns on unrealized gains.
Q: What about brokerage and STT?
If your tradebook records net amounts (after charges), XIRR automatically accounts for costs. If not, subtract charges from buy amounts and add to sell amounts.
Q: Monthly SIP but I missed a month?
No problem! Just don't include an entry for that month. XIRR handles irregular gaps perfectly.
Summary: The Golden Rules
- Every transaction gets a row — buys, sells, dividends, everything
- Use exact dates — not "Jan 2022" but "15-Jan-2022"
- Money out = negative, money in = positive
- Always include current value as the last entry with today's date
- Sort by date — earliest first
🎯 Skip the Manual Work
Setting up cash flows for hundreds of trades is tedious. TrueXIRR automatically parses your broker's tradebook and calculates XIRR for each stock and your portfolio—no manual entry needed.
Calculate Your True XIRR
Upload your tradebook and know your real annualized returns. Free & private.
Calculate XIRR Now →