Performance Bond

[BB Builder]

Hello all,

I am looking for a way to create a formula to calculate the information below within excel so that it is a table that can be used in the future by just plugging in variables. I believe this would be a tiered formula. This is for a performance bond regarding construction work.

The bid amount for the project is $3,000,000 and the first 100K is $10 per thousand making the calculation $1K and dropping the bid to $2.9M. The next 400K is $9 per thousand which is $3,600 and drops the bid to $2.5M. And so on and so forth until there is a remainder of $500k to the bid amount which goes with row 4 and $6 per thousand. The bond total in the end is $22,200.

Per Thousand
1 First $100,000.00 $10.00
2 Next $400,000.00 $9.00
3 Next $2,000,000.00 $7.30
4 Next $2,500,000.00 $6.00
5 Next $2,500,000.00 $5.45
5 Next $2,500,000.00 $5.00
6 Next $10,000,000.00 $4.50

[Glenn Kennedy]

Set it up as a small reference table and use:

=SUMPRODUCT((J3>F3:F9)*(J3-F3:F9)*(G3:G9-N(+G2:G8)))/1000

see file.

[Glenn Kennedy]

But if you're copying the formula down, use:

=SUMPRODUCT((J3>$F$3:$F$9)*(J3-$F$3:$F$9)*($G$3:$G$9-N(+$G$2:$G$8)))/1000

[DavePatton]

Similar to Glenn Kennedy's post

With Excel 365 the formula can be =SUM((B1>E4:E10)*(B1-E4:E10)*H4:H10)
Adjust the ranges as necessary for the brackets and rate differentials.

Another thought is to build a Lambda function

I named the function Bond with a parameter Bid

=LAMBDA(Bid,LET(b,{0;100000;500000;2500000;5000000;7500000;10000000},r,{0.01;-0.001;-0.0017;-0.0013;-0.00055;-0.00045;-0.0005},SUM((Bid>b)*(Bid-b)*r)))

to use the function with the Bid Amount in B1 enter =Bond(B1)