Formula to calculate percentage change at percentage complete.

[Michael206]

I have a project I am working on where the retention changes from 10% to 5% at 50% complete.
Example:
Project value: $120,000.00
Value at 50% complete = $60,000
up to 50% complete I need to withhold 10% ($6000)
after 50% (51% or more) I need to withhold 5% until 100% complete (additional $3000)

Question
To date I have paid $40000.00 and retained 10% or ($4000)
I have another payment due or $25,000 and will retain $10,000 @ 10% ($1000) and $15,000 @ 5% ($750)
What formula can I use to make this work?

Thanks,

 

[sijpie]

why are you retaining 10k at 10% and 15k at 5%? I would have thought that you have paid 40k. 60k is the 50% retention, so if you have to pay an additional 25k thet will get you to 65k total (40+25k). Then if i understand you correctly you would retain 10% up to the 60k (6k) and 5% over 60k (5% of 5k=250).