XIRR Function - Excel & Sheets
Calculate internal rate of return for irregular cash flows. Essential for investment analysis with non-periodic transactions.
Excel
Google SheetsExcelGoogle Sheets=XIRR(values, dates, [guess])Quick Answer
XIRR function XIRR function calculates the internal rate of return for cash flows at irregular intervals. Syntax: `=XIRR(values, dates, [guess])`. Use it for investment analysis with non-periodic transactions where cash flows don't occur at regular periods.
=XIRR(values, dates, [guess])Practical Examples
Basic Investment Analysis
Calculate return on investment with irregular payment dates
Real Estate Investment Returns
Track property investment with varying rental income and expenses
Startup Investment Portfolio
Calculate return on venture capital investments with multiple rounds
Error Handling with IFERROR
Prevent errors when XIRR cannot calculate a result
Common Errors and Solutions
XIRR cannot find a result that works
XIRR cannot find a result within 100 iterations, or dates not in chronological order
Try adjusting the guess parameter closer to expected result. Ensure dates are in chronological order and values include both positive and negative numbers.
Use a guess parameter close to expected result. Verify data has at least one positive and one negative value. Sort by date.
Example:
XIRR returns #VALUE! error
Dates and values arrays are different lengths, or dates are not valid date values
Ensure values and dates ranges have the same number of entries. Verify all dates are formatted as dates, not text.
Always use same-sized ranges for values and dates. Format date column as Date type. Use DATEVALUE() for text dates.
Example:
All cash flows same sign
All cash flows are the same sign (all positive or all negative)
XIRR requires at least one positive and one negative value. Verify investment (negative) and returns (positive) are correctly signed.
Investments should be negative values, returns should be positive. Check data signs before calculating.
Example:
Best Practices and Pro Tips
Understanding the Guess Parameter
The guess parameter helps XIRR converge to a solution faster. Use 0.1 (10%) for typical investments, 0.2-0.3 for high-growth scenarios, or 0.05 for conservative investments. A good guess reduces calculation time.
Investment Convention: Negative for Outflows
Always use negative values for money you invest (cash outflows) and positive values for returns you receive (cash inflows). This convention is critical for XIRR to calculate correctly.
Verify Date Formatting
Ensure dates are actual Excel date values, not text that looks like dates. Use DATEVALUE() if converting text dates. Dates must be in chronological order for accurate results.
XIRR vs IRR: Key Difference
IRR assumes regular periodic cash flows (monthly, annually). XIRR handles irregular timing. Always use XIRR when cash flows don't occur at regular intervals, even if they're close to periodic.
Combining with XNPV for Validation
Use XNPV with the XIRR result to verify accuracy. XNPV should return a value very close to zero when using XIRR as the discount rate, confirming the calculation is correct.
Need Help with XIRR Function - Excel & Sheets?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Calculate Modified Internal Rate of Return for investments. Learn MIRR syntax, examples, and how to evaluate investment projects effectively.
ExcelSheetsThe IRR function calculates internal rate of return for cash flows. Learn to evaluate investment profitability with practical examples.
ExcelSheetsThe NPV function calculates net present value of investments. Learn NPV with examples, common errors, and financial analysis tips.
ExcelSheetsThe RATE function calculates interest rate per period for loans. Master RATE with examples, error solutions, and tips. Get examples, tips, and solutions.
ExcelSheets