Allocating payment across a series of invoices

Hello,

Based upon the attached, I need an excel formula which will allocate the £3,560 payment amongst the listed invoices, starting from the earliest dated invoice. I anticipate that there will be a part-payment on one invoice.

Thanks for your help.

Hi Tuph,

Thank you, your solution is an excellent step forward. However, I have hit a stumbling block if the list is amended to include a Credit Note.

If a the list contains a Credit Note after the initial payment has been allocated, it will recognise the Credit Note (which is great as I would want to recognise the Credit Note straight away), and will allocate the payment against any invoices after the Credit Note in order to make up the payment differential.

However, the issue is that it will not allocate the payment against invoices immediately before the credit note up to the point where the initial payment was allocated.

I have amended the sample to show this.

For example, an initial payment of £5,200 has been allocated against invoices 1001,1002,1003,1004 and 1005. Invoice 1005 has a left-over balance of £200.

The Credit Note 1007 is for -£1,200 and has been recognised as part of the payment, so Invoices 1008 and 1009 have been allocated as paid in order to make up the -£1,200 credit.

The problem is....

(1) Invoice 1005 still has a residual balance of £200 (the payment needs to be allocated against this before Invoices 1008 and 1009.

(2) Invoice 1006 has no allocation against it (similarly, payment needs to be allocated against this before Invoices 1008 and 1009, but after Invoice 1005.

Thanks for your help with this. Any pointers will be a massive help.

How do you allocate Payment Dates to invoices based on the allocation done. This is so as to compute the time it has taken to pay.