Downloads
DAXFinancialAdvanced

XIRR

Returns the annual internal rate of return for cash flows that fall on specific dates, which do not need to be evenly spaced.

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

Returns: A decimal annual rate, such as 0.097 for 9.7 percent. It is the rate at which the XNPV of the cash flows is zero.

What it does

XIRR finds the annual rate of return that makes the present value of a series of dated cash flows equal to zero. It answers "what return did this investment earn?" when the money goes in and comes out on uneven dates. Finance teams use it to report the return on a private investment, a real estate deal, a fund distribution or a project with interim receipts. It is the dated version of the IRR function, and it uses the same day count as XNPV.

Syntax and arguments

Sign convention: the investment is negative and the proceeds are positive. The first row is usually the date of the investment.

Example

The same project as in the XNPV example: 10,000 invested on January 15, 2024, with 4,000 received on each of the next three January 15 dates. The controller wants the annual return.

Project IRR =
XIRR(
    'Project_Cash_Flows',
    'Project_Cash_Flows'[Amount],
    'Project_Cash_Flows'[CashFlowDate]
)

The result is about 0.0969, which is 9.7 percent a year. At that rate, the XNPV of the four cash flows is zero. That matches the XNPV result: 8 percent gives a small positive value and 10 percent gives a negative one, so the break-even rate sits between them.

Example: a second, different use

The return shown as a percentage on a dashboard card, with a guess supplied so that the function converges on an unusual deal.

Deal Return % = FORMAT(XIRR('Deal_Flows', 'Deal_Flows'[Amount], 'Deal_Flows'[CashFlowDate], 0.1), "0.0%")

Things to watch

In business terms

XIRR supports the reporting of returns on private equity, real estate, lending deals with fees and interim payments, and the internal return on a capital project. Investment committees ask for the XIRR because it reflects the timing of every dollar, not only the total gain.

Related functions

All DAX functions · View as Markdown · DAX fundamentals