Construction Performance Bond Premium Calculator in Excel: Tiers, Change Orders and Cash Flow

A general contractor is bidding a municipal library renovation. The estimate closes at $2,375,000 with overhead and fee, and the last line is the bond. The estimator types 2 percent, the number the company has used since its first public job, and the bid goes out at $2,422,500. A construction performance bond premium calculator in Excel that reads the surety's tiered schedule puts the bond at $27,525 and the bid at $2,402,525. If the next bidder comes in at $2,410,000, the first bid loses by $12,500 and the second one wins by $7,475, on a line that says nothing about how well the company builds.

The 2 percent came from somewhere. On a $150,000 job the tiered premium is $3,250, which is 2.17 percent of the contract. Contract bond premiums work like tax brackets: a rate per $1,000 of contract price that drops as the contract gets bigger, so any flat percentage is right at one contract size and wrong at every other. This article builds the rate table, solves the premium on a bid that contains its own premium, prices the bond on change orders at the rate the surety actually bills, and moves the premium to a line that gets paid in the first pay application.

How a surety prices a performance bond

A surety quotes contract bonds as a dollar amount per $1,000 of contract price, written $/M in surety paperwork. The rate depends on the class of work (a supply-only contract is the lowest risk, Class B covers harder trades), on the schedule the surety has filed with the state insurance department, and on the credit or debit the underwriter applies to your company. Old Republic Surety published a worked example in Surety Bond Quarterly, the magazine of the National Association of Surety Bond Producers, in 2022: a one-year, $1 million Class B electrical contract priced at $25/M on the first $100,000, $15/M on the next $400,000 and $10/M on the next $500,000, for $13,500 in total. The same article prices a flat-rate alternative at $30/M and states that every rate in it is an example.

On federal work the bond is mandatory. FAR 28.102-1(a) requires performance and payment bonds on any construction contract above $150,000, and the clause at FAR 52.228-15(b) sets the penal sum of each at 100 percent of the original contract price.

This article uses the 25/15/10 tiers from the Old Republic example, runs the $10/M tier up to $2.5 million as several surety brokers' published examples do, and assumes $7.50/M above that. Replace all four rates with the schedule your agent sends you before you bid anything.

Here is what a flat 1.5 percent does across contract sizes on that schedule.

The flat rate eats $1,000 of margin on small jobs and adds $27,750 of air to a $5 million bid. A company that set its percentage on $150,000 jobs and now bids $2.4 million work carries $8,500 of extra bond on every bid at 1.5 percent, and $20,500 at 2 percent.

The premium in one cell

Put the schedule on a sheet called Rates: tier floors in A5:A8, rates per $1,000 in B5:B8, and in C5:C8 the change in rate from one tier to the next. C5 is =B5 and C6 is =B6-B5, copied down to C8. On the bid sheet, the contract price sits in C4 and the premium in C5:

=SUMPRODUCT((C4>Rates!A5:A8)*(C4-Rates!A5:A8)*Rates!C5:C8)/1000

For every tier floor the contract passes, the formula adds the dollars above that floor times the change in rate at that floor. On a $2,400,000 contract it charges $60,000 for the whole amount at $25/M, takes back $23,000 for the $2,300,000 above $100,000 at $10/M, and takes back $9,500 for the $1,900,000 above $500,000 at $5/M. That leaves $27,500. Nothing about the formula changes when your surety's schedule has six tiers instead of four: extend the three ranges and it still works.

Two more cells do most of the work later in this article. The rate the surety will charge on the next dollar of contract value is =LOOKUP(C4,Rates!A5:A8,Rates!B5:B8), and the effective rate on the whole contract is =C5/C4. The first one prices change orders. The second one, $11.46/M on a $2,400,000 contract, belongs in job cost reports and nowhere else.

The bid that contains its own premium

The premium is charged on the contract price, and the contract price includes the premium. Point C4 at the bid total and Excel throws a circular reference warning. Turning on iterative calculation makes the warning go away for the whole workbook, including the next circular reference somebody creates by accident, so leave it off and solve the loop in three helper cells.

Put the bid before bond, $2,375,000, in C3. In D10, compute the premium on C3. In D11, compute it on =C3+D10. In D12, compute it on =C3+D11. Each cell is the same SUMPRODUCT pointed at a different price. Each pass cuts the error by a factor equal to the marginal rate, here 1 percent: the first pass misses by $275.25, the second by $2.75, the third by $0.03. D12 is the bond line, $27,525, and the bid is $2,402,525.

If you prefer one cell and you know which tier the bid lands in, the exact answer for the $500,000 to $2,500,000 tier is =(8500+10*(C3-500000)/1000)/(1-10/1000), where 8,500 is the premium at the tier floor. It breaks silently if the bid crosses into the next tier, which the three-cell version does not.

