Quick Navigation
XIRR Function
Summary
The Excel XIRR function calculates the internal rate of return (IRR) for a series of cash flows occurring at irregular intervals. Unlike the standard IRR function which assumes periodic payments, XIRR handles real-world scenarios where cash flows happen on specific, non-periodic dates.
Syntax
XIRR(values, dates, [guess])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| values | Number array |
Yes | Cash flow amounts corresponding to payment dates. Must include at least one positive (income) and one negative (expense) value. Initial investment should be negative. |
| dates | Date array |
Yes | Corresponding payment dates as Excel date serial numbers. Use DATE() function or cell references with dates. |
| guess | Number |
No | Optional estimate of IRR (defaults to 10% or 0.1). Helps convergence for complex calculations. |
Using the XIRR Function
XIRR is essential for financial analysis where cash flows don't follow monthly or annual patterns. Use it to evaluate investments, project returns, or assess loan profitability by providing exact cash flow amounts and their specific dates.
Common XIRR Examples
Investment Return Analysis
=XIRR(A2:A6,B2:B6)Calculates IRR for an investment of $10,000 on Jan 1, 2008 with subsequent cash inflows on irregular dates, returning approximately 37.34%.
With Guess Parameter
=XIRR(A2:A6,B2:B6,0.2)Uses 20% as starting guess for faster convergence in complex scenarios.
Real Estate Cash Flow
=XIRR(CashFlows,DealDates)Analyzes property investment returns with irregular closing costs, rental income, and sale proceeds.
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: No positive/negative cash flows, dates precede first date, or iteration failed after 100 tries
Solution: Verify cash flows include both signs, check date sequence, try different guess value
#VALUE!
Cause: Invalid dates (text instead of dates) or mismatched array sizes
Solution: Use DATE() function for dates, ensure values and dates arrays same length
#REF!
Cause: Deleted cell references in ranges
Solution: Check all referenced cells exist
Notes
- Dates stored as serial numbers (Jan 1, 1900 = 1)
- Uses 365-day year basis for daily discounting
- Default guess = 0.1 (10%) if omitted
- Iterates up to 100 times for 0.000001% accuracy
- Mathematically solves where NPV = 0
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+