New Construction Home Builder Pro Forma: Cost Per SF and Margin Before You Break Ground

Most spec builders learn what their margin actually was two months after the keys hand over. They got the construction loan, hired the framer, fought the inspector, and sold the house. The accountant cleans up the numbers in March and tells them they cleared 6% on a job they pitched their banker at 22%. By then the lot for the next build is already under contract at a price set by the same flawed thinking.

A new construction home builder pro forma is the document that prevents this. It is a single spreadsheet that models the entire project, from lot acquisition through closing, before a single permit is pulled. Done right, it tells you the maximum lot price you can pay, the cost per square foot you need to hit, the carry months you can survive, and the gross margin the project will deliver if every assumption holds. Done wrong, it is a feel-good exhibit you show your wife when you announce you bought another lot.

This article walks through how to build a new construction home builder pro forma in Excel from scratch, using real numbers a small builder would see on a 2,400 square foot spec home in a typical American suburb.

Why Cost Per Square Foot Lies to You

Ask any production builder what their hard cost is and they will quote a number per square foot. $165. $210. $245. The number is useful as a screening shortcut and dangerous as a budgeting tool. Cost per square foot is the average of a dozen line items moving in different directions, and the average hides every line where you are about to lose money.

Consider two identical 2,400 SF homes built six months apart in the same subdivision. Same plan, same builder, same crews:

House A pencils at $175 per SF. House B pencils at $194 per SF. The builder who quoted $180 as their "we always come in around there" number lost $34,800 on House B. The cost per square foot averaged out across the portfolio looks fine. The individual project bleeds margin.

The pro forma fix is to never use cost per square foot as an input. Use it only as an output, calculated from a detailed line item build-up. The builder who plugs $180 per SF into a single cell and multiplies by 2,400 is the builder who finds out about rough framing inflation in week 14.

The Five Buckets of a Home Builder Pro Forma

A new construction home builder pro forma has five logical sections, each tied to the next by formula references. Build them as separate cell blocks on a single sheet:

1. Acquisition: lot price, closing costs, survey, due diligence
2. Hard costs: every trade line built up individually with quantity and unit cost
3. Soft costs: permits, impact fees, architectural, engineering, financing fees, marketing
4. Carry: construction loan interest, property tax, insurance, utilities during build
5. Sale: list price, expected sale price, commissions, seller concessions, closing costs

The output block at the bottom of the sheet does the arithmetic on these five buckets and tells you gross profit, margin on cost, margin on revenue, and cash-on-cash return. Three numbers a builder should know before they sign the lot purchase agreement.

The Assumptions Block

Before any formula, build an assumptions block in rows 4 to 25. Every input lives here as a named cell. Every downstream calculation references these cells, never raw numbers. This is what lets you run "what if the lot is $15K cheaper" in 10 seconds instead of rebuilding the model.

Note B8 is the placeholder for hard cost per SF and gets replaced by an output from the detailed hard cost block below. The first pass of the model uses $175 as a sanity check. The final model calculates this from line items.

The Detailed Hard Cost Build-Up

The hard cost block lives in rows 30 to 75. One row per trade. Quantity, unit, unit cost, line total. The formulas chain together so a change in window count or square footage automatically rolls through:

Each line total uses =QuantityUnit_Cost. The subtotal at row 70 is =SUM(D30:D69). The garage line is =B5B9. Contingency is =B11*SUM(D30:D70). Total hard cost on this build pencils to roughly $150 per heated SF using =Total_Hard/B4, not the $175 the builder quoted. A below-line cost per SF on the same build sequence is a six-figure swing on a portfolio.

Soft Costs and Carry: The Items That Sink Margin

Soft costs on a new construction home are the line items that hit before the first stud goes up and after the last punchlist item gets fixed. Builders chronically underestimate them. Run them out as their own block:

