Iterative Interest Payment Calculation - Multiple Loans

[TommyGoodone]

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.

[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

[AngelosPachis · Accepted solution]

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.

Let me know if that helped 🙂

Angelos

[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.

[AngelosPachis · Accepted solution]

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

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

[TommyGoodone]

Hi Angelos,

Thanks for that. I seem to be unable to replicate the results that you get with those two tools being added. on my end the nulls still appear - perhaps I have overlooked something. Instead, I added the two tools in the main page of the script and that seemed to work really well!

I am now adjusting the source to use one of my real data inputs (250k line items) to see how this will go 🙂

Thanks!