Forum Discussion

StevenRK's avatar
StevenRK
Copper Contributor
Jul 09, 2023

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

Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 09, 2023
    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.

Resources