• Hi All


    Post Spam and you Will Be Deleted as a User



Allocate payment based on FIFO Basis


New Member
Dear All,

I need a formula help on allocating payment received from Customer on First cum first basis.

For example,

Customer Value

A 100

B 125

A 110

A 285

A 300

Payment is received for A is 500.It should be allocated as below:

Customer Value Payment

A 100 100

B 125

A 110 110

A 285 285

A 300 5

Request your support please!


New Member
Interesting question. I could not come up with a quick solution with formulas alone, so VBA to the rescue.

The basic logic is simple,

1. Have two tables one for dues, one for payments.

2. For each payment, we will go thru rows in dues and see

3. if customer matches, and whether can allocate any more payment to it

4. If so, we allocate the payment and subtract it from current value

5. Repeat

See this attached file. It uses your data but to test it I have added few dozen more rows. Seems to work ok for me.


Click the blue button to allocate payments, gray one to clear them.


Hi nandhamnk,

I am not very clear about your requirenet properly. Can u plz clarify the following points?

1) Do you have the payment data list separately somewhere in your excel file?

2) How are you distributing the 500 payment amount for 4 As in your list (However the total payment is coming up 510

in your examplpe above...please clarify why!)

3) Is there any specific relation between valu and payment column?




Excel Ninja

I think we can use the following formula based approach as well.

My sample data setup is as follows. (Allocation was calculated.)

Customer	Receivable	Allocation
A	        100	         100
B	        125	         25
A	        110	         110
A	        285	         285
A	        300	         5
i.e. Customer heading is in cell A1, with data in A2 onwards

Receivable is in column B

Allocation in column C

The payment data sample I used is as follows:

Customer	Payment
A	        500
B	        25
For ease of reference in the formula, I named the payment data using Excel Names:

"PaidBy" is the data range "A"; "B" above

"Payments" is the data range "500;25" above

Allocation can be calculated as (formula shown is for cell C2):

=MIN(B2, SUMIFS(Payments, PaidBy, A2) - SUMIFS(C$1:C1, A$1:A1,A2))

It can be copied down to additional rows in column C.

Hope this helps.




Excel Ninja
Staff member
This is the same logic as discussed in: http://chandoo.org/wp/2012/10/11/formula-forensics-no-031/

Including associated links at the end of the post


New Member
Dear Sajan,

I cant understand the formula.Instead of creating name ranges can you please put formula for my knowledge .Thanks for the support.


New Member
Hello All,

I am looking for the answer to the below query which is a little bit similar to above one:

I want to adjust "Advance received from customer" into the Debtors Aging buckets of 0-30, 31-60, 61-90, 91-180, >180 days on FIFO basis.

Note: Advance received is anonymous.

Let's take an eg. as below:

Case 1:

Tenant Name 0-30 31-60 61-90 91-180 >180 Advance
A 100 80 165 -20 40 -140

Case 2:

A 100 80 165 -20 -40 -140

Case 3:

A 100 80 165 -20 -40 -20

The whole idea is to settle the advance amount starting from >180 days and ending at 0-30 days.

Kindly help with an excel based file. (Macros enabled or formula based).

Thanks in advance.