Land Development Model – Multi-Scenario (Updated Apr 2024)

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, townhome development, and other build-to-sell situations.

Note: In version 0.75, I used a ChatGPT custom GPT we built called the ‘A.CRE Code Helper for Financial Models‘ to improve the code and add code comments to all macros in this model. I then tested each macro. However, given the GPT made hundreds of code changes, there may be bugs I didn’t catch. So, please let us know if you run into any issues.

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.

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.

Features – Land Development Model

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
• Macro to ‘Add’ and ‘Delete’ development cost line items
• Macro to ‘Add’ and ‘Delete’ unit/lot/pad line items
• Floating rate debt
• Secondary debt (e.g. mezzanine)
• 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
• Two additional tiers to the partnership waterfall
• 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.

---

Compatibility

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.

---

Version Notes

v0.752

• Added GP Dist. % input in ‘Tier 1 – Return of Capital and Preferred Return’ (Underwriting E113); allows a disproportionate distribution to either LP or GP in return of capital and pref tier

v0.751

• Fixed minor issue with Recalculate() macro that arose from code improvement project
• Deleted ‘Show’ and ‘Hide’ Annual CF macros as they’re not used in this model

v0.75

• Misc updates to the ‘Version’ tab
• Used ‘A.CRE Code Helper for Financial Models GPT’ to add code comments in all subroutines in the following modules to help users understand the code:
  • Development
  • General
  • Sales
• Used ‘A.CRE Code Helper for Financial Models GPT’ to improve efficiency of code
• As hundreds of code changes were made, tested each macro individually

v0.742

• Minor fix to Recalculate () subroutine
• Rewrite of All_Equity() subroutine

v0.741

• Added Named Cell: Debt_LTC_Input
• Revised All_Equity () macro to fix an error and improve its speed

v0.74

• Removed beta tag given the model has under gone 12 updates and has been downloaded thousands of times since its release in 2022
• Added ‘Assumption Notes’ section to Underwriting tab; open/close using the [+] / [-] button at the top of column O; use notes to support inputs on each row
  • Included various placeholder notes for reference
• Removed ‘Module1’ from Modules list in code backend
• Used ChatGPT to rewrite the Recalculate () macro to improve its speed
• Used ChatGPT to rewrite the All_Equity () macro to improve its speed
• Misc. placeholder values and formatting updates

beta v0.73

• 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

beta v0.72

• 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

beta v0.71

• 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

beta v0.7

• 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

beta v0.6

• 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

beta v0.5

• 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

beta v0.4

• 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

beta v0.31

• 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

beta v0.3

• 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

beta v0.2

• 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

beta v0.1

• Initial release