XIRR Function (DAX)

 
System_CAPS_ICON_note.jpg Note


This function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop only. Information provided here is subject to change.

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

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

Parameters

TermDefinition
tableA table for which the values and dates expressions should be calculated.
valuesAn expression that returns the cash flow value for each row of the table.
datesAn expression that returns the cash flow date for each row of the table.
guess(Optional) An initial guess for the internal rate of return. If omitted, the default guess of 0.1 is used.

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

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

XIRR Formula

The series of cash flow values must contain at least one positive number and one negative number.

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

Rate of return := XIRR( CashFlows, [Payment], [Date] )  

DatePayment
1/1/2014-10000
3/1/20142750
10/30/20144250
2/15/20153250
4/1/20152750

Rate of return = 37.49%

Community Additions

ADD
Show: