Iterative Macro to calculate running balances

Question

Hi All,

Looking to implement an iterative macro that will handle the following data.

PY Running Balance should be reduced by the PY Portion of Reduction amount each month.

CY Running Balance is previous month CY Running Balance + CY Monthly Activity - CY Portion of Reduction.

PY Running Balance cannot be less than 0. In this scenario, the PY Portion of Reduction (Total Monthly Reduction multiplied by PY Reduction Rate) will be replaced by the maximum reduction amount to reach a PY Running Balance equal to zero. The remaining Total Monthly Reduction will be allocated to CY Running Balance. Refer to row 8 in working example.

'Base Data' tab is the currently available data. All other data needs to be calculated.

The same process needs to be repeated for different jobs (2, 3, 4 etc.) and given that the apportionment of the reduction amount and the running balances need to be calculated dynamically, we believe an iterative macro is the solution to this problem.

Example.xlsx

apathetichell · Answer

I initially designed this like I'd have to use a batch macro for the differing principal amounts... Here's a version thats just a bunch of multi-row formula... The key is to replace the original PY Reduction amount with the actual reduction amount once you've calculated the PY payoff schedule. Then you go back and calculate CY and the CY monthly amounts....

2021-07-30 just a bunch of multi-row formulas.yxmd

Treyson · Answer

Actually, I think that after reading through your question and looking at the data, you may also be able to solve this with the multi-row tool. It allows you to do formulas on records above or below whatever record that the formula is currently being run on. Does that make sense?

Treyson · Answer

Hello @abbottscdp.

If you are interested in some reading about this, here is my shameless plug. I wrote a blog series a few years back about macros. If you want to try it on your own, you should read this: https://community.alteryx.com/t5/Engine-Works/Hello-Iterative-Macro-My-Old-Friend/ba-p/420308

If you want someone to build it for you, feel free to send me a PM.