Excel functions / Financial

XIRR function

The yearly rate of return for cash flows that fall on any dates.

Syntax

=XIRR(values, dates, [guess])

Arguments

When to use it

XIRR gives the yearly rate of return for money that goes in and comes out on any dates: an investment topped up now and then, a property bought and sold, a loan repaid unevenly.

IRR assumes the cash flows are exactly one period apart. XIRR reads the real dates, so use it whenever the gaps are not equal. The result is always a rate per year.

Examples

  1. The yearly return on dated cash flows

    ABCD
    1DateCash flow
    22025-01-01-1000016.3%
    32025-06-152500
    42026-01-014000
    52026-09-305500

    In D2: =XIRR(B2:B5, A2:A5) → 16.3%

    Money paid out is negative, money received is positive.

    Try this example

  2. Check it: at that rate the cash flows are worth nothing today

    ABCD
    1DateCash flow
    22025-01-01-100000
    32025-06-152500
    42026-01-014000
    52026-09-305500

    In D2: =ROUND(XNPV(XIRR(B2:B5, A2:A5), B2:B5, A2:A5), 2) → 0

    XIRR is the rate at which XNPV comes to zero. That is its definition.

    Try this example

Common mistakes

Common questions

What is the difference between IRR and XIRR?

IRR assumes every cash flow is one equal period after the last and gives a rate per period. XIRR takes the date of each cash flow and gives a yearly rate, so it is the one to use when payments are not evenly spaced.

XIRR in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try XIRR in the playground

Related functions