Alteryx Designer Discussions

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

Loan table using multi-row

Takkarstar
7 - Meteor

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

 

 

3 REPLIES 3
AZuc
Alteryx
Alteryx

@Takkarstar , what would be your expected result? Do you have it with formulas in Excel?

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


Takkarstar
7 - Meteor

@AZuc Hi ,

 

Please see the expected results

mceleavey
16 - Nebula

Hi @Takkarstar ,

 

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:

 

mceleavey_0-1637864922566.png

 

This has an embedded iterative macro which looks like this:

mceleavey_1-1637864959881.png

 

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.

mceleavey_0-1637865275652.png

 

 

I've attached the workflow for you.

 

I hope this helps,

 

M.

 

Labels