What it does
IRR assumes evenly spaced flows; XIRR takes the date of each amount and accounts for the days between them. The result is always an annual rate, however the flows are spaced.
The values and dates must have the same number of cells. The first date is the starting point and the others must come after it. The sign rule is the same: money you put in is negative, money you get back is positive. To find the return so far on an investment you still hold, add its current value as a positive amount in the last row.
For the present value of the same flows at a given rate, use XNPV. The dates must be real dates; dates stored as text cause an error, and DATE helps build them from parts.
Syntax
=XIRR(values, dates, [guess])=AİÇVERİMORANI(değerler; tarihler; [tahmin])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
values | Required | The cash flows: money out negative, money in positive. At least one negative and one positive value are needed. |
dates | Required | The date of each cash flow, in the same order and number as the values. The first date must come before the others. |
guess | Optional | The rate to start the search from; 10% if omitted. |
Examples
1The annual return of a project with irregular dates
The machine purchase and installation are negative and the sales income positive; each amount's date is in column A. XIRR accounts for the days in between.
=XIRR(C2:C6,A2:A6)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Description | Cash Flow | Annual Return | |
| 2 | 15-Jan-2026 | Machine purchase | -50,000.00 | 23.19% | |
| 3 | 10-Apr-2026 | Installation | -15,000.00 | ||
| 4 | 1-Sep-2026 | First sales income | 20,000.00 | ||
| 5 | 20-Mar-2027 | Sales income | 30,000.00 | ||
| 6 | 31-Dec-2027 | Sales income | 35,000.00 |
Payments and income are spread unevenly over two years; the project's annual return is 23.19%. Move an income date earlier and the rate goes up.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Portfolio return with top-ups and a current value
Two purchases are negative and a partial sale positive; the last row is the portfolio's value on 5 November 2026. F2 applies IRR, which ignores dates, to the same flows.
=XIRR(C2:C5,A2:A5)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Transaction | Amount | Annual Return | Rate Without Dates | |
| 2 | 3-Feb-2025 | Purchase | -10,000.00 | 15.67% | 8.93% | |
| 3 | 16-Jun-2025 | Purchase | -5,000.00 | |||
| 4 | 29-Dec-2025 | Partial sale | 4,000.00 | |||
| 5 | 5-Nov-2026 | Current value | 14,500.00 |
- E2
=XIRR(C2:C5,A2:A5) - F2
=IRR(C2:C5)
With the dates taken into account, the annual return is 15.67%. IRR treats the flows as evenly spaced and gives 8.93% per period; because the intervals are irregular, that rate cannot be read as an annual return.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: All values have the same sign, the values and dates differ in number, or a date is earlier than the first date.
Fix: Enter money put in as negative and money received as positive; make sure both ranges are the same size and the first date is the earliest.
- #VALUE!
Cause: One of the dates is text, not a real date.
Fix: Enter the dates as real dates or build them with DATE.
Good to know
- For a portfolio return, enter each deposit as negative and each withdrawal as positive, then add today's portfolio value as a positive amount with today's date.
- To check the result, work out XNPV at that rate; it should be very close to zero.
In Nexell
Get Nexell to do it from a sentence
Add a summary cell to my investment transactions table that works out the annual return from the dates
Convert to tableBring your transactions table into the tool and type this. Nexell works out the annual return with real Excel formulas and shows it in the preview; in the downloaded file, the rate changes when an amount or a date does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between XIRR and IRR?
IRR assumes the flows arrive at equal intervals and returns a rate per period. XIRR uses the actual date of each flow and always returns an annual rate. When the dates are irregular, XIRR is more accurate.