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.
XIRR syntax & arguments
Syntax
=XIRR(values, dates, [guess])
-
1
values
RequiredA 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
dates
RequiredThe 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
guess
OptionalAn 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
1000and receiving900exactly 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.
Advanced XIRR practice problems
Use XIRR alongside other Excel functions in realistic, less-prescriptive challenges.
No advanced XIRR problems are currently available.