Automatically distribute an amount across multiple rows to settle against the values in another column

[vikrampnz]

Hello

I have a worksheet with multiple rows showing multiple bills details. I have bill amount in column C in each row. I want to enter a lumpsum payment amount in column F and want it to break down in column H as allotted payment to settle each bill in full and then flow onto the next row of H until the amount is fully consumed i.e 0.

The bills are entered datewise. So, basically when I enter a payment amount at a later date after the credit period, that row has multiple pending bill rows above. I would like it pick up the last unpaid / partly paid bill and settle from there down until the payment amount in that row is completely consumed.

I am attaching a sample sheet for your reference.

I would really appreciate any help I can get.

Thanks

Test Cycle.xlsx
	A	B	C	D	E	F	G	H	I
1		24-Jun-23		Credit Period:	30		Current Opening Balance:	5,00,000
2							Total Overdue:	6,25,000
3
4		Milestone	Amount	Work Date	Bill Due Date	Payment Amount	Payment Date	Allotted Payment	Balance From the bIll
5	1	B-9	1,25,000	05-May-23	04-Jun-23			1,25,000	0
6	2	A-10	5,00,000	10-May-23	09-Jun-23			5,00,000	0
7	3	B-10	3,75,000	02-Jun-23	02-Jul-23			1,75,000	2,00,000
8						8,00,000			#VALUE!
9									#VALUE!
10									#VALUE!
11									#VALUE!
12									#VALUE!
13									#VALUE!
14									#VALUE!
15									#VALUE!
BILL CYCLE

Cell Formulas
Range	Formula
B1	B1	=TODAY()
H2	H2	=SUMIF(E5:E21,"<"&B1,C5:C21)
I5:I15	I5	=C5-H5
H8:H15	H8	=IFERROR(H7+C8-F8,"")
C8:C15	C8	=IFERROR(VLOOKUP($B8,#REF!,2),"")
E5:E15	E5	=IF(D5="","",D5+$E$1)

Cells with Data Validation
Cell	Allow	Criteria
B5:B15	List	=SLAB

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I
1		6/24/2023		Credit Period:	30		Current Opening Balance:	500000
2							Total Overdue:	625000
3
4		Milestone	Amount	Work Date	Bill Due Date	Payment Amount	Payment Date	Allotted Payment	Balance From the bIll
5	1	B-9	125000	05-May-23	04-Jun-23			125000	0
6	2	A-10	500000	10-May-23	09-Jun-23			500000	0
7	3	B-10	375000	02-Jun-23	02-Jul-23			175000	200000
8						800000
Sheet4

Cell Formulas
Range	Formula
B1	B1	=TODAY()
H2	H2	=SUMIF(E5:E21,"<"&B1,C5:C21)
H5:H8	H5	=IF(C5<>"",MIN(F$8-SUM(H$4:H4),C5),"")
I5:I8	I5	=IF(C5<>"",C5-H5,"")
C8	C8	=IFERROR(VLOOKUP($B8,#REF!,2),"")
E5:E8	E5	=IF(D5="","",D5+$E$1)

 

[vikrampnz]

Hello Eric W

Thanks for your response. It is working for the first payment. But, for the bills and new payments below the first payment row, its not work. This is a running sheet showing multiple bills and lumpsum payments made at various points. I am attaching the minisheet for your reference.

Thanks again for your help

Test Cycle.xlsx
	A	B	C	D	E	F	G	H	I
1		26-Jun-23		Credit Period:	30		Current Opening Balance:	5,00,000
2							Total Overdue:	6,25,000
3
4		Milestone	Amount	Work Date	Bill Due Date	Payment Amount	Payment Date	Allotted Payment	Balance From the bIll
5	1	B-9	1,25,000	05-May-23	04-Jun-23			1,25,000	0
6	2	A-10	5,00,000	10-May-23	09-Jun-23			4,75,000	25,000
7	3	B-10	3,75,000	02-Jun-23	02-Jul-23			0	3,75,000
8	4	C8	0			6,00,000		0	0
9	5	A12	21,00,000					0	21,00,000
10	6	A12	0			5,00,000		0	0
11
12
BILL CYCLE

Cell Formulas
Range	Formula
B1	B1	=TODAY()
H2	H2	=SUMIF(E5:E21,"<"&B1,C5:C21)
H5:H12	H5	=IF(C5<>"",MIN(F$8-SUM(H$4:H4),C5),"")
I5:I12	I5	=IF(C5<>"",C5-H5,"")
C11:C12	C11	=IFERROR(VLOOKUP($B11,#REF!,2),"")
E5:E12	E5	=IF(D5="","",D5+$E$1)

Cells with Data Validation
Cell	Allow	Criteria
B5:B12	List	=SLAB

 

[Sanjeev1976]

Try :

Book1
	A	B	C	D	E	F	G	H	I
1		26/06/2023		Credit Period:	30		Current Opening Balance:	500,000
2							Total Overdue:	625,000
3
4		Milestone	Amount	Work Date	Bill Due Date	Payment Amount	Payment Date	Allotted Payment	Balance From the bIll
5	1	B-9	125,000	05/05/2023	04/06/2023			125,000	-
6	2	A-10	500,000	10/05/2023	09/06/2023			500,000	-
7	3	B-10	375,000	02/06/2023	02/07/2023			375,000	-
8	4	C8	-			600,000		-	-
9	5	A12	2,100,000					100,000	2,000,000
10	6	A12	-			500,000		-	-
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TODAY()
H2	H2	=SUMIF(E5:E21,"<"&B1,C5:C21)
H5:H10	H5	=IF(C5<>"",MIN(SUM($F$5:$F$10)-SUM(H$4:H4),C5),"")
I5:I10	I5	=IF(C5<>"",C5-H5,"")
E5:E10	E5	=IF(D5="","",D5+$E$1)

 

[vikrampnz]

Thanks Sanjeev1976 !! ....That worked very well !!

Thank you again !