Can someone help with a forumula?

Question

Here is the scenerio:
A customer has an invoice for $10 which they can make 3 payments to pay it off in. The first payment they need to pay 33%, the second payment another 33% and the third payment is 34%. My problem is they might write me a check for $4 and I need to apply $3.30 to the first payment and $0.70 to the second payment.

The big problem is that I don't have any information I can carry from one payment to the next, so I need a forumula that cannot leave the line to work.

The only info I have is what percent of the total payment makes up this payment and what percentage of the total payment has been used so far.

In this example:
Payment 1 is 33% of the total ($3.30) and 0% has been used so far.
Payment 2 is also 33% of the total ($3.30) and 33% is ahead of it ($3.30)
Payment 3 is 34% of the total ($3.40) and 66% is ahead it it (6.60).

Is there a forumula that will work on all three lines that will show me $3.30 for Payment 1, $0.70 for Payment 2, and $0 for Payment 3.

I was looking for more of a mathmatical formula so I could run it in a sql query. It would not have any knowledge about what has happened before.

Answer 1

First let's just think about this.
You take in $4.00, and you need to apply this to the first payment first, second payment next, and whatever is left over is applied to the third payment. The first payment is 33% of $10.00, so that is $3.30, leaving 0.70 left from the $4.00. You apply all of that to the second payment, and there is nothing left over for the third payment.

There is not a "formula" for this, it is an algorithm, in other words, a process.
FirstPay=NewPay*.33;
NewPay=NewPay-FirstPay;
if (NewPay < FirstPay) then {SecondPay=NewPay;}
else
{SecondPay = FirstPay;
NewPay=NewPay-FirstPay;}
if (NewPay > 0) then {ThirdPay=NewPay;}
else NewPay=0;