Syntax
XIRR(values, dates, [guess])
| Parameter | Description |
|---|---|
| values | Parameter of the XIRR function. |
| dates | Parameter of the XIRR function. |
| [guess] | (Optional.) Parameter of the XIRR function. |
Examples
Stock investment return
Formula
=XIRR({-10000, 500, 500, 12000}, {"2024-01-15","2024-07-15","2025-01-15","2025-08-01"})
19.2%. Bought $10K in stock, received two $500 dividends, sold for $12K — annualized return is 19.2%.
Private equity investment
Formula
=XIRR({-50000, -25000, 200000}, {"2022-03-01","2023-01-15","2026-06-30"})
38.7%. A $50K initial investment, $25K follow-on, and $200K exit over ~4 years yields a very strong annualized return.
Rental property with irregular income
Formula
=XIRR({-180000, 7200, 14400, 7200, 210000}, {"2023-06-01","2023-12-31","2024-12-31","2025-06-30","2025-12-31"})
15.3%. Purchase, partial-year rents, full-year rent, partial-year rent, and sale — XIRR handles the uneven timing correctly.
Common Errors
#NUM!
XIRR cannot converge on a solution. Provide a guess parameter (e.g., 0.1) or check that cash flows have both positive and negative values.
#VALUE!
Dates are not valid or values are non-numeric, or the arrays have different lengths.
Tips
Always use XIRR over IRR for real investments
Real investments rarely have perfectly spaced cash flows. XIRR gives you the true annualized return accounting for exact dates.
First cash flow should be negative
The initial investment (outflow) should be negative. Returns, dividends, and sale proceeds should be positive.
Provide a guess for unusual cash flows
If you expect a very high or very low return, pass a guess as the third argument (e.g., 0.5 for 50%). This helps XIRR converge when the default 10% guess fails.
Try XIRR in Viztab
Import your data and use XIRR with 370+ other formulas. No signup required.
Open Viztab