I need to write a formula that will calculate the cost of a construction performance and payment bond. The bond is calculated as a percentage of the contract amount. Let's just say the contract amount is in cell A1 on the spreadsheet. The cost of the bond will be 2.5% of the first $100,000 of contract, 1.5% on the next $500,000 of the contract, and then 1.0% on the next 2,000,000 and so on. I have put the table below that references every percentage we would use. Remember that the formula needs to work even if the contract amount is as small as $1 or as large as millions of dollars. I am terrible at these types of formulas and could use some help. Thank you for your assistance!
BOND RATE BOND AMOUNT
2.50% Up to 100,000.00
1.50% On the Next 400,000.00
1.00% On the Next 2,000,000.00
0.75% On the Next 2,500,000.00
0.70% On the Next 2,500,000.00
0.65% Amount Over 7,500,000.00
Bookmarks