XIRR

The internal rate of return of cash flows on dates, each row of a table a value and its date.

Group: Financial. Compatible with DAX.

Syntax

XIRR ( <table>, <values>, <dates> [, <guess>] [, <alternateResult>] )

Parameter	Type	Description
`table`	Table	The cash flows: one row for each payment or receipt.
`values`	Expression evaluated for each row	The amount of each row: negative for money paid out, positive for money received.
`dates`	Expression evaluated for each row	The date of each row's amount.
`guess`	Number optional	Where the search for the rate starts; 0.1 when left out.
`alternateResult`	Value optional	What to return when no rate can be found, instead of an error.

Returns

Number. The internal rate of return a year, as a decimal: the rate at which XNPV of the flows is 0.

Remarks

Every amount is discounted to the earliest date of the table, in years of 365 days, whatever order the rows are in. The times of day are dropped.
Rows whose amount is blank are left out. A row with an amount but no date is an error.
The flows need at least one negative and one positive amount, or there is no rate: then the result is an error, or alternateResult when given.
The rate is found by trial, so it agrees with the exact root to about eight digits.
For regular periods without dates, RATE gives the rate of a level annuity.

Examples

The return on a new line

500,000 invested in January 2024, then four payments back over almost three years.

EVALUATE
VAR Flows =
    DATATABLE (
        "Date", DATETIME, "Amount", DOUBLE,
        {
            { "2024-01-15", -500000 },
            { "2024-09-30", 120000 },
            { "2025-06-30", 180000 },
            { "2026-01-31", 150000 },
            { "2026-10-31", 210000 }
        }
    )
RETURN
    ROW ( "IRR", XIRR ( Flows, [Amount], [Date] ) )

IRR
0.163099418580532

When there is no rate

Only money coming in: no rate makes the value 0, so the alternate result is returned.

EVALUATE
VAR Incoming =
    DATATABLE (
        "Date", DATETIME, "Amount", DOUBLE,
        { { "2026-01-31", 100 }, { "2026-07-31", 100 } }
    )
RETURN
    ROW ( "IRR", XIRR ( Incoming, [Amount], [Date], 0.1, -1 ) )

IRR
-1

XNPV, RATE, PV.

Last updated 3 November 2026 · OpenBI Studio 0.3.74