Payment Allocation of Debtors Receipt on FIFO basis to calculate late payment interest

[1we4ty]

dear All,

I'm new here and completely stuck on a calculation of Interest on late payment received. its taking me forever manually and this will be a monthly exercise so im looking to automate it in excel.
below is a sample of this data

PARTY NAME	BILL DATE	INVOICE NUMBER	DUE DATE	BILL AMOUNT	RECD. DATE	RECEIVED AMOUNT
N ENTERPRISES	01/04/2021​	21/22/0000001	01/04/2021​	2,86,469	01/04/2021​	1,50,000
N ENTERPRISES	01/04/2021​	21/22/0000001	01/04/2021​	2,86,469	01/04/2021​	94,875
N ENTERPRISES	01/04/2021​	21/22/0000001	01/04/2021​	2,86,469	01/04/2021​	41,594
S ENTERPRISES	01/04/2021​	21/22/0000002	01/04/2021​	1,96,712	01/04/2021​	1,06,889
S ENTERPRISES	01/04/2021​	21/22/0000002	01/04/2021​	1,96,712	01/04/2021​	89,823
I & COMPANY	02/04/2021​	21/22/0000003	02/05/2021​	9,47,175	21/05/2021​	9,47,175
U ENGINEERS	02/04/2021​	21/22/0000004	02/04/2021​	68,481	02/04/2021​	68,481

Rate of interest can be say 12%

for payments beyond due date, i need to allocate the received amount and then calculate the interest on delayed/late payment.

Please help!! thank you in advance!

 

[vletm]

1we4ty
A sample Excel-file seems to missing,
which should show a sample data and ... what are You looking for?

 

[1we4ty]

Sorry Missed attached the excel. I am looking for a cell which calculates the interest on delayed/late payment.

late days are: Due date minus payment receipt date.

 

[vletm]

1we4ty
One guess ... with Your say value

 

[1we4ty]

1we4ty
One guess ... with Your say value

Thank you. i tried this but if you see the last 3 transactions - these are 3 payments against the same invoice number on different dates. So the interest calculation will only be on the amount less 1000000 for the second instant and similarly cumulative first 2 payments need to be deducted from the invoice value to give me interest value. I can do this manually but wanted to see if I can get a formula going so it take the earlier receipts against the invoice into consideration before it calculates.

 

[vletm]

1we4ty
2nd guess ...
You didn't show expected results
and
are You sure that Your given LATE DAYS are still as You would like?

 

[1we4ty]

1we4ty
2nd guess ...
You didn't show expected results
and
are You sure that Your given LATE DAYS are still as You would like?

hi sorry im unsure what you are asking.
i also have some debtors where no amount has been received so the interest will be calculated as of date. or atleast year end. and some debtors where i received less than the amount due (new entries highlighted in Yellow)

thank you

 

[vletm]

1we4ty
1st question:
What are Your expected results?
... if my sample gives something else than You've thought.

2nd question:
You sure that Your given LATE DAYS are still as You would like?

eg that red marked 96 ... is it okay, as You've given it?
... someway from previous RECD.DATE has gone two days ... hmm?

Your writings ...
#3 How possible that there are date like? Isn't that totally impossible date?

#4 some debtors where i received less than the amount due (new entries highlighted in Yellow)
>> give expected correct results for those Your marked yellows

 

[1we4ty]

1we4ty
1st question:
What are Your expected results?
... if my sample gives something else than You've thought.
I want to know my CARRYING COST - what this current delay in receiving payment is costing me. Eg. if i do it from Due date also and i would like to do it from invoice date also

2nd question:
You sure that Your given LATE DAYS are still as You would like?
View attachment 80737
eg that red marked 96 ... is it okay, as You've given it? late days are just Due date - Recd date, so 96 seems fine.
... someway from previous RECD.DATE has gone two days ... hmm? sometimes recd date is before bill date also as payment is received in advance.

Your writings ...
#3 How possible that there are date like? Isn't that totally impossible date? this is a typo - should be BLANK - as this amount is not received. the ERP is giving this erroneous date which i will get corrected - thanks for pointing it out.
View attachment 80738
#4 some debtors where i received less than the amount due (new entries highlighted in Yellow)
>> give expected correct results for those Your marked yellows here i need to make sure where no payment is recd or less than what is due is recd my "late payment" interest is accurate.

for #4 - expected result is interest to be calculated if no amount is recd based on full invoice amount till today. also for where amount recd is less than due, interest would need to be calculated on the recd amount till recd date and on balance amount till today.

 

[vletm]

1we4ty
#1,#4 As I've written: give expected results and show - how have You gotten those?
#2 ... hmm? .... of course the 1st BILL AMOUNT is something, but there are 'missing' 4'716 ... from 05/04/2021
... interesting solution or not?

 

[1we4ty]

1we4ty
#1,#4 As I've written: give expected results and show - how have You gotten those?
#2 ... hmm? .... of course the 1st BILL AMOUNT is something, but there are 'missing' 4'716 ... from 05/04/2021
... interesting solution or not?

I am unable to get the expected result. the result i want is the cost of delayed payment - scenario 1 from due date and scenario 2 from bill date. this helps me understand my true cost of giving credit to parties.

Sorry i did not get your second comment on missing items.

 

[vletm]

1we4ty
... hmm? If You cannot get expected results then - what about those results which my sample shows?

 

[1we4ty]

your result appears accurate for the initial sample - i just need to add the condition if 1. payment is not received and 2. if final payment < all payment amounts recd then for amount still due it should calculate delayed payment interest upto todays date/financial year end.