Allocation of payment

[sna]

Hi there

I have got a question to ask you how to track amount due.is it possible with excel formula to handle.

For example I have amount to pay $790 (principal and interest) a client is late to pay so they have a penalty of $ 3.5.
If client comes to pay $150.i need an excel formula to track.so if he/she paid $150 so penalty is 0, interest will leave and principal as well.


Tks

[sna]

here is a sample template.

Tks

[sna]

principal would be $453

Interest would be $ 6

Penalty would be zero.

I can track by simple formula minus.

Is there any formula with if and ...to solve?

Tks

[norkaz]

...

Hi Guy,

I have some questions to clarify related to your questions.

1. Where is $790 come from?
2. Does 3.5$ penalty means for every late payment?

3. Could you please help to provide before and after scenarios for this case?

I mean before is a question that needs to solve -- after means the right answers once the problem is solved.

Norkaz

[sna]

1.forget it.it partially paid in last month so it left 456+153=609 only.
2.3.5$ is penalty for late of 3 days or more...
The problem is how can we can write formula in excel to track amount of principal, interest or penalty left after payment.in this case $150 it will deduct to pay penalty first $3.5 so amount left $146.5 will pay for interest .interest decreases to 146.5$ leaving amount $6.5 so money is over without any left for principal , principal amount stay the same $453.
The procedure said amount paid from client will deduct penalty first then interest and finally, principal.

Thanks

[norkaz]

...

One point still isn't clear -- where leaving the amount of $6.5 come from?

However, try to follow up with this.

B5
=IF(C5=0,B2-(A2-C2-D2),B2)

C5
=MAX(0,IF(D5=0,C2-(A2-D2),C2))

D5
=MIN(0,A2-D2)

Norkaz

[sna]

Thank you so much, really helpful