Land Development Model – Multi-Scenario (Updated Jan 2022)
Over the years, we’ve shared eight development-focused real estate financial models and numerous development-specific tools to the A.CRE Library of Excel Models. Nevertheless, there’s been a hole in the development offerings in our library. The library has been missing a model to analyze general land development scenarios, such as master-planned residential and commercial developer opportunities as well as townhome developments.
Prior to today, when an A.CRE reader had asked for help with those scenarios, we’d suggest to readers our Residential Land Development Pro Forma or our Condo Development Model. Neither was an ideal solution, but acted as sufficient workarounds.
But today, I’m excited to share a new alternative: this Multi-Scenario Land Development Model. This model offers greater flexibility to analyze a variety of unique residential land development, commercial land development, and townhome development situations.
Important Note: While this model has gone through eight updates since its original release in early 2021, it nevertheless is still in beta and therefore likely contains errors. I also intend to add features to the model based on your feedback, and what I see as opportunities to enhance this model. If you find a bug or have a feature you’d like included in a future version, please let me know.
Basic Objectives – Land Development Model
The purpose of this land development model is to provide a foundation upon which you (and me) can analyze a variety of different and unique land development scenarios.
In its current beta form, the model is in its infancy. The development cash flow module is robust, and the general framework is quite sophisticated. But beyond that, the various modules that make up this model are in need of further expansion.
However, the structure of the model and the general methodology make this tool easily expandable. Thus, my intention in the coming months is to create comprehensive off-shoot models, using this model as a foundation.
So for instance. I plan to take this model and expand the Unit Sale (Reversion Cash Flow) module to build a solid Townhome Development Model. I also plan to add multi-phase functionality to this model.
Ultimately, the objective of this model is to provide the flexibility to handle most any for-sale development scenario.
Overview – Land Development Model
The Multi-Scenario Land Development model uses my single-tab underwriting technique, common to the models I’ve released in recent years. This means the great majority of underwriting inputs are entered on the Underwriting tab, providing a more intuitive and easy-to-use experience. Here are all of the tabs included in the model:
Version Tab (Visible by default)
The model opens initially to this tab so you can see what changes have been made in the most recent version of the model. On this tab you can also find links to model tutorials, guides, support, and other information.
Underwriting Tab (Visible by default)
The Underwriting tab is where all of your primary inputs are entered. The tab is broken up into five sections, built from top to bottom. The sections can be accessed by scrolling down to each or using the convenient navigation buttons at the top of the Underwriting tab.
The five sections are ‘Investment Description’, ‘Development Cash Flows (Investment)’, ‘Sale Cash Flows (Reversion)’, ‘Project-Level Cash Flow and Returns’, and ‘Partnership-Level Cash Flow and Returns’.
Summary Tab (Visible by default)
The Summary tab includes a ‘Model Setup’ section, as well as ‘Investment Description’, ‘Key Assumptions and Return Metrics’, and ‘Project-Level Cash Flow Visualization’ sections. The tab is designed to be printed, and contains the key metrics important to the project that you might share with third-parties.
The model setup section includes a drop-down menu for ‘Model Type’, which dynamically updates various labels based on the model type.
Annual Cash Flow (Hidden by default)
Coming Soon. This feature will be added as the beta version progresses.
Features – Land Development Model
While the model is still in its beta form, it’s not lacking in features. Here is a summary list of the features included in the latest version of the model:
- Option to model a residential land development, commercial land development, townhome development, or single-family home development
- Module-based framework, making it easier to expand/customize the model.
- Robust development cash flow module, with sections for land costs, hard costs (horizontal), hard costs (vertical), soft costs, and carry costs. The development cash flow module also allows you to forecast development cash flow using a straight-line method, s-curve method, and detailed method.
- Sources and Uses module that includes one tranche of debt, and a two-partner equity structure.
- Includes a technique to handle the iterative nature of construction interest reserve, using a one-click macro-enabled button.
- Unit Sales (Reversion Cash Flow) module that adapts to the type of development selected
- Built to easily allow addition/deletion of various line items
- Summary tab with various institutional return metrics such as IRR and Equity Multiple
- Two-tier partnership waterfall
- Fully dynamic charts on Summary tab visualize project cash flow
- Option to use either the metric or imperial system of measurement
- And much more
Planned Features – Land Development Model
As the model progresses and as my time permits, I hope to add at least the following features to this model:
- Annual Summary Report tab
- Macro to ‘Add’ and ‘Delete’ development cost line items
- Macro to ‘Add’ and ‘Delete’ unit/lot/pad line items
- Two additional tiers to the partnership waterfall
- Secondary debt (e.g. mezzanine)
- Floating rate debt
- Greater detail in the unit sales module
- Option to model Hard Costs (vertical) based on the unit sales table
Video Walkthrough – Land Development Model
Watch a walkthrough video on how to use the Multi-Scenario Land Development Model. It’s important to note that this walkthrough video is based on beta v0.1 of the model. As the model approaches its full release, I will plan to build a series of videos that provide greater specificity.
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365
Download the Land Development Model (Multi-Scenario)
To make this model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you need) or maximum (your support helps keep the content coming – typical real estate development models sell for $200 – $500+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.
- Added a Header in the fixed rows (2:4) for the backend calculation section (starting Column GU)
- Fixed issue where Interest Reserve was not populating after construction period ended
- Added ‘Debt Balance’ line (row 66) to track outstanding balance of Total Debt
- Added Debt Module feature, which is automatically turned on when LTC (D66 on Underwriting tab) is greater than 0%’
- Updated Recalculate macro to account for scenarios where the Debt Module is off
- Fixes issue where setting LTC 0% had errored out the model
- Fixed issue where ‘Description’ navigation button did not scroll back up to Investment Description section
- Updated frozen Panes to not include ‘Investment Description’ header
- Renamed ‘Total Construction Debt’ to ‘Total Debt’ on Underwriting tab to account for Secondary Financing
- Changed ‘Underwriting’ cell B6 label to ‘Project Name’ in preparation for multi-phase option
- Revised ‘Underwriting’ title (cell B2) to clean up clipping issue when project name is too long
- Added further detail to ‘Sources and Uses’ table on Summary tab
- Added ‘Secondary Financing’ option to the model (e.g. second mortgage, mezzanine debt, private debt)
- New ‘Debt Assumptions’ section in rows 63:66
- Major revamp to the backend interest calculation (cells GN57:NR62)
- Set LTC for the two debt tranches in cells D64:D65
- Choose Fixed or Variable for each tranche in cells H64:H65
- Enter Variable rate assumptions in rows 64:65
- Misc. formatting updates
- Misc. updates to placeholder values
- Fixed issue where when using ‘Pari Passu’ deployment of equity and debt, that the equity cash flow was missing the carry costs
- Updated ‘Recalculate’ macro to solve for missing carry costs
- Misc. updates to ‘Recalculate’ macro for improved performance
- Added IFERROR logic to Unit Sales table to accommodate scenarios where a the number of units under a unit type is set to 0
- Created ‘Add Line’ and ‘Delete Line’ functionality for Unit Sales table
- Wrote ‘Add_Unit_Types’ macro (see VBA module: Sales)
- Wrote ‘Delete_Unit_Types’ macro (see VBA module: Sales)
- Added Add Line and Del. Line buttons above Unit Sales table
- Fixed issue where Hectares were not being calculated (L6 on Underwriting tab)
- Added Total Project Cost pie chart to Summary tab
- Misc. updates to placeholder inputs
- Added option to fund equity and debt either ‘Equity 1st’ or ‘Pari Passu’ (i.e. at the same time)
- Inserted drop-down menu to select funding order in cell F64
- Revised Equity Funding formula (cells GU53:NR53) to accommodate multiple funding order options
- Added ‘Rate Type’ heading to cell I59 for clarification
- Fixed labels in cells GN68:GN71 to reflect that cash flows in those rows are related to the Interest Reserve
- Added option to choose what percentage of sales proceeds to use towards loan paydown
- Inserted % input into cell F68 to select percentage of sale proceeds to go towards loan paydown
- Updated formulas in cells GU65:NR65 and GU70:NR70 to accommodate a paydown less than or equal to 100% of proceeds of each sale
- Misc. formatting updates
- Fixed a bug that created erroneous GP/LP returns when outflows and inflows occurred in the same period
- Added an Error Check (cell N114) to the LP/GP return cash flows
- Added Variable Interest Rate module
- Added Fixed/Variable drop-down menu to cell I60
- Added Variable Rate input starting in cell O60 of row 60
- Updated backend to use variable rate of fixed rate based on input in cell I60
- Wrote Add and Delete macros for each of the four budget categories
- Six new macros in VBA backedn
- Inserted Add and Delete buttons for each of the budget categories
- Added ‘Profit Margin’ metric to both Underwriting and Summary tabs
- Added summary levered Profit Margin and Equity Multiple to top bar on Underwriting tab
- Misc. formatting updates
- Added navigation buttons to top of Underwriting tab
- Minor updates to labels to make them fully dynamic to Model Type
- Fixed Promote and Preferred Return labels, which had been reversed
- Linked address items from Underwriting to Summary tab
- Misc. formatting updates
- Initial release