Construction Progress Billing Template: Invoice by Percentage Complete

You just finished framing the second floor of a $450,000 custom home build. Your subcontractor submits an invoice for $28,000, claiming they're "80% complete" with the framing package. But when you walk the site, you see missing headers, incomplete sheathing, and zero progress on the covered porch that was included in their $35,000 bid.

This scenario burns contractors every month. Without a systematic way to track and bill construction progress, you either overpay subcontractors for incomplete work or underpay yourself on your own invoices to clients. A construction progress billing template in Excel solves both problems by tying payments to measurable completion percentages.

The Real Cost of Informal Progress Billing

Most small contractors handle progress billing through gut feel and round numbers. "Looks like you're about halfway done, so here's half the money." This approach costs money in three specific ways:

Cash flow timing errors: If you bill a client for 70% completion but actually completed 85%, you've given them a $15,000+ interest-free loan on a $100,000 project. Over 12 months with six similar projects, that's $90,000 in delayed revenue.

Subcontractor overpayments: When your electrician claims 90% completion but has only pulled wire without making connections or installing fixtures, you might overpay by $8,000-$12,000 on a $40,000 electrical package. Those overpayments never get recovered.

Client disputes: Clients who receive bills for work they can't verify will withhold payment. "You invoiced us for completed drywall, but I see bare studs in three bedrooms." These disputes delay project completion and damage relationships.

Setting Up Measurable Completion Milestones

Effective progress billing starts with breaking each trade into measurable milestones before work begins. Instead of tracking "plumbing" as one line item, create specific checkpoints:

These milestones prevent the common mistake of paying for "completed rough plumbing" when pipes are run but not tested. Each milestone connects to a measurable deliverable.

Excel Formula Structure for Progress Tracking

The core formula for progress billing calculates the percentage complete based on finished milestones:

=SUMIF(D4:D10,"Complete",C4:C10)/SUM(C4:C10)

Where column C contains the percentage weight for each milestone and column D shows completion status. This formula adds up the percentage weights for all completed milestones and divides by the total possible percentage.

For a plumbing example with the milestones above:

The plumber can legitimately bill for 55% of their contract amount, not the "75% complete" they might claim based on time spent.

Calculating Billable Amounts with Retainage

Most commercial and many residential contracts include retainage (typically 5-10% held until project completion). Your billing formula needs to account for this:

=B15B16(1-B17)-B18

Where:

• B15 = Contract amount ($45,000)
• B16 = Completion percentage (55%)
• B17 = Retainage percentage (10%)
• B18 = Previous payments received ($18,000)

This calculates: $45,000 × 55% × (1 - 10%) - $18,000 = $22,275 - $18,000 = $4,275 due this billing cycle.

Without the retainage calculation, you'd bill $24,750 and create a $2,475 overpayment that must be corrected later.

Managing Multiple Trades on One Project

Real projects involve multiple trades running simultaneously. Your progress billing template needs to track each trade separately while rolling up to overall project completion.

Use this formula to weight each trade's completion by their contract value:

=SUMPRODUCT(F4:F8,G4:G8)/SUM(F4:F8)

Where column F contains contract amounts and column G contains completion percentages:

Total project completion: ($35,000 + $48,750 + $8,400 + $12,100) ÷ $150,000 = 69.5%

This prevents the mistake of averaging completion percentages (65%) when higher-value trades are further along.

Handling Change Orders in Progress Billing

Change orders complicate progress billing because they alter both the total contract value and completion percentages. Build change order tracking into your template from the start.

Original contract formula: =B4*B5 (Base contract × completion %)

With change orders: =(B4+B6)*B5-B7 Where B6 contains approved change order amounts.

Example scenario: Original electrical contract $28,000, change order for additional outlets $3,500, 60% complete.

Correct billing: ($28,000 + $3,500) × 60% = $18,900

Wrong calculation using original contract: $28,000 × 60% = $16,800

The $2,100 difference matters for cash flow, especially across multiple trades with change orders.

Red Flags to Build into Your Template

Smart progress billing templates include automatic alerts for suspicious patterns. Add conditional formatting or formulas to flag:

Completion percentage jumps: =IF(G4-G3>0.3,"FLAG","OK")

Flags any trade claiming more than 30% completion in one billing cycle.

High completion, low visible progress: =IF(AND(G4>0.8,H4="No"),"REVIEW","OK")

Where column H tracks whether recent site visits confirm the reported progress.

Front-loaded billing: =IF(G4>0.5*I4,"CAUTION","OK")

Where column I contains the expected completion percentage based on project timeline.

These flags don't prevent payment, but they trigger verification before processing invoices.

Documentation That Supports Billing

Progress billing disputes often come down to documentation. Your Excel template should reference supporting evidence for each milestone completion:

• Photo documentation (link to cloud storage folder)
• Inspection results (pass/fail with inspector name)
• Material delivery confirmations (for material-heavy milestones)
• Subcontractor sign-offs (for multi-step processes)

Use Excel's hyperlink function to connect completion checkmarks to evidence:

=HYPERLINK("file:///path/to/photos/framing_complete_2024-04-13.jpg","COMPLETE")

This creates a clickable link from your billing template to photographic proof of completion.

Monthly Reconciliation Process

Run a monthly reconciliation to catch discrepancies before they compound. Compare your completion percentages to actual project spending and timeline progress.

Budget comparison formula: =B4*B5/B6

Where B4 is original budget, B5 is completion percentage, and B6 is actual spending to date.

If this ratio significantly exceeds 1.0, you're either over budget or reporting inflated completion percentages. A ratio below 0.8 suggests under-billing or ahead-of-budget performance.

Integration with Project Management

Your progress billing template works best when connected to your overall project schedule. Use Excel's conditional logic to prevent billing for work that can't realistically be complete:

=IF(TODAY()

This prevents billing for electrical work (B5) before the scheduled start date (B12), even if someone mistakenly marks it complete.

Client Communication Templates

Include standardized language in your template for client invoices. Clear descriptions prevent payment delays:

Good description: "Framing package: 75% complete. Includes all exterior walls, interior partition walls first floor, floor joists second floor. Remaining: interior walls second floor, roof trusses, sheathing."

Poor description: "Framing work: mostly done."

The detailed version lets clients verify progress and understand what they're paying for.

Conclusion

Construction progress billing doesn't have to be guesswork that costs you money every month. A structured Excel template with measurable milestones, proper retainage calculations, and built-in verification prevents overpayments to subcontractors and ensures accurate client billing.

The difference between "looks about 70% done" and systematically calculated 73% completion might seem small, but on a $200,000 project, that's $6,000 in cash flow timing that affects every subsequent decision you make.

Stop estimating completion percentages and start measuring them. Your bank account will notice the difference within 60 days.

For a complete construction progress billing system that integrates with budget tracking, change orders, and payment schedules, check out SheetCraft's Construction Budget Tracker. It includes pre-built milestone templates for 15 common trades, automatic retainage calculations, and progress billing reports that clients actually understand.