Calculating a graduated construction bond

Good Morning

I need to prepare a spreadsheet that will calculate the cost of a construction bond. If cell B1 is the value of the construction costs which will be user input. Here are the levels of the bonding rates.

The first $100,000 is $12.50 per $1,000.00

$100,000.01 - $500,000.00 is $10.00 per $1,000.00


$500,000.01 - $2,500,000.00 is $8.75 per $1,000.00


$2,500,000.01 - $5,000,000.00 is $7.50 per $1,000.00


$5,000,000.01 - $7,500,000.00 is $6.25 per $1,000.00


$7,500,000.01 > is $5.63 per $1,000.00

Here is what I started with, but its not complete

=IF(G$4<=7500000,SUMPRODUCT(--(G$4>{0,500000,2500000}),G$4-{0,500000,2500000},{0.0125,-0.0017,-0.0036}),"To Be Determined")

So in this spreadsheet where do I input the Construction Value? The key is that anyone input there construction value and this will calculate the Construction Bond Cost

Got it! Thank you for your help!