Created two columns that use each other values on calculation

Question

Hello Everyone,

I got stacked on a calculation  point on Alteryx

Excel is great at auto updating columns so that I can calculate these two columns simultaneously, but I am having a hard time figuring it out in Alteryx. I search on community, but nothing solved my problem.

I need to calculate using Alteryx  Col "Present Value2" and  Col "Interest2" . I figure out how to calculate the first values, but after that I got stacked.

You can find attached the excel file the the workflow that I have created.

Any help appreciated.

Thank you in advance!

Book1.xlsx

IFRS16.yxmd

apathetichell · Accepted Answer

building amortization tables takes a bit of getting used to - and requires multi-row formula (multiple times)

apathetichell · Accepted Answer

try this... the keys are:

1) make sure you have a 0 (beginning balance row)

2) use a multi-row formula to create a new column for outstanding principal - for row 0 that's beginning balance

once you have that - you need to use    lease-payment-[Row-1:outstanding principal]*[interest rate]/12 (monthly payment - calculation of amount owed in interest on the previous month's balance) - and subtract that amount from the previous months balance.

That gives you your balance schedule. I use a second multi-row to compute the principal paid and a vanilla formula tool to break out interest from the lease payment.