XIRR - Daxplained

XIRR is a DAX function that returns the internal rate of return for a schedule of cash flows that are not necessarily periodic.

Syntax

XIRR(
   Table,
   Values,
   Dates,
   Guess,
   AlternateResult
)

Argument	Properties	Description
Table		The table containing the rows for which the Values and Dates expressions will be evaluated.
Values		An expression to be evaluated for each row of the table, which will yield a series of cash flows.
Dates		An expression to be evaluated for each row of the table, which will yield a schedule of payment dates.
Guess	Optional	Optional. A number that you guess is close to the result of XIRR.
AlternateResult	Optional	Optional. The alternate result to return when XIRR cannot find a solution.

Return Values

Internal rate of return for the given inputs. If the calculation fails to return a valid result, an error or value specified as alternateResult is returned.

Remarks

The value is calculated as the rate that satisfies the following function:

$0 = \sum_{j = 1}^{N} \frac{P_{j}}{(1 + rate)^{\frac{d_{j} - d_{1}}{365}}}$

Where:
- $P_{j}$ is the $j^{t h}$ payment
- $d_{j}$ is the $j^{t h}$ payment date
- $d_{1}$ is the first payment date
The series of cash flow values must contain at least one positive number and one negative number.
Avoid using ISERROR or IFERROR functions to capture an error returned by XIRR. If some inputs to the function may result in a no solution error, providing an alternateResult parameter is the most reliable and highest performing way to handle the error.
To learn more about using the alternateResult parameter, be to check out this video.
When the absolute value of initial payment is small, the calculation likely fails to return a valid result.
Avoid including 0 values in payments. They will not impact the final result, and using 0 as initial payment will fail XIRR() calculation always.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

The following formula calculates the internal rate of return of the CashFlows table:

= XIRR( CashFlows, [Payment], [Date] )

Date	Payment
1/1/2014	-10000
3/1/2014	2750
10/30/2014	4250
2/15/2015	3250
4/1/2015	2750

Rate of return = 37.49%

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/xirr-function-dax