Loan table using multi-row

I have to calculate interest and closing value of loan using opening value , payment and rate.

I have tried in Alteryx but either I get wrong interest figure or wrong closing balance.

I have added the input file and the Alteryx workflow ( in excel I have calculated first months figures to show how closing value and interest needs to be calculated).

Please can someone tell me where am I going wrong

Raw Data 2.xlsx

Loan Table.yxzp

Datasets

Transformation

Data Investigation

Accepted answers

mceleavey

Hi @Data_Alter ,

I think you're going wrong because you're trying to calculate something which depends on the opening balance being pre-calculated on the row above. This will never work on a multi-row formula, you need to use an iterative macro where each row begins where the last one finished.

I've built the following workflow:

This has an embedded iterative macro which looks like this:

This iterated through every row, calculating the interest, closing balance etc. for the current row (beginning at Period 1). The closing balance is then passed to the first row of the remaining rows and replaces the opening balance of that row. All calculations are made on that row and the whole thing repeats until all rows have been calculated.