Help with Excel Formula to calculate Constuction Performance Bond

Copper Contributor

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 much in advance !   Steve

1 Reply
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.