Excel XIRR Function

XIRR finds the annualized rate that makes the net present value of dated cash flows equal to zero.

Use it to estimate an investment’s return when receipts and expenses occur on specific dates that may be irregularly spaced.

1
Pair every cash flow with its date Include the initial investment and later receipts or expenses. Money paid out is negative and money received is positive. Keep each amount aligned with its date.
2
Account for the days between cash flows XIRR uses the number of days from the first date to each later cash flow. The time between payments comes from those dates, rather than their positions in the range.
3
Calculate the annualized return XIRR searches for the annual rate that makes the dated cash flows’ net present value zero.

XIRR syntax & arguments

Syntax

=XIRR(values, dates, [guess])
Required Optional
  1. 1

    values

    Required

    A range or array of cash flows, including the initial investment. Each amount must match the date at the same position in dates. Include at least one negative and one positive value.

  2. 2

    dates

    Required

    The Excel dates corresponding to the cash flows in values, with the same number of entries. Put the earliest date first. Dates do not need to be equally spaced.

  3. 3

    guess

    Optional

    An initial estimate of the annualized rate for the iterative calculation. Try another estimate if the calculation does not converge. Defaults to 10%.

Example

=XIRR(B3:B5,C3:C5)

Return the annualized internal rate of return for the cash flows in B3:B5 using their corresponding dates in C3:C5.

XIRR caveats

  • Both signs are required

    Money paid out is negative and money received is positive. The values must contain at least one of each sign. An all-positive or all-negative series returns #NUM!.

  • Every amount needs a matching date

    The first value belongs to the first date, the second value to the second date, and so on. Ranges with different numbers of entries return #NUM!. When sorting the data, move amounts and dates together so each pair stays intact.

  • The first date must be the earliest

    XIRR uses the first date as its starting point. Later entries do not have to be sorted, but any date earlier than the first returns #NUM!. Keeping the pairs in chronological order makes the timeline easier to check.

  • Use Excel dates, not text labels

    Supply cells containing Excel dates or create dates with DATE, such as =DATE(2027,1,1). An invalid date returns #VALUE!. XIRR ignores the time of day because date serial numbers are truncated to whole days.

  • The result is annualized on a 365-day basis

    Monthly or irregular cash flows still produce an annualized return. XIRR discounts using the actual number of days divided by 365, including across leap years. IRR instead returns a rate per equally spaced period, so the two functions can produce different results for the same amounts.

  • The calculation may fail to find a rate

    Excel searches for a rate starting from guess, which defaults to 10%. If it cannot find a sufficiently accurate result within 100 iterations, it returns #NUM!. A different guess may help, but does not guarantee a result.

  • More than one rate can fit the same cash flows

    When cash flows change sign more than once, more than one rate may make their net present value zero. Different guesses can lead to different rates that satisfy the same calculation.

  • A negative return can be valid

    A loss can produce a negative rate. For example, paying out 1000 and receiving 900 exactly 365 days later gives an annualized return of -10%. A negative result is not itself a calculation error.

Comparing project value at a required return? Use XNPV with the same cash flows and dates and your chosen annual discount rate to calculate their net present value.

Intro XIRR practice problem

Solve the intro problem directly here, or open it on its own page.

Open full problem

Advanced XIRR practice problems

Use XIRR alongside other Excel functions in realistic, less-prescriptive challenges.

No advanced XIRR problems are currently available.