Skip to content

Excel function · Financial

Excel XIRR function

XIRR works out the annual rate of return of cash flows that each happen on their own date. Use it to find the real yearly return of an investment that you added to and withdrew from on different days.

In Turkish Excel: AİÇVERİMORANI

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

English Excel=XIRR(values, dates, [guess])
Turkish Excel=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

ArgumentRequiredDescription
valuesRequiredThe cash flows: money out negative, money in positive. At least one negative and one positive value are needed.
datesRequiredThe date of each cash flow, in the same order and number as the values. The first date must come before the others.
guessOptionalThe 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.

E2fx=XIRR(C2:C6,A2:A6)
The annual return of a project with irregular dates: example table
ABCDE
1DateDescriptionCash FlowAnnual Return
215-Jan-2026Machine purchase-50,000.0023.19%
310-Apr-2026Installation-15,000.00
41-Sep-2026First sales income20,000.00
520-Mar-2027Sales income30,000.00
631-Dec-2027Sales income35,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.

E2fx=XIRR(C2:C5,A2:A5)
Portfolio return with top-ups and a current value: example table
ABCDEF
1DateTransactionAmountAnnual ReturnRate Without Dates
23-Feb-2025Purchase-10,000.0015.67%8.93%
316-Jun-2025Purchase-5,000.00
429-Dec-2025Partial sale4,000.00
55-Nov-2026Current value14,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

Type this in Nexell

Add a summary cell to my investment transactions table that works out the annual return from the dates

Convert to table

Bring 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.

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free