# Help in running a calculation for monthly installment payments from Day 0 to Contract Ending Date, segregated

Hey,

So, I’m new to Knime and have been developing some easier floews and studying a way to build a flow to help me run a monthly analytical base, segregate it by start date and due date (contract ending date) and calculate the monthly amount of installments due to payment for this vendor.

The expenses invoiced in January must be paid in February, and so go on.

I have a base as below:

Start Date (DD/MM/YYYY) Number of Installments (Months) Total Amount Due Monthly Payment (Total Amount Due / No. of Installments)
01/01/2024 3 3000 1000
01/01/2024 6 3000 500
01/02/2024 2 3000 1500
01/02/2024 3 3000 1000

And it must become like this:
(All dates in DD/MM/YYYY format)

Start Date (DD/MM/YYYY) Number of Installments (Months) 01/02/2024 01/03/2024 01/04/2024 01/05/2024 01/06/2024 01/07/2024 Total Amount Due
01/01/2024 3 1000 1000 1000 3000
01/01/2024 6 500 500 500 500 500 500 3000
01/02/2024 2 1500 1500 3000
01/02/2024 3 1000 1000 1000 3000
MONTHLY PAYMENTS 1500 4000 4000 1500 500 500 12000

The main goal is to be able to see, in an aggregate/grouped way, the monthly value to be paid each month, while also having an overlook of the entire period - from start date transactions dating a couple years back to the last installment (so far, 2030).

Also, there is new entries each month, so, as an example, we could have a 1.000 payment due in 12 installments, and another one of 6.000 due in 18 installments from next month - 01/03/2024 (DD/MM/YYYY).

What I am finding the hardest is to make Knime create like 60 columns for 60 monthly installments in 01/02/2024 (DD/MM/YYYY), and next month using not the actual column, but the next column and also rightfully calculating another 60 months.

I guess this is a rather complex problem, but would be grateful if you could at least point me in the direction of what to study in order to get this result - if it is possible.

Thank you all!

Hi @dml97

Welcome to KNIME Forum. See this wf monthly payments.knwf (62.0 KB)

With the Chunk Loop every record is processed in the wf. The One Row to Many node duplicates the records according to the Number of Installments.
The Date&Time shift is used to created the sequence of the months based on the created counter values.

gr. Hans

5 Likes

Thank you very, very much! You solved my entire problem.

I’ve ran the calculation that was being made manually since jan/2022 till feb/2024 (payments until 2030) and all the amounts are matching between the manual and automated calculations for each single month.

Best!

1 Like

OMG

This is so amazing.