Land Development & Subdivision Model with Construction Facility and Capitalised Interest (Excel)

Description
Most subdivision templates are a revenue line, a cost line and a difference. They give you a margin, and the margin is usually about right. What they do not give you are the two numbers a scheme actually lives or dies on: how much cash the equity has to have in the ground at the worst moment, and what happens to all of it when the lots sell more slowly than the promoter says they will.

This workbook answers both on the face of the file.

1. THE PEAK EQUITY REQUIREMENT, AND THE QUARTER IT HAPPENS INA development is not funded by its margin. It is funded by whoever can write the biggest cheque at the worst moment. The model tracks cumulative equity invested less capital returned in every quarter, reports the maximum, and names the quarter. In the worked scheme that is $4.48m in quarter three, against $4.81m of profit over six years. Anyone who cannot fund the first number does not get the second.
2. ABSORPTION RISK IS A SWITCH, NOT A FOOTNOTETwo cells on the Assumptions tab. One slips the opening of every phase by up to four quarters; the other cuts the lots sold per quarter without moving the dates. Slip the worked scheme by two quarters and the levered return falls from 16.99 per cent to 11.0 per cent, the interest reserve grows from $2.11m to $2.52m, and the peak equity rises to $5.15m.
3. THE INTEREST RESERVE IS CAPITALISED, AND IT IS VISIBLEConstruction interest is charged on the opening balance, added to the loan rather than paid out of cash that does not exist yet, and reported as its own line in the cost stack. In the worked scheme that is $2.11m, nine per cent of total cost, and the line most development templates lose entirely. Because it is charged on the opening balance there is no circular reference anywhere in the file.

THE FACILITY, PROPERLY MODELLED
The draw each quarter is the smallest of three things: what the loan-to-cost cap still allows, what is left of the commitment, and the eligible cost incurred that quarter. Eligible means land, acquisition, infrastructure, soft costs and contingency, not marketing, not selling costs, not capitalised interest. Repayment is the release price, a share of each lot price, capped at the balance outstanding. The loan-to-cost cap is tested against principal drawn rather than the raw balance, because the cap applies to money drawn against cost and not to the interest the facility rolls up on itself.

THE CHECKS
Eighteen tests. Every lot sold inside the horizon. Each phase sells exactly its own lot count. Infrastructure spend equal to the phase plan. Every cost line an outflow. Total cost equal to the sum of its eight components. The facility never negative, never above the commitment, never redrawn after it is repaid, clear by the final quarter. Interest equal to the rate on the opening balance. Sources equal to uses across the whole scheme.All eighteen were independently re-derived in 86 separate comparisons against a second, from-scratch implementation of the entire scheme, with both internal rates of return recomputed by bisection rather than by calling Excel.

THE WORKED EXAMPLE
Alderbrook Meadows. 210 lots in three phases of 60, 70 and 80. Land at $6.4m. Lot prices from $124,000 to $137,000 with 2.5 per cent annual escalation. A $9m facility at 8.25 per cent, 60 per cent loan to cost, 65 per cent release price. Revenue $28.09m, total cost $23.27m including $2.11m of capitalised interest, profit on cost 20.7 per cent, peak equity $4.48m in quarter three, unlevered return 16.23 per cent and levered 16.99 per cent.

FORMAT
One Microsoft Excel workbook. No macros, no add-ins, no external links, no password protection, no locked cells, iterative calculation off. A guide and preview PDF is included. Building this from scratch takes a competent financial modeller two to four days.