Closing balance of a row needs to move in next as opening balance

[Kamran1991]

Hi all,

I am building a loan repayment schedule and trying to update  Opening Balance as Closing Balance  of last row. It's working for the second row only. Additionally, there is no change on the amount of Interest in second row, as Opening Balance is reduced. Someone please help me on the missing part of the formula.

 

[AngelosPachis]

Hi @Kamran1991 ,

Since your payment and interest columns depend on the opening balance which changes for each month, a simple multi-row formula tool won't take you where you are looking to go, because for your second row your payments and interest will be different than in the first row.

To solve this problem, you probably have to create an iterative macro that will yield the correct result for you. I'm sure there should be a similar post in the community that uses a similar iterative macro.

[Kamran1991]

Hi @AngelosPachis,

thanks for your reply.

Can you help me to find this solution means iterative macro for it ?

[AngelosPachis · Accepted solution]

@Kamran1991  I had this post in mind but then I realised it's quite complex for your use case

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Iterative-Interest-Payment-Calculation...

If you are not familiar with macros I would suggest first going through this interactive lesson of the Academy

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

I have created a workflow for you that probably answers your question

The iterative macro will isolate each record one by one, starting from month number 1, estimating the interest and principal and then finding the closing balance for that month.

It will then go to the next month, and replace the opening balance with the closing balance of the prior month and pass the data through the macro on the next iteration. On the second iteration, month number 2 will be isolated, the interest and principal will be recalculated based on the new opening balance values and a new closing balance will be estimated for that month. Then again, the closing balance for month #2 will become the opening balance for month #3 and the next iteration will start and so on, until you don't have any more months in the data you are feeding the macro.

In the formula tool, there are also quite a few Finance formulas that might have come in handy but personally I'm not very familiar with them. But if you are in the world of Finance, then it might be worth having a look at them.

Cheers,

Angelos

[Kamran1991 · Accepted solution]

Hi @AngelosPachis,

Thanks for your help. It is a little difficult to understand for beginners but your post is helpful.