Applying Payments in a Certain Order

[Captain825]

I'm working on a simple spreadsheet that tracks dues and payments. Everything is done an works great except the following:

When a payment comes in, I need to apply that payment in a certain order. First its applied to interest, then to late fee and lastly to the dues. Can anyone help me with this formula?

Thank you in advance.

 

[onlyadrafter]

Hello,

can you supply further information,

cell locations, an example or before and after payment has been received and allocated.

 

[Captain825]

Sure. Here is what I have...

M8 = Payment Date
O8 = Check #
P8 = Amount of Payment


O23 = Total for Interest
O24 = Total for Late Fees

P23 = Amount applied to interest
P24 = Amount applied to late fees

Q23 = Balance of interest after payment is applied
Q24 = Balance of late fees after payment is applied

So...assuming there is a balance for interest (O23) of $123.98 and a balance for late fees (O24) of $150.00.

A payment comes (P8)in for $200.00. I am looking for either a formula or maybe a loop to take the $200 payment and apply $123.98 to interest leaving a zero balance for interest (Q23) and then apply the remaining portion of the payment $76.02 to late fees leaving a balance for late fees (Q24) of $73.98.

Thank you in advance for any help.

 

[onlyadrafter]

Hello,

this gives the answers you expect in the example you gave.

In Q23 enter

=IF(P8>=O23,0,O23-P8)

in Q24 enter

=IF(P8>=O23,O24-(P8-O23),O24)

I'm sure there will be further questions, so feel free to ask.

 

[Captain825]

You are a genius!!! Thank you so much. I took your formula and tweaked it a bit so that payments can be further allocated to other charges in the correct order and it works great. Can't thank you enough.

All the best.