Building a Property Development Cash Flow Model in Excel: A Complete Guide

Property development is a capital-intensive business where timing is everything. A dollar spent today is not the same as a dollar received eighteen months from now - and in the Australian market, where stamp duty, GST, and construction costs can shift dramatically within a single project lifecycle, getting the cash flow model right is essential.

This guide walks through the full methodology - from first principles to advanced sensitivity analysis - so you can build a robust, auditable model that banks, equity partners, and town planners will take seriously.

Why a Cash Flow Model Matters More Than a Simple Feasibility

Consider two identical $50 million apartment developments with the same gross profit. Project A sells 80% off-the-plan before construction starts and delivers on time. Project B sells nothing until completion and runs four months late due to wet weather and supply chain delays. A static feasibility shows both as profitable. The cash flow model reveals that Project B burns through its equity facility by month nine, breaches its debt covenants in month fourteen, and delivers a sub-10% IRR despite looking healthy on paper.

A proper development cash flow model accounts for:

• Time value of money - $1 received today is worth more than $1 received in two years
• Cost phasing - when each trade package actually gets paid
• Revenue phasing - deposit, progress payments, and final settlement timing
• Debt draw and repayment schedules - interest accrual, facility fees, and covenants
• Equity waterfalls - how returns cascade between preferred equity, ordinary equity, and performance hurdles
• Tax timing - GST on sales versus input credits on construction, and their impact on quarterly BAS

Core Components of the Development Cash Flow Model

Before you open Excel, it pays to map out the model's architecture. A well-structured model has separate worksheets for inputs, calculations, outputs, and scenario testing.

1. Project Timeline and Time Axis

The foundation of any cash flow model is the time axis. For Australian development projects, a monthly time step is standard, while weekly is overkill for most projects and quarterly too coarse to capture construction cash flows.

Set up a row of dates using the EDATE function:

=EOMONTH(Start_Date, COLUMN()-COLUMN($A$1))

This lets you drag the date row across and have it auto-calculate month-end dates. Label each column with the month number (Month 1, Month 2, etc.) for easy cross-referencing.

Typical project timeline for a medium-rise apartment development (25-40 units):

2. Revenue Phasing

Revenue in a development project arrives in tranches, not as a single lump sum at the end. For off-the-plan sales, model three distinct revenue events:

Deposit (typically 10%): Received at contract exchange, usually within 14 days. For a $1 million apartment, that's $100,000 booked in the month of exchange.

Progress claims (varies by state and contract): In NSW and Victoria, staged progress claims tied to construction milestones are common, including slab pour, frame completion, lock-up, and finishing stage. Each claim typically represents 15-20% of the contract price.

Final settlement (balance ~60-70%): The largest cash inflow, occurring when title transfers. This is where settlement delays bite hardest.

Use a VLOOKUP or XLOOKUP to map each unit's settlement month based on your construction timeline, then sum into the revenue row on your cash flow sheet.

Pro tip: Build a settlement delay buffer row. Add 1, 2, and 3-month delays using a drop-down selector and watch the IRR compress in real time.

3. Cost Phasing

Cost phasing is where most models need the most attention. Taking the total construction contract value and dividing it evenly across the build period understates peak funding requirements.

An S-curve cost profile is more realistic:

Mobilisation (Months 1-2 of construction): 10-15% of total construction cost. Site establishment, insurances, piling rigs, first concrete pour.

Main works (Middle 60% of construction timeline): 60-70% of cost. Structured trades including formwork, reinforcing, concreting, structural steel, fire services, hydraulics, and electrical rough-in.

Completion and defects (Final 15-20%): 15-20% of cost. Fit-outs, landscaping, OC costs, defects liability period provisions.

Break the construction budget into at least six trade packages:

1. Site works and demolition
2. Structure and façade
3. Services (hydraulics, electrical, mechanical, fire)
4. Internal finishes
5. External works and landscaping
6. Contingency (typically 5-10%)

Each package gets its own phasing row, and the rows sum into a total cost outflow for each month.

Australian context: Residential construction is input-taxed for GST purposes. Most developers will claim input tax credits on construction costs via their BAS, but the timing of those credits, typically one quarter after payment, creates a working capital gap. Model this explicitly with a GST recovery lag row.

4. Financing and Debt Draw Schedule

The debt draw schedule links construction costs to external funding. For an Australian development, the typical capital stack looks like:

Model the debt draw using a cumulative cash flow approach:

• Month 1: Opening cash balance + debt draw = available funds
• Available funds less costs incurred = closing cash balance
• If closing cash balance goes negative, draw additional debt (subject to headroom)

Interest calculation: Use the IPMT function or a simple daily interest accrual:

=SUM(Debt_Drawn_This_Month * Annual_Rate / 12)

Most senior facilities in Australia capitalise interest - add drawn interest back to the loan balance each month.

Covenant tests: Build a row that checks the loan-to-value ratio (LVR) and interest coverage ratio (ICR) at each draw point. If a test fails, flag it in red with conditional formatting. This is critical for lender presentations.

5. IRR and NPV Calculations

With fully phased cash flows in place, calculating returns is straightforward.

Net Present Value (NPV):

=NPV(Discount_Rate / 12, Cash_Flow_Range) + Initial_Equity

Use monthly (not annual) discounting because your cash flows are monthly.

