Forum Discussion
Help with Excel Formula to calculate Constuction Performance Bond
I need to formula to calc a performance Bond Cell1 - Total Amount to Bond $12 Per 1000 for first 100K $9 Per 1000 for next 400K $8 Per 1000 for next 200K $6 Per 1000 for next 200K Thanks ...
What happens after 900K? Maybe (assuming value in A1):
=A1*0.006 + MIN(A1*0.002, 1400) + MIN(A1*0.001, 500) + MIN(A1*0.003, 300)
basically this says you apply $6/1000 for all values and an additional $2/1000 up to 700K (i.e. if that extra reached 1400 then stop taking any more), and then an extra $1/1000 up to 500K, and another $3/1000 up to 100K.
=A1*0.006 + MIN(A1*0.002, 1400) + MIN(A1*0.001, 500) + MIN(A1*0.003, 300)
basically this says you apply $6/1000 for all values and an additional $2/1000 up to 700K (i.e. if that extra reached 1400 then stop taking any more), and then an extra $1/1000 up to 500K, and another $3/1000 up to 100K.