I’m often asked to share models centered around residential development and home building. This is an area of real estate I’m completely comfortable in, since I spent the first 10 years of my career in new residential development.
Not surprisingly, I’ve assembled a decent size library of Excel pro formas for land developers and home builders over the years. However, too many of those models are overly complex and in some cases downright messy. So, a few years back I set out to build a robust, yet easy-to-use Excel pro forma for home builders to quickly vet potential build opportunities and/or to quickly do a (residual) lot value calculation.
Since that time, various improvements have been made to the model. Most recently, I added an unlevered (before debt) and levered (after debt) cash flow calculation in order to calculate internal rate of return and equity multiple on an unlevered and levered basis. Below find a more detailed description of the pro forma, a video tutorial, and a link to download the model.
You might also like my residential land development pro forma.
When to Use the Single Family Home Construction Pro Forma
This pro forma is a simple, back-of-the-envelope model. It is meant to be used at the earliest stages of analyzing a potential project to determine whether there is sufficient financial reason to continue spending time and money pursuing the project.
So you might use this model when a lot buying opportunity comes up, when moving into a new market, or just to refresh your numbers before the start of a new build.
The entirety of the model is built in one Excel worksheet (tab), giving the user the option to duplicate the tab for use with multiple projects. What this means is it can easily be adapted to analyze a multi-build project.
For instance, say you’re considering buying 10 lots in a subdivision with 10 different homes on each lot. You could take this model, duplicate the one tab ten times, and then add a ‘Project Summary’ tab that rolls up the various outputs from the ten tabs into a ‘Project Level’ pro forma.
Components of this “Back-of-the-Envelope” Model
The model includes two tabs. The ‘Version’ tab and the ‘Foggy Glen Lot 11 Blk A’ tab. The Version tab offers links to this post, to other models you might find of interest, and a list of changes made to the model.
The ‘Foggy Glen Lot 11 Blk A’ tab is where all of the analysis is done (ie. the ‘Analysis’ tab). The name of the tab is meant to be changed to the name of the home site you’re analyzing. To change the tab’s name, hover your mouse cursor over the tab name and right click. Select ‘Rename’ and name the tab whatever you wish.
The Single-Family Home Construction Pro Forma analysis tab includes eight sections plus a cash flow calculation section. The eight sections are:
- Summary – a debrief of the results of your inputs. This includes a projected profit and loss, the total project length in days, the profit margin (net profit ÷ gross sales price), the total builder cash required (i.e. builder equity), and the total builder cash required less overhead.
- Timing – where the user inputs a simple project timeline. Here you will include the project start date, the construction start day, the construction end day, and the day the home closes.
- Project Description – information about the physical characteristics of the build. This includes the name of the neighborhood, the lot and block, the lot size in square feet, the address with city and state, the name of the home plan, the size of the home, and the specifics of bed, bath, and garage.
- Project Costs – a very simple project budget. In this section you’ll build out your project budget. The period (i.e. day) when each item starts and ends , and the duration of each is also calculated and shown here.
- Home Sale Pro Forma – a breakdown of the gross sales price and a summary of the associated costs required to build the home. There is just one input in this section – the expected home sales price.
- Sources and Uses – a list of the sources (e.g. debt, equity) and uses (e.g. land cost, hard costs, etc) for the project. Here is where you’ll determine the construction financing amount that you’ll need to complete the project.
- Other Return Metrics – an unlevered and levered cash flow calculation, which allows the model to calculate unlevered and levered internal rate of return as well as unlevered and levered equity multiple.
- Data Validation – a data entry section, it is here where you’ll list the nomenclature of the home type or stories drop-down menu found in the Project Description section.
In addition to the eight sections listed above. Cash flows by by period (i.e. day) are shown on the right-hand side of the model. , using the box labeled ‘1’ in the upper-left hand corner of the worksheet. The calculations are made on a per period basis, with each period representing one day. The maximum number of periods is 730 or in other words, the maximum project length is two years.
Watch the Video on How to Use the Home Construction Pro Forma
I recorded a short video showing you how to use the model. The video is based on v1.0 of the model. While various elements have been updated/improved since this video was recorded, the core functions of the model have not changed. You can watch it below:
Download the Single Family Home Construction Pro Forma
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’d like) or maximum (your support helps keep the content coming – typical real estate Excel models sell for $100 – $300+ 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.
- Rename underwriting tab to ‘Analysis’
- Make ‘Analysis’ tab printable
- Added Data Validation to Timing Inputs
- Removed light gray background on Analysis tab
- Added Floor Plan + Lot/Block to header
- Linked lot closing date to construction start date by default
- Misc. formatting improvements
- Added date header row to detailed cash flow section
- Set detailed cash flow section to ‘show’, by default
- Updated version tab with a link to the model tutorial, other similar models
- Simplified header row formula to avoid errors
- Added return metrics section
- Calculated unlevered and levered cash flow by day
- Calculated unlevered and levered internal rate of return
- Calculated unlevered and levered equity multiple
- Misc formatting edits
- Initial release
About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.