Renovation cost overrun percentage calculator: the formula and a worked example

The overrun percentage is how far the forecast final cost sits above the budget, divided by the budget. On an $18,000 budget with a $20,160 forecast that is $2,160, or 12.0%.

General information only, not financial, construction or legal advice. Contingency, holdback and payment-split percentages are your own inputs, and payment and deposit rules vary by contract and by state or province; check yours. Figures cited from other sources are dated where they appear.

The formula

Renovation cost overrun percentage = (forecast - budget) / budget. You divide by the budget, the number you set before the work started, not by the forecast. A positive result is an overrun and a negative one is a saving. On an $18,000 budget with a $20,160 forecast, the overrun is $2,160 and the overrun percentage is 12.0%. Percentages in this guide are rounded to one decimal.

A four-cell calculator

B1  Budget                18000
B2  Forecast              20160
B3  Over/(under) $        =B2-B1                          2,160
B4  Overrun %             =IF(B1=0,0,B3/B1)               12.0%   (format as 0.0%)

Check it backwards: the forecast that goes with a given overrun is =B1*(1+12%), which is $18,000 x 1.12 = $20,160.

Line by line, then the project

The project percentage is built from the project's dollars, not from the line percentages. Here are five lines that add up to the same $18,000 and $20,160.

With headers in row 4 (A Line, B Budget, C Forecast, D Over/(under), E Overrun %) and the lines in rows 5 to 9:

D5   Over/(under) $     =C5-B5
E5   Overrun %          =IF(B5=0,0,D5/B5)

B10  Project budget     =SUM(B5:B9)                       18,000
C10  Project forecast   =SUM(C5:C9)                       20,160
D10  Project over/under =C10-B10                          2,160
E10  Project overrun %  =IF(B10=0,0,D10/B10)              12.0%

Gross overruns $        =SUMIFS(D5:D9,D5:D9,">0")         2,360
Gross overrun %         =IF(B10=0,0,SUMIFS(D5:D9,D5:D9,">0")/B10)   13.1%
Lines over budget       =COUNTIFS(D5:D9,">0")             3

Four ways to get the wrong percentage

1. Dividing by the forecast. $2,160 / $20,160 = 10.7%, not 12.0%. The denominator grows with the overrun, so the percentage shrinks. That 10.7% is a real number, the share of the final cost that was not budgeted, but it is not the overrun percentage. If you report it, label it.
2. Averaging the line percentages. (15.0% - 5.0% + 12.0% + 0.0% + 36.7%) / 5 = 11.7%, which is not the project's 12.0%. The $3,000 electrical line counts as much as the $6,000 cabinet line in an average. Always sum dollars first.
3. Netting savings without saying so. The flooring saving of $200 offsets other lines in the 12.0% figure. If you add only the lines that are over, the overruns are $900 + $360 + $1,100 = $2,360, which is 13.1% of the budget. Neither is wrong, but say which one you mean. The gross version is the one that fits a contingency pool defined as the sum of amounts committed above each line's own Budget.
4. Forecasting open lines at budget. A line with no quote has no overrun yet. In a tracker where Forecast is the committed amount when there is one and the budget otherwise, the percentage reflects only the quotes you have, and it can move either way as the rest arrive. Read it next to a count of lines still not committed.

Comparing it with a threshold and a pool

A threshold such as 10% is your input. At 12.0% this project is above a 10% line, and it is the three over-budget lines, led by electrical at 36.7%, that explain why. As a second example, suppose you had typed a 10% contingency, which makes the pool 10% of $18,000 = $1,800. The $2,360 of gross overruns would exceed it by $560. Those inputs are placeholders; use your own. The contingency tracker guide walks through the pool.

Change orders use a different base. Their percentage is approved change orders divided by the original contract, not by the whole budget, and the change order tracker guide covers it. For a room-level version of the same test, see the budget by room guide.

Turning a threshold into dollars

A percentage threshold is easier to act on as a dollar ceiling: =B1*(1+threshold). With a 10% threshold, $18,000 x 1.10 = $19,800, so the $20,160 forecast is $360 above that ceiling. Show both the percentage and the ceiling next to each other, because people tend to remember the dollars.

When to recalculate

Forecast moves whenever a quote is accepted and whenever a change order is approved, so recalculate at those two moments rather than only when an invoice arrives. A proposed change order that is not approved yet is not in Forecast; keep its amount as a separate pending figure so it does not surprise you later.

Sign conventions

If your sheet shows Variance as Budget minus Forecast, an overrun is negative: -$2,160 for the project. The overrun percentage is the same dollars with the sign flipped, divided by the budget: =-Variance/Budget. Pick one convention for the whole file and label the column header with it.

What this does not do

This is a planning tool, not financial, construction or legal advice. The calculator measures the gap between two numbers you enter. It does not predict the final cost, and a forecast is only as good as the quotes behind it. It sets no limit on what counts as too much, so the threshold, the contingency percentage and any holdback are your inputs. It does not deal with taxes. The Hexloom Renovation Budget Tracker computes Forecast and Variance on every line and rolls them up, in one Excel file.

More renovation budget guides

• Renovation contingency tracker: how much contingency is left
• Renovation change order tracker: a spreadsheet for homeowners
• Renovation budget spreadsheet: track committed vs paid
• Renovation budget by room spreadsheet: SUMIFS per room, with an overrun flag
• Kitchen remodel budget spreadsheet in Excel, built line by line
• DIY renovation expense tracker spreadsheet: receipts by room and category
• Contractor payment schedule template for homeowners in Excel
• Contractor bid comparison spreadsheet for homeowners
• Bathroom remodel budget spreadsheet in Excel: how much is really left