<!-- https://learnsql.cpetoday.com/functions/dax/xirr -->
# XIRR

Language: DAX
Category: Financial
Syntax: XIRR(<table>, <values>, <dates> [, <guess>])
Summary: Returns the annual internal rate of return for cash flows that fall on specific dates, which do not need to be evenly spaced.
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.
Level: Advanced
Related: XNPV, RATE, DATEDIFF, SUMX, MAX

## 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
- table: the table that holds the cash flows and their dates. Required.
- values: the column of cash flows. Money you pay out is negative, and money you receive is positive. The list must contain at least one negative and one positive value, or the function returns an error. Required.
- dates: the column of dates for the cash flows. Each date must be a real date. Required.
- guess: a starting estimate of the rate, such as 0.1. Optional. Supply one when the function fails to find a rate.

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.

```dax
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.

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

## Things to watch
- The list must have at least one negative and one positive cash flow. A list with only outflows, such as a deal that has not paid out yet, gives an error.
- The rate is annual, on the same 365-day basis as XNPV. Compare it only with other annual rates.
- If the result fails, give a guess near the expected return, for example 0.1 for 10 percent.
- Multiple sign changes, such as a deal with a later cash call, can give more than one rate. Check the answer against XNPV at that rate.
- A high return on a short hold can look impressive. Show the amount invested and the holding period next to the rate.

## 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.
