Advanced Payment Repayment schedule

[singcbl]

In my line of work we use to make an advance payment to our contractor and then set a repayment schedule based on a certain percentage (20%) less off from the amount of work completed each month will be deducted until the full amount of the advanced payment is recovered.
For example, the Contract amount is $10,000 the Advanced Payment amount is 10% which is $1000
Repayment method is 30% less off from each month of the work done.
On the start of the month 10% is paid = $1000 as the advanced payment.
At the end of the month if the Contractor complete 30% ($3000) of the work he will be paid $2100 ($3000 less $900 (which is 30% of $3000)). At the end of the second month if the Contractor complete 40% ($4000) of the work he will be paid $3900 as by then the balance of the repayment is only $100. The following month there will no more any deduction of advanced payment is the full amount is already recovered.
The above is a simple example and what I need help is a formula to apply to calculate the repayment that will ensure not to exceed the Advance Payment amount.

Thank you

 

[Tubal]

Singclb,

Try out this example. Here are the cell contents starting in A1

RePay Pct	0.3	
Pre Pay Pct	0.1	
Contract Amt	10000	
Pre Pay Amt	=B3*B2	
Monthly Values	Month 1	Month 2
Month x Earn	3000	1000
Month x RePay	=B1*B6	=MIN(B1*C6,B8)
Pre Pay Balance	=B4-B7	=B8-C7
Month x Net	=B6-B7	=C6-C7

I think your key is in C7 =MIN(B1*C6,B8). Apply proper cell reference

Tubal