Construction Cash Flow Forecast Excel: S-Curve Draw Schedule That Lenders Approve
The day a small general contractor learns the difference between cash flow and profit is usually the day a $2.4 million project runs out of money in week 11. The job is profitable. The schedule is on track. Subs are billing on time. And the GC cannot make payroll Friday because the lender funded the last draw three weeks ago and the next one is still 18 days out. The accountant says the project shows a $312,000 gross profit. The bank account shows negative $44,000.
A construction cash flow forecast in Excel is the document that prevents this exact scenario. Done right, it tells you week by week how much money goes out, how much comes in from draws, and where the gap is. Done wrong, it is a budget renamed and printed on Tuesday because the loan officer asked for one. The lender will not catch the difference until your project is the one bleeding.
This article walks through how to build a construction cash flow forecast Excel model that an actual lender will accept. We will use a $2.4 million ground-up commercial project as the example: a 14,800 SF medical office on a 9-month schedule, 75% loan to cost, 5 monthly draws plus a final retainage release. The same structure works for a $400,000 spec home or a $40 million multifamily build.
Why Lenders Demand an S-Curve and Why Builders Hand Them a Line
Construction lenders underwrite projects in two stages. They underwrite the deal: appraised value, loan to cost, sponsor net worth, market rents. Then they underwrite the draw mechanics: how the money flows out and back. The first stage gets attention. The second is where deals fall apart on a Wednesday at noon.
The lender wants an S-curve cash flow forecast because that is how construction spending actually behaves. Month 1 is slow: site work, foundation, permits cleared. Months 3 through 6 are heavy: framing, mechanicals, exterior envelope. Months 8 and 9 taper: finishes, punch, certificate of occupancy. Plot cumulative spend against time and the curve looks like an elongated S. A linear forecast (10% per month for 10 months) hides the spike and gives the lender bad information about peak exposure.
Consider what happens when a GC submits a linear forecast on the $2.4M project. The lender approves the loan and disburses on the projection. Real spend hits 22% in month 4 because mechanical rough-ins were paid faster than expected. The GC needs a $528,000 draw. The lender's last draw was $240,000 (the linear amount). Now the GC has $288,000 of subs invoices that the project budget supports but the draw cycle does not. The subs go unpaid for three weeks. The framer pulls his crew. The schedule slips 11 working days. The carry cost on that slip alone is $14,000 in additional loan interest.
The math on a missed S-curve looks like this:
| Scenario | Cost of Friction |
|---|---|
| Sub crew pulled, schedule slips 11 days | $14,000 carry interest |
| Sub charges 2% late payment fee | $5,760 |
| Backfilling the framer with a premium crew | $18,000 |
| GC bridge loan for 3 weeks at 14% | $3,400 |
| Total cost of a bad forecast | $41,160 |
That is the cost of a spreadsheet built in 20 minutes. A real construction cash flow forecast Excel model takes two hours the first time and prevents this every time after.
The Six Buckets of a Construction Cash Flow Forecast
A cash flow forecast for a construction project is not a budget. A budget is a static document that lists categories and dollar amounts. A cash flow forecast is a time-phased schedule that maps every dollar to a week or month. Six logical blocks live on the sheet:
- Schedule of values: every cost code with budget amount and the months it spans
- Spend curve: monthly outflow per cost code, summed to total monthly spend
- Draw schedule: monthly draw amount, retainage withheld, draw timing offset
- Cumulative position: running balance of spend versus draws received
- Carry costs: interest on cumulative drawn balance, property tax, insurance
- Output dashboard: peak exposure, total carry, draw-versus-spend gap by month
Build these as horizontal blocks across columns C through O (months 1 through 12, with two months of pre-construction and one of close-out). Cost codes live in column B. The format is unforgiving: every formula must reference the assumptions block at the top of the sheet, never a hard-coded number.
The Schedule of Values Block
The schedule of values is the AIA-style document the lender will compare your draws against. It lists each cost code, the contract value, and the percent complete that triggers the draw. Build it in rows 8 to 45 with one cost code per row:
| Cost Code | Description | Budget | Months Active |
|---|---|---|---|
| 02-100 | Site work and grading | $148,000 | 1 to 2 |
| 03-300 | Foundation and slab | $172,000 | 2 to 3 |
| 05-100 | Structural steel | $215,000 | 3 to 4 |
| 06-100 | Framing and rough carpentry | $184,000 | 3 to 5 |
| 07-500 | Roofing and waterproofing | $96,000 | 4 to 5 |
| 08-100 | Doors, frames, hardware | $68,000 | 5 to 7 |
| 08-400 | Storefront and glazing | $112,000 | 5 to 6 |
| 09-200 | Drywall and plaster | $148,000 | 5 to 7 |
| 09-600 | Flooring | $84,000 | 7 to 8 |
| 09-900 | Paint and coatings | $42,000 | 7 to 8 |
| 15-400 | Plumbing | $186,000 | 3 to 7 |
| 15-700 | HVAC | $232,000 | 4 to 7 |
| 16-000 | Electrical | $248,000 | 3 to 8 |
| General conditions | Project management, supervision | $168,000 | 1 to 9 |
| Overhead and profit | GC margin | $248,000 | 1 to 9 |
| Total contract | $2,401,000 |
Note the "months active" column. This is where the S-curve starts. Site work happens early. Finishes happen late. General conditions spread across the entire project. A linear forecast ignores all of this.
Building the Spend Curve with SUMPRODUCT
The monthly spend per cost code is not a simple division. A $215,000 structural steel package spread across months 3 and 4 is not $107,500 per month. It is roughly 30% in month 3 (delivery and erection start) and 70% in month 4 (erection completion and decking). Build a weighting table to handle this:
| Cost Code | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 |
|---|---|---|---|---|---|---|---|---|---|
| 02-100 Site work | 60% | 40% | 0% | 0% | 0% | 0% | 0% | 0% | 0% |
| 03-300 Foundation | 0% | 40% | 60% | 0% | 0% | 0% | 0% | 0% | 0% |
| 05-100 Steel | 0% | 0% | 30% | 70% | 0% | 0% | 0% | 0% | 0% |
| 15-700 HVAC | 0% | 0% | 0% | 15% | 30% | 30% | 25% | 0% | 0% |
| 16-000 Electrical | 0% | 0% | 10% | 15% | 20% | 20% | 20% | 15% | 0% |
The monthly spend for each cost code is then =Budget*Weight. The total monthly spend at the bottom of each column is =SUMPRODUCT(Budgets,Weights_For_That_Month).
For example, if budgets are in D8:D24 and the month 4 weights are in H8:H24, the formula for total month 4 spend is:
=SUMPRODUCT(D8:D24,H8:H24)
Run this across all 9 months and the cumulative spend curve emerges. On this $2.4M project the curve looks like:
| Month | Monthly Spend | Cumulative | % Complete |
|---|---|---|---|
| 1 | $118,000 | $118,000 | 4.9% |
| 2 | $164,000 | $282,000 | 11.7% |
| 3 | $298,000 | $580,000 | 24.2% |
| 4 | $412,000 | $992,000 | 41.3% |
| 5 | $398,000 | $1,390,000 | 57.9% |
| 6 | $346,000 | $1,736,000 | 72.3% |
| 7 | $294,000 | $2,030,000 | 84.5% |
| 8 | $214,000 | $2,244,000 | 93.5% |
| 9 | $157,000 | $2,401,000 | 100.0% |
Plot the cumulative column against months and the S-curve is right there: slow start, steep middle, gentle close. The lender sees this and trusts the model. The lender who sees 11.1% per month for 9 months sends the file back with a request for revision.
The Draw Schedule and the Retainage Trap
Draws lag spend. The GC pays subs on the 30th, submits a draw request on the 1st, the lender processes for 5 business days, the funds hit the GC account on the 8th. That is a 9-day timing gap on every cycle. Layered on top is retainage: the lender holds 10% of every draw until substantial completion, which means a $412,000 month 4 spend yields a $370,800 draw.
Build the draw schedule with these formulas:
Draw_Amount = Spend_That_Month * (1 - Retainage_Pct)
For the month 4 example: =412000*(1-0.10) = $370,800
The retainage release happens at month 10 (post substantial completion): =SUM(All_Monthly_Spends)*Retainage_Pct = $240,100
Now layer in the timing offset. Spend hits month 4. Draw hits month 4 day 8 to 12. For cash flow purposes, the draw lands in the same month if your draw cycle is monthly and you bill on schedule. If you bill biweekly, half the draw lands in the current month and half in the next. Model whichever cycle your loan agreement specifies.
The Carry Cost Calculation That Bankers Verify
Loan interest on a construction loan accrues on the cumulative drawn balance, not the committed loan amount. This is where the linear-forecast builder loses thousands. The construction loan on this $2.4M project is $1.8M at 9.5%. The interest formula is:
Monthly_Interest = Cumulative_Drawn_Balance * (Rate / 12)
For month 4, cumulative drawn through month 3 is roughly $522,000. Interest in month 4 is =522000(0.095/12) = $4,132. By month 7, cumulative drawn climbs to $1.83M and monthly interest hits =1830000(0.095/12) = $14,488. Total carry interest over the 9-month build clocks in around $74,000.
The builder who used a 50% average balance shortcut to estimate carry would have projected =(1800000/2)(0.095/12)9 = $64,125. The S-curve model produces a number 15% higher. On a $2.4M project that is $10,000 of unaccounted carry. Repeat across four projects a year and the GC is leaving $40,000 on the table because nobody bothered to model the draw curve properly.
The Cash Gap Output and the Working Capital Decision
The output block at the bottom of the sheet tells the GC three numbers that decide whether the project is fundable:
| Output | Formula | Result |
|---|---|---|
| Peak cash gap | =MAX(Cumulative_Spend-Cumulative_Draws) | $284,000 |
| Month of peak gap | =MATCH(MAX(Gap),Gap,0) | Month 5 |
| Total project carry | =SUM(Monthly_Interest) | $74,200 |
| Working capital needed | =Peak_Gap+1_Month_GC_Payroll | $328,000 |
| Owner equity required | =Total_Cost-Loan_Amount | $601,000 |
| Total cash to close | =Equity+Working_Capital | $929,000 |
That last row is the conversation the GC needs to have with the owner before mobilization. The lender funds $1.8M. The owner equity covers $601K. But the project needs another $328K of working capital floating somewhere because the draw mechanics never close the gap to zero. If the owner shows up with $601K and no working capital line of credit, the project stalls in month 5.
This is the conversation that does not happen on most jobs until it is too late. The forecast forces it to happen on the day the loan term sheet gets signed.
Three Sensitivities Every Lender Asks About
The lender's underwriter will run three stress tests on your forecast. Build them as scenarios in columns Q through S:
- Schedule slip 30 days: shift every monthly weight one column right, add a month of general conditions and carry, recalculate peak exposure
- Cost overrun 8%: multiply every budget by 1.08, run the same spend curve, watch the peak gap widen
- Draw cycle delayed 14 days: shift the draw schedule but not the spend schedule, watch the working capital number nearly double
If your peak cash gap under the worst of these three sits below your committed working capital line, the deal is bankable. If it does not, you negotiate a larger interest reserve, a higher loan-to-cost, or a smaller draw retainage. You negotiate from a position of knowing exactly what you need. The GC who walks into the lender's office with a linear forecast negotiates from the position of hoping nobody asks hard questions.
Build It Once, Reuse It Forever
A construction cash flow forecast Excel model that handles a 9-month commercial build also handles a 5-month spec home and an 18-month multifamily. The cost codes change. The number of months changes. The structure does not. Every project the GC bids gets fed through the same sheet. Pattern recognition kicks in. The peak gap on a $1.2M renovation should land near month 3. The peak gap on a $6M ground-up should land near month 5 or 6. If the model says otherwise, something in the inputs is wrong and the GC catches it before the bank does.
If you would rather not build this from scratch on a Tuesday afternoon, the SheetCraft Construction Budget Tracker ships with the schedule of values, S-curve spend engine, draw schedule, retainage logic, and lender-ready cash flow dashboard already wired. Drop in your cost codes, set your loan terms, and the forecast renders in 15 minutes instead of two hours. The math is the same. The work is already done. Spend the saved time on the conversation with the owner that the forecast just made possible.
Related template
Construction Budget Tracker
Track every line item, change order, and payment across your entire project. Spot a $23K billing discrepancy before it hits your bottom line — not after.
Get the Template — $49