Loan table using multi-row
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Data Investigation
- Datasets
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Data_Alter , what would be your expected result? Do you have it with formulas in Excel?
Sales Engineer - LATAM -
Alteryx, Inc.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
I've attached the workflow for you.
I hope this helps,
M.