Stacked XIRR Calcs

[TommyGoodone]

HI There,

sorry if this is the wrong thread for the post. I have a question regarding a somewhat complex logical problem I wish to solve.

in the attached file, I have some dummy data in four columns, being:

Client ID

Transaction ID

Date

Amount

I am trying to create a flow that will allow me to calculate the IRR for each transaction, where each transaction starts with a negative value and has the cash flows following it. Normally in Excel you can do this for a single cash flow, however in our business we'd be looking to perform this for a total of ~60k transactions with 700k rows in total. 

any idea what function I might use (I'm thinking the transaction ID) as a unique identifier to tell the formula/script where to start and finish in terms of creating an array, and then using that array to calculate the IRR, then outputting it somewhere in the format of:

Transaction ID               IRR

Thanks for any advice on this - loving the software so far, just want to see how far we can push it to automate some of our very manual processes

[atcodedog05]

Hi @TommyGoodone ,

It would have been great if you could have clearly told us the IRR calculation.

But any ways i will give it a try.

[atcodedog05]

Hi @TommyGoodone ,

Based on your description possible approaches can be

1. using a running total groupingby ClientID and TransactionID

2. using running total for any other kind of calculation  groupingby ClientID and TransactionID

https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

I have attached the workflow.

Hope this helps : )

If this post helps you please mark it as solution. And give a like if you dont mind : )

[atcodedog05]

Hi @TommyGoodone ,

Please provide the desired output. So that i can build on those lines.

[TommyGoodone]

Thanks a bunch for this. it's certainly a step in the right direction.

In terms of the actual Calc - I've made some serious progress in Excel to simulate what I'll be intending to implement in Alteryx. Please see the attached file.

you'll see in Excel it's a pretty long formula which populates based on a spill formula to the left. it now works in Excel, so with your logic i think I can get it functioning in Alteryx 🙂

[atcodedog05 · Accepted solution]

Hi @TommyGoodone ,

Happy to help : )

Pretty sure you can pull it off. All the best 👍

If you run into any roadblock you can reply back on this post.

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[jdunkerley79 · Accepted solution]

The summarise tool can do what you need:

Parse the date into an Alteryx DateTime

Group by Client, Transaction

There is an XIRR function within finance menu - select it on Amount and choose the datetime field for the date column

The table will be produced.

Sample attached 

[TommyGoodone]

Thanks so much for the help on this one guys. wonderful suggestions, which have not only helped me understand how to work the data (as a new user) but also solve the initial problem that I had posed.

Thanks!