Excel Monthly Interest Model - Alteryx Solution?

Question

Hey AC,

I've recently built an Excel model to perform interest calculations on a home loan. I am having trouble converting this into an Alteryx Workflow - I believe I'll need to use a batch macro - unless anyone has any other out-of-the-box solutions?

The premise is as follows:

* Two accounts, home loan and offset account
* Interest is calculated on each independently, daily (closing balance * interest rate/365)
* These interest amounts are netted off each day (home loan - offset)
* This interest is accumulated until the end of the cycle (12th day of each month), where it is then added to the home loan account

I've got a 6 month period to calculate the interest over, and the attached Excel model does a decent job of this. Hoping Alteryx can win this one though!

Also see attached my (summarised) attempt of creating an Alteryx model that performs this - I got as far as calculating a closing balance for each, using the multirow function.

However, there are issues here as the closing balance does not include the accumulated interest until the last day of the cycle (while my current closing balance is adding the interest daily). As a result, I need an iterative process that performs the calculation line-by-line, storing the daily interest and accruing it until the final day of the cycle.

Any help would be much appreciated!

Cheers

AlteryxExampleModel.xlsx

InterestModel.yxmd

MichaelLaRose · Accepted Answer

Hi @jordanalytics

I want to start by saying that you were actually looking for an iterative macro, not a batch macro. Because you need to use records twice you should iterate (This is possible by replicating records and then building a batch macro but it is more complicated.)

I created a macro with a sub-macro in order to do what you were attempting. The first level isn't strictly necessary but makes the setup much nicer. The first level preps the data for iteration while the second level actually loops through the formulas in order to build each row, one at a time.

Let me know what you think.

Best,

Michael