XIRR (Irregular Cash Flows)
Calculate investment returns when cash flows occur at irregular intervals, not fixed periods.
=XIRR(cash_flows, dates) How It Works
XIRR calculates the internal rate of return for cash flows that don’t occur at regular intervals. Unlike IRR (which assumes equal periods), XIRR uses actual dates - making it the gold standard for tracking real portfolio performance.
Syntax
=XIRR(values, dates, [guess])- values: Cash flow amounts (negative for outflows, positive for inflows)
- dates: Corresponding dates for each cash flow
- guess: Optional starting estimate
Example
Investment Account Activity:
| Date | Cash Flow | Description |
|---|---|---|
| 1/15/2023 | -$10,000 | Initial investment |
| 4/3/2023 | -$5,000 | Additional deposit |
| 7/22/2023 | $500 | Dividend |
| 11/8/2023 | -$3,000 | Another deposit |
| 1/15/2024 | $20,500 | Current value |
Formula: =XIRR(B2:B6, A2:A6)
Result: 11.8% annualized return
Why XIRR Matters
Regular IRR would wrongly assume equal spacing between these transactions. XIRR uses actual dates, giving you the true annualized return on your specific timing of deposits and withdrawals.
Portfolio Performance Tracker
Track your actual investment returns:
| Date | Transaction | Amount |
|---|---|---|
| 1/1/2023 | Initial | -25000 |
| 2/15/2023 | Deposit | -2000 |
| 3/1/2023 | Deposit | -2000 |
| 5/10/2023 | Withdrawal | 5000 |
| 8/20/2023 | Deposit | -3000 |
| 12/31/2023 | Value | 30500 |
Your Return: =XIRR(C2:C7, A2:A7) = 12.3%
This tells you exactly how well your money performed given YOUR timing of contributions and withdrawals.
401(k) True Return
Calculate your actual 401(k) return including all contributions:
| Date | Flow | Type |
|---|---|---|
| 1/15/2023 | -500 | Contribution |
| 1/31/2023 | -500 | Contribution |
| 2/15/2023 | -500 | Contribution |
| … | … | … |
| 12/15/2023 | -500 | Contribution |
| 12/31/2023 | 45000 | Year-end value |
Formula: =XIRR(cash_flows, dates)
This accounts for the fact that early contributions had more time to grow than later ones.
Real Estate with Irregular Cash Flows
| Date | Amount | Description |
|---|---|---|
| 3/15/2020 | -60000 | Purchase |
| 4/1/2020 | 1200 | Rent |
| 5/1/2020 | 1200 | Rent |
| … | … | Monthly rent |
| 9/15/2020 | -2500 | Repair |
| 2/1/2023 | -4000 | Renovation |
| 8/20/2024 | 95000 | Sale |
XIRR handles the irregular repair costs and varying rent collection dates correctly.
Comparing Investment Options
Which investment actually performed better?
Investment A:
| Date | Flow |
|---|---|
| 1/1/23 | -10000 |
| 12/31/23 | 11500 |
| XIRR: 15.0% |
Investment B:
| Date | Flow |
|---|---|
| 1/1/23 | -10000 |
| 3/15/23 | -5000 |
| 12/31/23 | 17000 |
| XIRR: 13.2% |
Investment A had better return despite B having higher total gain, because A’s money was working longer.
Adding Current Value
To calculate return-to-date, include current portfolio value as a positive cash flow:
=XIRR(VSTACK(contributions, current_value), VSTACK(contribution_dates, TODAY()))Or manually add today’s date and positive value as the last row.
Dollar-Weighted vs. Time-Weighted Returns
XIRR (Dollar-Weighted):
- Accounts for timing and size of your deposits
- Reflects YOUR actual experience
- Affected by when you added/removed money
Time-Weighted Return:
- Measures pure investment performance
- Ignores cash flow timing
- Used by fund managers
For personal tracking, XIRR is usually more relevant.
Building an Investment Tracker
| A (Date) | B (Description) | C (Flow) |
|---|---|---|
| 1/15/23 | Initial deposit | -10000 |
| 2/1/23 | Monthly contribution | -500 |
| 3/1/23 | Monthly contribution | -500 |
| 4/1/23 | Monthly contribution | -500 |
| =TODAY() | Current value | =portfolio_value |
| XIRR | =XIRR(C:C, A:A) |
Update the current value cell regularly to track performance.
XIRR vs. Simple Return
| Metric | Formula | Result |
|---|---|---|
| Simple Return | =(End-Start)/Start | 15% |
| XIRR | =XIRR(…) | 11.8% |
Simple return ignores that you added money during the year. XIRR shows the true rate your money earned.
Handling Edge Cases
Short Holding Periods
XIRR annualizes returns. A 5% gain over 1 month becomes ~80% annualized. This is mathematically correct but can be misleading.
Context formula:
=IF(DAYS(MAX(dates), MIN(dates)) < 365,
"Annualized: " & TEXT(XIRR(...), "0%") & " (Short period)",
XIRR(...))All Positive or Negative Flows
XIRR needs at least one positive and one negative cash flow. If tracking only contributions, add current value as positive final flow.
Pro Tips
-
Always include current value - treat it as a positive “outflow” (money you could withdraw)
-
Track every transaction - missing flows skew the result
-
Be consistent with timing - use transaction dates, not statement dates
-
Compare to benchmarks - your XIRR vs. S&P 500 over same period shows if you’re beating the market
-
Reinvested dividends - include as separate positive then negative flows on the same date
Common Errors
- #NUM! error: No solution found - check that you have both positive and negative flows
- #VALUE! error: Dates and values arrays are different sizes
- Extremely high/low result: Short time period amplifies annualized rate
- Dates as text: Ensure dates are actual date values, not text strings