XIRR
Find the annual return of cash flows that arrive on irregular dates — the rate at which their XNPV is zero.
Syntax
XIRR(values, dates, [guess])| Argument | Required | Description |
|---|---|---|
values | Required | The cash flows, with at least one negative and one positive. |
dates | Required | The date of each cash flow, one per value. |
guess | Optional | A starting estimate. Defaults to 10%; rarely needed. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=XIRR(A2:A6,B2:B6) | 0.37 | About 37.3% a year — Microsoft's example, shown to two decimal places. |
=XIRR(A2:A6,B2:B6)*100 | 37.34 | |
=XIRR(A3:A6,B3:B6) | #NUM! | Without a negative flow there is no rate that breaks even. |
IRR for real dates
IRR assumes one period between cash flows. XIRR takes a
date for each, which is how investments, private deals and anything paid on
invoice actually behave. The answer is an annual rate whatever the spacing.
The usual check is that the net present value at that rate is zero:
=XNPV(XIRR(A2:A6,B2:B6),A2:A6,B2:B6)When it returns #NUM!
The flows need at least one negative (money in) and one positive (money out),
and the pairs must line up. If the flows swing between positive and negative
several times, more than one rate can balance them; a guess near the one you
expect steers it there.
Excel compatibility
Matches Excel, including the 365-day year and #NUM! when the flows do not change sign.