Need formula for Bid Bond Calculation

I need a formula to calculate a bid bond. Example rates are as follows:

$0-$500,000 = $10.00 per thousand
$500,001-$2,500,000 = $8.00 per thousand
$2,500,001 - $5,000,000 = $6.00 per thousand
$5,00,001 - $7,500,000 = $5.00 per thousand
$7,500,001 - above - $4.00 per thousand

I know this is some sort of if then statement...but having trouble getting the formula correct.

Thank you in advance.

If I understand correctly, I would make this a lookup problem:

1) First, a lookup table, sorted in descending order by bid bond

Please Login or Register  to view this content.

2) Then use MATCH() to find the correct entry MATCH(E1,$A$2:$A$7,-1) followed by INDEX() to return the corresponding rate =INDEX($B$2:$B$7,MATCH(...))

Did I understand correctly? Will something like that work for you?

It appears that I misunderstood. I'm also not sure I understand the new example. Why is the final result 4000 (8*500) and not 4500 (8*500+5*100)?

Here's what I did in your sample file:

1) D5 needs to be the smaller value of 0.5E6 or the total bid, so I enter =MIN(D3,B5) into D5
2) D6 (and below) need to keep a running total of the values above, subtract from the initial value in D3, then repeat the same minimum test as D5, while also not going below 0.
2a) D$3-SUM(D$5:D5) will keep track of the remaining amount as it goes down the column. Note the mix of relative and absolute references.
2b) MIN(D$3-SUM(D$5:D5),B5) will return the smaller of the two values.
2c) MAX(MIN(...),0) will prevent the value from going below 0. Enter that formula into D5 and copy/paste/fill into D5:D8.

If the other calculations are correct, that should give you the final bond cost in F9. To check, I get:
bid amount -- bond cost
6.3E6 -- 30450
6.7E6 -- 32050
3.3E6 -- 17600
4.6E6 -- 23450

Does that look right?