Match multiple payments to Invoices for calculating overdue charges

[rdavuluri44]

Hi, I need help to automate overdue calculation based on matching multiple payments to multiple invoices on date basis. first payment goes to oldest invoice.
hope below sheet better explains what i'm trying to achieve
thanks in advance

sample data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Loan_Date	Loan_ref	Loan_Value		Pmt_Date	Pmt_ref	Pmt_Value		Loan_Date	Loan_ref	Loan_Value	Pmt_ref	Pmt_Value	due (+120d)	Pmt_Date	delay days	fee1 @ 6% (<30d)	fee2 @ 12% (>30d)	Total fee
2	01-02-22	1	100		29-06-22	1	200		01-02-22	1	100	1	100	01-06-22	29-06-22	28	0.46	0.00	0.46
3	14-02-22	2	170		31-07-22	2	300		14-02-22	2	100	1	100	14-06-22	29-06-22	15	0.25	0.00	0.25
4	28-02-22	3	280		10-10-22	3	500		14-02-22	2	70	2	70	14-06-22	31-07-22	47	0.35	0.39	0.74
5	06-03-22	4	548		26-07-22	4	500		28-02-22	3	230	2	230	28-06-22	31-07-22	33	1.13	0.23	1.36
6	16-03-22	5	90		29-06-22	5	169		28-02-22	3	50	3	50	28-06-22	10-10-22	104	0.25	1.22	1.46
7	21-03-22	6	481				1669		06-03-22	4	450	3	450	04-07-22	10-10-22	98	2.22	10.06	12.28
8			1669						06-03-22	4	50	4	50	04-07-22	26-07-22	22	0.18	0.00	0.18
9									06-03-22	4	48	4	48	04-07-22	26-07-22	22	0.17	0.00	0.17
10									16-03-22	5	90	4	90	14-07-22	26-07-22	12	0.18	0.00	0.18
11									21-03-22	6	312	4	312	19-07-22	26-07-22	7	0.36	0.00	0.36
12									21-03-22	6	169	5	169	19-07-22	29-06-22	-20	0.00	0.00	0.00
13											1669		1669				5.54	11.89	17.44
example

Cell Formulas
Range	Formula
Q2:Q12	Q2	=IF(P2<0,0,IF(P2<30,P2,30))/365*6%*M2
R2:R12	R2	=IF(P2<30,0,(P2-30)/365*12%*M2)
S2:S12	S2	=Q2+R2
Q13:S13,M13,K13	Q13	=SUM(Q2:Q12)
G7	G7	=SUM(G2:G6)
C8	C8	=SUM(C2:C7)
N2:N12	N2	=I2+120
P2:P12	P2	=O2-N2

 

[OaklandJim]

I cannot promise to assist but I'll try. Unfortunately, the need is not clear. Please give me a more detailed explanation. Describe results and how they are calculated.