The best approach to understand what i need is taking a look at the sample spreadsheet

https://docs.google.com/spreadsheets/d/1AyqCMvbjUt3nlqvE2ZLbmPfixwh_i1nIl9HMTn4pETY/edit?usp=sharing

What i need is unpivot the data:

1. 1st date payment
2. xx months depending on the months entered in Col G (here i need to round the amount of the monthly payments, and in the last payment adjust the amount, so i don't get cents in the monthly payments divided)
3. last date payment

i believe the best way is generate a data base ordered correctly, from there know in which date every client has a due date for his payment

The way im entering the data is as the sample sheet shows from A:J And my expected result is in range L:P

any help on this please will be very much appreciated

CodePudding user response：

This is probably best handled by breaking the problem in 2 parts. Monthly payments and First/Last Payments. I've laid out one possible solution on your sheet in a tab called MK.Idea.

I used a SPLIT(FLATTEN( technique to generate both sets of cashflows and then a simple query to stack and order them.

This formula generated the monthly flows:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN('S1'!A2:A&"|"&'S1'!B2:B&"|"&MROUND(('S1'!D2:D-'S1'!E2:E-'S1'!I2:I)/'S1'!G2:G,100)&"|"&EDATE('S1'!H2:H,SEQUENCE(1,MAX('S1'!G2:G),0))&"|"&EDATE('S1'!H2:H,'S1'!G2:G)&"|"&"Monthly "&SEQUENCE(1,MAX('S1'!G2:G),1)),"|",0,0),"select Col4,Col1,Col2,Col6,Col3 where Col4<Col5"))