Iterative Interest Payment Calculation - Multiple Loans

Hi Guys,

I recently had some help in building out a flow which calculates the IRR for 60k transactions and allows them to be grouped by Client etc.

I am now looking at building an iterative calculation to work out the Interest payments for each of these transactions.

In excel, this happens based on a rolling Balance - as provided in the attached file.

The second sheet of the file has the output format after running the IRR calc in Alteryx. the IRR figure is what needs to be used to iteratively determine the rolling balance.

I saw a similar problem & solution in this thread, however noticed that the solution used some hard coded inputs, where as I'd be wanting to get the initial loan value from 60k + loans and the iteratively work out the interest values for each instalment of each loan.

I think this might actually be quite a common requirement for anyone in the lending space, and a solid solution could be very useful to a lot of people and businesses as these calculations en masse are very cumbersome for older systems or simple Excel files.

Kind regards,

Tony.

ExampleCalculator.xlsx

Macros

Accepted answers

AngelosPachis

As mentioned earlier, my solution to your question includes a batch macro including an iterative macro.

The batch macro allows to run the workflow for each ClientID-Transaction ID combination. Then some data cleansing occurs to prep the data for your iterative macro but as long as the data you will feed in remain the same as that in the example you provided, then everything should work fine.

Screenshot 2020-10-09 123710.jpg

Let me know if that helped 🙂

Angelos

example_v2.yxzp

AngelosPachis

Hi @TommyGoodone ,

Thanks for accepting my solution. Regarding the nulls in the Client ID and Transaction ID columns, you can use two multi-row formula tools to populate the nulls of each column.

Open your batch macro and after the iterative macro output, add the two tools with the following formula

Screenshot 2020-10-12 103602.jpg

One should update the Client ID and the other the Transaction ID. The fields used in each multi-row formula tool should change accordingly, so when you are updating the Client ID then your formula should include only Client ID fields.

Hope that helps.

Cheers

Angelos

All comments

AngelosPachis

Hi @TommyGoodone ,

I think I have created an iterative macro that does what you are looking for. This is the first version and it was built on the example you provided (first tab of your excel file), but I will configure it accordingly to receive as an input whatever is in the second excel tab and create the desired output.

It seems like for that you are gonna need both a batch and an iterative macro.

Have a look and let me know if that's what you are looking for. I will revert with the second version 🙂

Cheers,

Angelos

example.yxzp

AngelosPachis

As mentioned earlier, my solution to your question includes a batch macro including an iterative macro.

Screenshot 2020-10-09 123710.jpg

Let me know if that helped 🙂

Angelos

example_v2.yxzp

TommyGoodone

WOW. just Wow. once again I am blown away by the quality and skills of the community here. this is an amazing solution, cleaner than what I was coming up with and (once I figure out how it handles rounding) almost perfectly gets the solution.

In reality for Transaction 1, there's a total of 1,000 interest accrued. I believe the output alteryx gave gives Transaction 1 a total of 999.81 (2 D.P.) but this might be the truncation to 6 DP for items during export.

Only other piece, is the new lines representing the interest accrual have nulls in the Client ID and Transaction ID column. what would I change to populate those lines with the correct ID, thus allowing a group by Transaction ID to correctly sum the interest for each transaction?

Thanks again for your wonderful insight into this project!

Tony.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703