Internal Rate of Return (IRR):

=XIRR(Cash_Flow_Range, Date_Range)

The XIRR function is preferred over IRR because it handles irregular date spacing - which is almost always the case in real development projects.

Modified Internal Rate of Return (MIRR):

For more conservative analysis, use MIRR with a reinvestment rate (typically the risk-free rate, around 4-5% in Australia) and a finance rate (the cost of debt):

=MIRR(Cash_Flow_Range, Finance_Rate, Reinvestment_Rate)

6. Sensitivity Analysis

No development model is complete without sensitivity analysis. The two most critical variables in Australian development are:

• Average sales price per square metre (driven by market conditions)
• Construction cost per square metre (driven by materials, labour, and supply chains)

Build a two-way Data Table:

Each cell shows the project IRR under that combination. Highlight the base case and use conditional formatting to flag falls below your hurdle rate (typically 15-18% for Australian development equity).

Tornado chart: Create a tornado chart to rank sensitivity variables by their impact on NPV. Typical order of impact for Australian developments:

1. Sales price per m² (highest impact)
2. Construction cost per m²
3. Settlement timing (months delay)
4. Construction duration
5. Finance rate
6. DA approval timeline
7. Contingency utilisation

Bringing the Model Together: A Worked Example

Let's walk through a realistic scenario to see how these components interact.

Key inputs:

Base case results:

Sensitivity highlights:

• A 10% drop in average sales price (to $9,090/m²) reduces IRR to 16.2%, still above the hurdle, but tight.
• A combined 10% cost overrun and 10% price drop pushes IRR to 10.3%, well below acceptable returns.
• A three-month settlement delay reduces IRR by approximately 2.4 percentage points and increases peak debt by $1.8 million.

This analysis gives a developer the confidence to proceed with a reduced offer on the site, negotiating a price reduction to build additional headroom into the model.

Common Mistakes in Development Cash Flow Modelling

Watch for these errors in your own models:

Ignoring the time value of money on deposits: A 10% deposit taken today is not worth 10% of the final price in today's dollars. Discount it back at the project's cost of capital.

Double-counting GST: The sale price of new residential property is GST-inclusive, and the developer owes 1/11th of the sale price to the ATO. But input tax credits on construction costs reduce the net GST liability. Model this explicitly or use the margin scheme where eligible.

Underestimating holding costs: Land holding costs (rates, insurance, security) during the DA approval phase can easily run $50,000-$150,000 for a medium-sized site. These are often omitted from models that start at construction.

Over-optimistic settlement timing: Industry data from the Property Council of Australia shows that 40-50% of off-the-plan settlements are delayed by at least two months. Build this into your base case, not just your downside scenario.

Using annual discounting on monthly cash flows: Always match your discounting frequency to your cash flow frequency. Annual discounting on monthly cash flows understates NPV.

Excel Formulas to Build Your Model

Here are the key Excel functions you'll use in a development cash flow model:

Date handling:

=EOMONTH(start_date, 0)           - First month-end
=EDATE(start_date, MONTH_NUMBER)   - Subsequent months
=NETWORKDAYS(date1, date2)         - Business days for interest calc

Lookups for revenue and cost phasing:

=XLOOKUP(month_ref, phase_table_month, phase_table_amount, 0)
=SUMIFS(cost_data, month_range, target_month, trade_category, "Structure")

IRR and NPV:

=XIRR(cash_flow_range, date_range)
=NPV(monthly_rate, cash_flow_range) + initial_investment
=MIRR(cash_flow_range, finance_rate, reinvest_rate)

Sensitivity and scenario:

=TABLE(data_table_row_input, data_table_col_input)
=CHOOSE(scenario_selector, scenario_1_array, scenario_2_array, ...)

Conditional formatting for covenant tests:

=AND(MAX(LVR_range) > 0.75, CURRENT_LVR > 0.75)  - Flag in red
=IF(ICR < 1.2, "BREACH", "PASS")

Extending the Model

Once your base model is working, consider layering in:

• Equity waterfall modelling: Distribute returns according to a preferred return / promote structure, where the developer receives a higher share of profits above a target IRR (often 18-20%).
• Phased development: For multi-stage projects, build separate sheets for each stage and link them with a master timeline. Stage 1's cash flows fund Stage 2's land acquisition.
• Monte Carlo simulation: Use Excel's built-in data analysis tools or a simple VBA macro to run 1,000+ iterations with randomised inputs for sales rate, cost escalation, and timing. This provides a probability distribution of outcomes rather than a single point estimate.
• Portfolio consolidation: If you're running multiple projects, build a consolidation sheet that aggregates cash flows across all active developments so you can see group-level liquidity and covenant headroom.

For related modelling techniques, see our guides on lease-versus-buy analysis and sensitivity testing in financial models.

Bringing It All Together

A development cash flow model is not a set-and-forget spreadsheet. It's a living tool that should be updated monthly, or even weekly during construction, as actual costs come in, sales contracts exchange, and settlement dates firm up.

The developers who succeed in the current Australian market - where construction costs have risen 25-30% since 2021 and interest rates have more than doubled - are the ones who model conservatively, stress-test aggressively, and refuse to fall in love with their own projections.

Further Reading

For a complete overview of this topic, see the Financial Modelling in Excel: The Complete Guide for Australian Businesses (2026).