Calculate and Capitalize Compounding Interest Quarterly

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?

Compounding Interest Quarterly Desired Output.xlsx

Compounding Interest Quarterly Input.xlsx

Workflow

Accepted answers

AngelosPachis

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

example_interest_calc.yxzp

All comments

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

Hi @AkisM ,

Hope that helps,

Angelos

example_interest_calc.yxzp

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280