Goal seeking and XIRR in PowerBI and PowerQuery

by

If you want to solve Excels XIRR-function with M in PowerBI or PowerQuery you have to use a goal seeking algorithm. I tried it with a binary-search and the results were quite good (on my scale):

Comparison of XIRR results between native Excel function and M

Wondering if there are other solutions out there or different techniques regarding the “helper”-elements I had to include here, so please come forward 🙂

Goal-seek for XIRR

Code:  GoalSeekXIRR.txt

The goal-value is formulated in a way that it should be zero, as this is what the binary-search procedure is aiming at. In this case it’s the XNPV. Other cases could be Break-Even for example, where the accumulated sales match the accumulated costs. In that case you would write: Result = sales – costs .

Subscribers can check it out in this file: GoalSeekForXIRR.xlsx

Enjoy & stay queryious 🙂

7 thoughts on “Goal seeking and XIRR in PowerBI and PowerQuery”

  1. Hi Imke,

    I’ve learned my lesson with Kmeans clustering algorithm and since stopped trying to implement number crunching algos in M 🙂

    Nevertheless the script you have here is a great example of how to do (for/while) loops in M.

    By the way, have you seen how the clustering was implemented in the tabular model? Apparently there is a KMeansClustering function in DAX. I guess this is where most of the algos will be implemented eventually.

    Cheers,
    Igor

    Reply

    • Hi Igor, thx!
      Have you shared your lessons learned re the Kmeans yet? (Looks like I’ve missed that)
      Haven’t seen KMeans Clustering in DAX either. Can you please share a link?

      Thx and cheers, Imke

      Reply
  2. Pingback: #Excel Super Links #50 – shared by David Hager | Excel For You
  3. Pingback: Writing data to GitHub using Power Query only – The BIccountant

  4. Hello Imke

    Perhaps a silly question but I struggle to pass a tablename “Cashflow” to the function in the excel file you have provided in this post. I tried to type in “Cashflow”, and [Cashflow} and just Cashflow – and nothing works coming back with an error message :
    An error occurred in the ‘fnXNPV’ query. Expression.Error: We cannot convert the value “Cashflow” to type Table.

    Thank you for your time!

    Reply

  5. Dear All – please ignore my earlier question. fnXIRR works perfectly well.
    For new to PQ, this function is to call the function from within a table of cashflow… not passing a table name to a function…

    Reply

Leave a comment