Calculate and Capitalize Compounding Interest Quarterly

[AkisM]

Hi all,

I'm trying to create a workflow that makes some interest calculations. The theory is simple. Each end of month, there should be an interest calculation, and the rate is given (I'm attaching below an excel sheet with the plain input data). Where it gets tricky is that, at the end of each quarter, all accumulated interest must be moved to the "principal" balance (what we call capitalization of interest), and the interest balance must be zero'ed out on the beginning of the next quarter. Additionally, that means that all subsequent interest calculations will now be made based on a new, bigger principal, since we added some interest on top of it.

I'm also attaching the desire output showing all the calculations within formulas if the above description was not helpful. To me it's a bit difficult to figure out how to do this in alteryx, since alteryx usually calculates everything from top to bottom, but in this case the calculations need to be done quarter by quarter from left to right before moving to the next quarter.

Any ideas on how to get from Input excel to Output excel using alteryx?

[DawnDuong]

hi @AkisM 

For such "rolling forward" type of accounting/finance question (e.g. compounding interest, asset depreciation, IFRS16 application etc...), you can solve it with the Multi-row Formula Tool.

https://help.alteryx.com/20213/designer/multi-row-formula-tool

This Tool is very popular for accounting/finance application - so I really encourage you to check out the above link to help document and also explore the example file which comes pre-installed with the Designer (right-click on the Multi-row Formula Tool, then click Open example).

Dawn.

[AngelosPachis · Accepted solution]

Hi @AkisM ,

I think you need an iterative macro to tackle this problem which like you said will allow you to do all calcs per row on a quarterly level. Also it will allow you to account for the fact that each periods interest is dependent on any interest from prior periods plus any Ending amount for each quarter.

I've attached a sample workflow that I believe does what you're looking for. I have also included a series of tools flagged as iteration 1, 2 and so on that will emulate the process that the iterative macro follows.

Hope that helps,

Angelos

[AkisM]

That is very helpful and exactly what I was looking for @AngelosPachis , thanks for going the extra mile to also include workings iteration by iteration! Now I can study your workflow to become a bit more comfortable with iterative macros as well.

[AkisM]

One last question @AngelosPachis , I noticed there is a "Max number of iterations" in the macro. It was set to 100 by default and since my dataset can sometimes be longer than 100, I wanted to make the limit infinite, or at least something artificially high.

So I set it to 5000. But I noticed that after setting it to 5000 and actually tries to do 5000 iterations, which makes no sense, because my workflow only has 123 records to pass to the macro, so I would expect it to stop after it has finishing processing all the records. Any idea why it loops infinitely (up to the manually set upper limit) instead of just stopping after finishing the records?

I noticed that this happens in the sample you provided as well before I made any changes to it. The message says "100 iterations were run with 16 iterative records remaining". My goal is to make it iterate only for as many times as there are records. No more, no less.

[AngelosPachis]

@AkisM the max number of iterations is one way that you can make the macro stop from running. Like you noticed, Alteryx will keep running iterations, even if there are no records to process. If you set it to 5000 and you only have 123 records it will keep going an extra 4877 iterations that would not change the output.

The second way that you can make the macro stop is to add a condition (filter tool) and then have two output anchors, one being your iterative one and one being your main output. Your workflow will then stop if there is nothing falling out of the iterative output.

[AngelosPachis]

@AkisM 

So this is how you can make it stop. I've added a summarize tool outside the macro to get the maximum number of record ID

Now inside the macro I've added a condition (filter tool) that will keep feeding data to the iteration output as long as the number of iterations is less than the  maximum record ID. Since each records is processed one by one, you want your macro to stop once the iteration number is equal to the maximum number of rows in your table.

Up until that point, everything will fall out of the F output anchor of the filter tool, which in turn is going to loop the data back into the iteration input (red line). Once your condition is met, all records will fall out of the T output anchor of the filter tool (blue line) and no records will fall out of the iteration output which will cause your macro to stop.

So now my macro has not run 100 times but only 15, which is equal to the maximum number of records in my table minus 1, as the iteration counter in Alteryx starts counting at 0 (so the first iteration ID is 0 and not 1)

[AkisM]

Perfect, thanks a lot for your time and detailed explanations @AngelosPachis !