Creating a first in first out calculation (Possible a loop through an iterative macro)

[ScottyCarr]

Hi to all,

First time posting, so bear with me :)

I'm having some issues designing a workflow to automate a "first in first out" accounting calculation. For the sake of this example, we'll use a loan in a foreign currency.

Assume I am a Canadian citizen who lends you out money in USD on various occasions, and you pay me back on various occasions. Now for the sake of lending, since you are my good friend, I don't charge you interest, and I just ask you to pay me pack in the same currency I lent you money in (USD).  However on my end, I do want to calculate the amount I gained or lost as you pay me back. To do so I want to calculate the difference in the rate of exchange multiplied by the amount. However since multiple debts occur, I'll use the rate of exchange of the first debt until it is totally paid and then the rate of exchange of the next debt until it is fully paid and so on and so forth. 

I've shown how to manually calculate it in excel, but I'm having a particular hard time transferring this to alteryx in a way that could handle a very large amount of transactions automatically. 

My issues comes with figuring out how I can create a dynamic calculation that considers the right exchange rate (like a running total until it passes the total amount of the first debt). 

Any help is much appreciated :)

[danrh]

Iterative macro is probably the way to go, but if that doesn't suit take a look at the attached.  There has GOT to be a simpler way to do this.

[ScottyCarr]

There is probably a simpler way, but this way does work just fine. Great workaround For now I'll use this method in my workflow while I build a macro in the long run. 

Cheers, 

[ScottyCarr]

I am running into a problem when we have multiple debts that are later repaid by the same payback. Any idea on how we could solve this? I was trying to adjust the extra, but I'm realizing we might actually need multiple payout lines for each amount to account for the ROE of each debt.

What do you think? :)

[danrh]

I've spent a few minutes trying things and I'm at a loss on this one.  There's likely a way to do it, but I'd look into an iterative macro -- someone else will likely need to chip in here for advice on that, I'm still a novice :)

[ScottyCarr]

Off to youtube land I go. Thank you so much for your help regardless :)

If someone wants to give a hand, I've attached the desired outcome on a simplified level of the excel sheet (FX Gain / Loss). I'd really love to see how to do this with an iterative macro. 

Thanks!

[KaneG]

Great question and you could definitely use an iterative macro whereby you line up the payments and distribute the debt across them one by one (The iterative part would be the left over payments). It's probably easier to go that way rather than the other.

If you want to play with this type of solution, check out the Weekly Challenge #14 as it deals with distributing to warehouses via priority  (I remember it, because it was in the very first certification exam a few years ago).

I had a go at creating a macro, but before I knew had done it without... take a look at the attached, it might not be intuitive at first as it's not looking at it in a procedural manner....