values - Required. Cash flow amounts. Negative = outflow (investment), Positive = inflow (return). Must have at least one negative and one positive value.
dates - Required. Dates corresponding to each cash flow. Must be in chronological order. Excel date serial numbers or date strings.
guess - Optional. Optional starting point for calculation (default 0.1 = 10%). Try different values (0.5, -0.1) if #NUM! error occurs.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Cash Flow | Event |
| 2 | 1/1/2023 | -$100,000 | Initial investment |
| 3 | 7/15/2023 | -$50,000 | Follow-on round |
| 4 | 3/20/2024 | $25,000 | Dividend payout |
| 5 | 12/31/2024 | $200,000 | Exit/Sale |
| 6 | XIRR: | 32.5% | =XIRR(B2:B5, A2:A5) 32.5% |
Calculate return on investment with irregular dates
→ 32.5% annualized return
Provide starting estimate when default fails
→ Helps convergence for complex cash flows
Analyze dollar-cost averaging strategy
→ Performance of staggered purchases
IRR for property with irregular capital calls
→ True time-weighted return
Venture capital and angel investors need to calculate returns on startup investments where funding rounds happen at irregular intervals. XIRR handles the complexity of multiple capital calls spread over months or years, followed by exit events at unpredictable dates. This formula provides accurate annualized returns that account for the exact timing of each cash flow, essential for Limited Partner (LP) reporting, fund performance benchmarking, and making data-driven decisions about follow-on investments versus portfolio rebalancing in early-stage venture portfolios.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Cash Flow | Event |
| 2 | 1/1/2023 | -$100,000 | Seed round investment |
| 3 | 7/15/2023 | -$50,000 | Series A participation |
| 4 | 3/20/2024 | $25,000 | Dividend distribution |
| 5 | 12/31/2024 | $200,000 | Exit via acquisition |
| 6 | XIRR: | 32.5% | =XIRR(B2:B5, A2:A5) Annualized return |
Real estate syndication and REIT managers face irregular capital contribution schedules where investors contribute funds as deals close, renovations require additional capital, and distributions happen quarterly or when properties sell. XIRR calculates precise investor returns that account for the time value of money across these scattered cash flows. This calculation is critical for investor relations, comparing deal performance across different properties, and making strategic decisions about when to hold versus sell assets to maximize Limited Partner returns in commercial real estate portfolios.
| A | B | C | |
|---|---|---|---|
| 1 | Transaction Date | Amount | Description |
| 2 | 3/15/2023 | -$500,000 | Initial capital call |
| 3 | 8/1/2023 | -$200,000 | Renovation funding |
| 4 | 12/1/2023 | $30,000 | Q4 distribution |
| 5 | 6/1/2024 | $35,000 | Q2 distribution |
| 6 | 12/15/2024 | $800,000 | Property sale proceeds |
| 7 | Annual IRR: | 18.7% | =XIRR(B2:B6, A2:A6) |
❌ The Problem:
✅ Solution:
=XIRR({-1000, -500, 2000}, {DATE(2023,1,1), DATE(2023,6,15), DATE(2024,12,31)})Ensure initial investments are negative (outflows) and final proceeds or dividends are positive (inflows). XIRR uses the sign of cash flows to determine direction - negative means money leaving your account, positive means money coming back. This sign convention is standard in finance and matches how IRR calculations work across all professional investment analysis tools.
❌ The Problem:
✅ Solution:
=XIRR(B2:B5, A2:A5)Always sort your data by date (earliest to latest) before using XIRR. Use Excel's Sort feature (Data > Sort) to arrange cash flow rows by the date column in ascending order. XIRR calculates time-weighted returns based on the exact number of days between cash flows, so chronological order is mathematically required for accurate IRR computation.
❌ The Problem:
✅ Solution:
=XIRR(B2:B5, A2:A5)Use XIRR whenever cash flows don't occur at regular intervals. The standard IRR function assumes equal time periods between each cash flow, which is rarely true for investments, project financing, or real estate deals. XIRR calculates day-level precision, giving you accurate annualized returns that account for the exact timing of every transaction - critical for fiduciary reporting and investor communications. For financial modeling best practices, see <Link href="/formulas/pv">PV</Link> and <Link href="/formulas/fv">FV</Link> functions.
Master these XIRR Function variants:
From basics to advanced - AI generates perfect formulas instantly.