XIRR Function (DAX)

How does the XIRR function (DAX) work?

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

XIRR Formula Syntax

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

How do you use the XIRR function?

The XIRR function calculates in the internal rate of return for series of cash flows that occur at irregular intervals. To calculate the internal rate of return for a series of regular, periodic cash flows, use the IRR function.

Related Blog Posts

Related Support Forum Posts

Dax Calculation question
Dax not working
DAX Buffer Calculation Query

Considerations when using the XIRR function

  • When entering the cash flow values, the value that is dated at the beginning of the investment must be negative.
  • The cash flow values can be listed in any chronological order because the corresponding date is provided.
  • If you don’t have at least 1 positive and 1 negative cash flow value, the XIRR function will return the #NUM! error.
  • If you provide an invalid date in the date parameter, the XIRR function will return #NUM! error.
  • Excel tries to recalculate the XIRR until the result is accurate within 0.000001 percent. If after 100 tries Excel has not calculated an accurate value, it will return the #NUM! error.

Related Video Tutorials

Formula examples using the XIRR function

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

=XIRR(A3:A7, B3:B7, 0.1)

=XIRR(A4:A8,B4:B8)

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI

DOWNLOAD

To access our comprehensive DAX formula reference guide just insert your email address below. The resource will download immediately and we’ll also email it to you.