The 2 percent version adds $19,975 to the bid, 0.83 percent of it. On a low-bid public job, that is enough for the bond line alone to decide the award. The 1 percent version wins and then takes $3,737.50 straight out of fee.

Before any of this, check that the job fits your bonding line at all. The construction bonding capacity calculator tests a bid against your single-job and aggregate limits, and a surety that declines the bond makes the premium question moot.

Change orders: bill the marginal rate, track the true-up

The premium is not settled at award. When the contract price grows, the owner wants more bond. FAR 52.228-15(b)(3) lets the government require additional performance and payment bond protection when the contract price increases, generally 100 percent of the increase, either by raising the penal sum of the existing bond or by a new bond. The surety bills additional premium on overruns and returns premium on deductive changes. In the Old Republic example, the change orders are priced at the last tier rate, $10/M on a $1 million contract, because they are the top dollars of the job.

That makes the effective rate the wrong number for change orders. On this job the effective rate at award is $11.46/M, while every change order dollar up to $2,500,000 costs $10/M and every dollar above it costs $7.50/M. Here is the job's change order log, with revised contract price in column E (=E7+C8), premium on it in column F (the same SUMPRODUCT), and the additional premium in column G (=F8-F7).

CO 2 straddles the $2,500,000 floor: $32,675 of it is priced at $10/M and $85,725 at $7.50/M. A per-CO percentage cannot see that, and the column G subtraction handles it without any extra logic.

Net change orders total $309,600 and the surety's additional premium is $2,565.69. Priced at the award's effective rate, the bond line on those change orders would come to $3,547.03. Priced at a flat 1.5 percent, it comes to $4,644.00, which is $2,078.31 more than the surety will charge. On a cost-plus or open-book contract an owner's auditor will find that difference. Leave the line out entirely and the company pays $2,565.69 when the surety bills the final contract price. The change order markup calculator already carries a bond and insurance line: feed it the LOOKUP cell, 1.0 percent or 0.75 percent on this job, and keep the bond rate out of the insurance percentage.

Send the surety the final contract price at closeout and reconcile its invoice against column F. If the job ran long, also ask whether a time surcharge applies. Old Republic notes that some filings add a charge when the contract runs past one or two years, depending on the filing, and that others do not charge for the second year.

Get the premium back in pay application one

The premium is paid when the bond is issued, and the bond is issued before work starts: FAR 28.102-1(c) requires the contractor to furnish all bonds before receiving a notice to proceed or being allowed to start work. If the bond is buried in overhead and spread across the schedule of values, the company finances its own premium for the whole job and then waits for retainage.

Federal work gives you a better option in writing. Under FAR 52.232-5(g), the government reimburses bond premiums with progress payments on request, once you show evidence of full payment to the surety, and the retainage provisions do not apply to the part of the payment that covers bond premiums. On private work, ask for a separate bond line in the schedule of values and bill it 100 percent complete in the first application with the surety's paid invoice attached. Some owners will still hold retainage on it.

The model assumes a 12-month job, payment 30 days after each bill and retainage released in month 14. The difference between the first and last rows is $1,269.60, small on its own and about half of everything the four change orders added to the premium. The schedule of values template article covers how to set up the line items an owner will accept.

What the bond sheet needs before bid day

• The surety's filed schedule for your class of work, tier by tier, plus your credit or debit as a separate cell, so the next estimator cannot type a percentage over it.
• The three-pass premium solve on the bid total, with iterative calculation turned off.
• A marginal rate cell that the change order sheet reads instead of a hard-coded percentage.
• A change order log that recomputes the premium on the revised contract and posts the difference, positive or negative.
• The contract duration, with the surety's rule for time surcharges written next to it.
• A warranty or maintenance period beyond one year, if the spec calls for it. In the Old Republic example, the first year is included in the performance bond and the second year is billed on a separate, much lower maintenance schedule.
• A separate schedule of values line for bonds, and the surety's paid invoice filed with pay application one.

What to change before the next bid

Get your surety's filed schedule and your credit in writing this week, replace the percentage in your estimate with the SUMPRODUCT cell, and solve the bid total in three passes. On the library job that change alone moves the bid from $2,422,500 to $2,402,525. Price every change order's bond line at the marginal rate and put the bond on its own line in pay application one.

The premium then has to be tracked through the job like any other cost. The Construction Budget Tracker holds the bond as its own budget category, logs each change order with its effect on the budget, records the surety's invoices next to the other payments, and shows budget against actual and the projected final cost as the change orders come in. It does not hold a surety rate table: build the tier sheet above for the bid, then carry the award premium into the tracker as the bond budget and log each additional premium against it.