Goal seeking and XIRR in PowerBI and PowerQuery

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 🙂

Comments (3) Write a comment

  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

Leave a Reply