Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel Monthly Interest Model - Alteryx Solution?

jordanalytics
5 - Atom

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

 

 

 

2 REPLIES 2
MichaelLaRose
10 - Fireball

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

jordanalytics
5 - Atom

Hi Michael,

 

Very nice solution! This does exactly what I was looking for.

 

I can now filter for the end of cycle days and summarise on those interest charges.

 

Cheers,

Jordan

 

P.S Excuse my terminology on the macros - iterative was what I was going for!

 

 

Labels