XIRR
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. Unlike IRR, XIRR handles irregular time intervals between payments.
XIRR(values, dates, [guess])Arguments
valuesA series of cash flows corresponding to the dates. The first payment is optional and corresponds to a cost or payment at the beginning of the investment. Must contain at least one positive and one negative value.datesA schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule.guessAn optional estimate for what you expect XIRR to return. Defaults to 0.1 (10%).(optional)
=XIRR(B2:B6, C2:C6)Returns the IRR for cash flows in B2:B6 occurring on the irregular dates in C2:C6
=XIRR({-10000,3000,4200,6800},{"1/1/2024","6/1/2024","12/1/2024","3/1/2025"})Returns the rate of return for this irregular investment schedule
=XIRR(B2:B6, C2:C6, 0.05)Uses 5% as the initial guess for the calculation
- •Values must include at least one positive and one negative number
- •Dates must be valid Excel dates, entered in chronological order
- •Use XIRR instead of IRR whenever your cash flows don't occur at regular intervals
- •If XIRR returns #NUM!, try a different guess value or check that values alternate signs
- •Using XIRR when cash flows actually occur at regular intervals - plain IRR is simpler and sufficient in that case, and XIRR's date-based approach adds complexity without benefit
- •Forgetting that values must include at least one negative and one positive number, just like IRR - an all-positive or all-negative series has no solvable rate and returns a #NUM! error
- •Entering dates out of chronological order or as text instead of genuine Excel dates, which can cause XIRR to return incorrect results or errors
IRRCalculates a rate of return assuming equally spaced periods, a simpler alternative when your cash flows aren't irregular.XNPVCalculates net present value at a fixed rate using the same exact-date approach as XIRR, and pairs naturally with it (XNPV is 0 at the rate XIRR returns).NPVThe regular-interval counterpart to XNPV, just as IRR is the regular-interval counterpart to XIRR.When should I use XIRR instead of IRR?
Use XIRR whenever your cash flows happen on irregular, specific dates rather than at neat, equally spaced intervals - it accounts for the exact number of days between payments instead of assuming uniform periods.
Why does XIRR return a #NUM! error?
Usually because the values don't contain both a negative and a positive number, or because XIRR's iterative calculation failed to converge - try supplying a different guess value.
Do the dates in XIRR need to be in order?
They should be entered chronologically for reliable results, and must be genuine Excel date values rather than text that merely looks like a date.
Need to translate a formula using XIRR?
Use our translator to convert your complete formula