• Building permit and impact fees: $4,000 to $18,000 depending on jurisdiction
• Plan review and inspection fees: $800 to $2,500
• Architectural and structural engineering: $2,500 to $9,000 if not stock plan
• Survey, perc test, environmental: $1,500 to $4,000
• Builder risk insurance: $1,200 to $3,500
• Construction loan origination and appraisal: 1.5% to 2% of loan
• Title insurance and closing on lot: 0.5% to 0.8% of lot price
• Marketing, photography, staging: $2,500 to $8,000
• Property tax during build and marketing: assessed value times mill rate prorated
• Utilities during build (temp power, water): $1,500 to $3,500

On a $97,800 lot plus $360,948 hard, soft costs at 9% land near $41,300. The carry calculation is where most builders blow it. A 7-month build plus 3-month marketing window on a $367,000 construction loan at 9% with 50% average outstanding balance produces:

=(Loan_Amount/2)(Rate/12)Total_Months

Or numerically: =(367000/2)(0.09/12)10 = $13,762

The 50% average balance assumption is fine for a screening pro forma. For the bank submission, build a draw schedule that maps to the construction sequence (foundation in month 1, framing in months 2 to 3, mechanicals in 4, finishes in 5 to 6, punch in 7) and calculate interest on the cumulative drawn balance month by month. The real-world interest on this kind of build is typically 15% to 25% higher than the average-balance shortcut suggests.

The Output Block: Margin, Max Lot Price, and the Go/No-Go

Once acquisition, hard, soft, and carry are stacked, the output block lives at the bottom of the sheet. Six cells that decide whether the project happens:

This project pencils at 10.1% margin on cost against a 20% target. That is a no-go. The builder has three levers: lot price, sale price, or cost. The pro forma tells you exactly how much each lever needs to move. To hit 20% on cost at the modeled $513,810, the sale price needs to clear $658,000 net, which means a list price of $722,000 after commissions. If the comps say $615,000, the lot price has to come down. Solve for max lot price using:

=Target_Net_Sale/(1+Target_Margin)-Hard-Soft-Carry-Closing_Costs

Plug the numbers and the answer is $54,000. Not $95,000. The seller is asking $95K. The lot does not work at the price they want. The pro forma just saved the builder from a $40,000 mistake.

Sensitivity Testing Before You Sign

One column on the sheet runs sensitivity scenarios. Hold every input constant except one, vary it across a realistic range, and watch margin move. The three variables that matter:

If your project pencils at 18% margin and a stacked bad scenario takes you to 9%, the deal still works on a bad day. If you start at 14% and stacked bad takes you to 5%, you are one framer no-show away from breakeven. The pro forma tells you which deals can absorb a hit and which cannot. Builders who skip this run on hope.

The Build-or-Walk Threshold

Every builder should set a numeric threshold that triggers a no-go before emotion gets involved. A defensible threshold for a spec build in a normal market:

• Margin on cost: 18% minimum at the base case
• Margin on cost: 10% minimum at the stacked-bad case
• Cash-on-cash: 35% minimum on equity-in
• Build months: under 9 from groundbreak to CO
• Comps within 10% of target sale price in the past 90 days

If two of these miss, walk. If three miss, walk faster. The pro forma is the document that makes that conversation about numbers, not gut feel. Builders who run pro formas walk away from 6 of 10 deals they look at. Builders who do not walk away from 1 of 10 and wonder why their portfolio averages 5% margin instead of 20%.

Build It Once, Use It Every Deal

A new construction home builder pro forma is not a one-off exercise. It is the template you run every lot, every plan, every market shift. Build it once with the structure described above, lock the formula cells, color the assumption cells yellow, and save the file as your evaluation engine. Every new lot under consideration takes 20 minutes to model. The deals that pencil get a deeper draw schedule and a real bank submission. The deals that do not get killed in pencil, where killing them is free.

If you do not want to build the structure from scratch, SheetCraft's Construction Budget Tracker is pre-built with the assumption block, hard cost line items, soft cost categories, draw schedule, and sensitivity table described in this article. It is set up to be your standing pro forma engine, so the next lot you look at gets evaluated with the same discipline as the last one, and you spend your time deciding which deals are real instead of building spreadsheets at the kitchen table.