, , , ,

Single Family Home Construction Pro Forma for Home Builders (Updated Jan 2024)

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 built new module for assessing multiple home building projects. As of v2.0 of the model, you’ll find a ‘Projects’ tab where you can ‘Add New Homes’ to the analysis. It’s still possible to use the model for standalone projects; just use one home. Or you can analyze multiple home construction projects out three years.

You might also like my residential land development pro forma.

When to Use the Single-Family Home Construction Pro Forma

This pro forma is still meant to be a back-of-the-envelope model, although the functionality added in v2.0 of the model makes it more comprehensive. So you might use this model when a lot (or multiple lots in a subdivision) 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 analysis for each home is built in one Excel worksheet (tab), while the aggregate analysis of multiple homes is shown on the ‘Project’ tab. To assess more than one home, simply click the ‘Add New Home’ button on the ‘Project’ tab and instructions for adding one ore more new home construction analysis worksheets will appear between the ‘Homes–>’ and ‘<–End’ separator tabs.

When you open a fresh copy of the model, you’ll immediately find five tabs along the bottom:

  • Version – The version tab lists what’s new in the model and provides helpful links to resources (such as this).
  • Project –  On this worksheet, you’ll perform multi-home analysis. To add homes to your analysis, simply click the blue ‘Add New Home’ button. You can also name the Project and set the Analysis Start date on the Project tab.
  • Homes–> – This worksheet is a divider, together with the ‘End’ worksheet. Any Home Analysis tab inserted between these two dividers will be included in the analysis shown on the ‘Project’ tab.
  • Home #1 – This is where the bulk of your analysis will be performed. The model includes a dummy property with the tab labeled ‘Home #1’. Feel free to change the name of the tab (right-click the tab and click rename), and then follow the directions below for completing the Home Construction Analysis.
  • Home #2 – Similar to Home #1, this is where analysis for your second home will appear. If you only intend to have one home in your project, either delete this worksheet or move drag the tab so that it’s not between the ‘Homes–>”<–End’ tabs.
  • Home #3 – Similar to Home #1 and Home #2, this is where analysis for your third home will appear. If you only intend to have one or two homes in your project, either delete this worksheet or move drag the tab so that it’s not between the ‘Homes–>”<–End’ tabs.
  • <–End – This is the second divider tab within which all homes included in the analysis are placed.

In addition to the above visible worksheets, there is one hidden worksheet. That worksheet is called ‘Home Template’ and it’s the template home analysis tab that is duplicated each time you click the ‘Add New Home’ button. Basically, when you click to add a new home, the model duplicates the ‘Home Template’ worksheet, places it between the ‘Home—>’ and ‘End’ tab dividers, and asks you to rename the worksheet.

It’s recommended that you leave the ‘Home Template’ worksheet hidden.

Components of the Single-Family Home Construction Pro Forma

As described above, when opening a fresh copy of the model you’ll find a ‘Home #1’ tab. It’s here where all of the individual property analysis is done. 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:

  1. 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.
  2. 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.
  3. Home 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.
  4. Home 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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 period (i.e. day) are shown on the right-hand side of the model. The calculations are made on a per period basis, with each period representing one day. The maximum number of periods is 1095 or in other words, the maximum project length is three years.

Watch the Video on How to Use the Home Construction Pro Forma (Pre-Version 2)

I recorded a short video showing you how to use the model. The video is based on v1.0 of the model. This video is helpful for learning how to analyze an individual property. Once you’ve finished watching this video, review the second video below on how to include additional homes, if necessary.

How to Use the New Home Construction Model – Version 2 and Later

In mid-2020, I updated the model to analyze multiple homes as part of a large “Project”. With this update I added a ‘Project’ tab, which sums up the cash flows from the various home construction projects. The tab then estimates the Source, Uses, Cash Flows, Pro Forma, and Returns of the combined homes. In this video, I discuss the differences and show you how to navigate the various changes in version 2.

Note that v2 is a major update, and therefore may contain errors. Please let us know if you spot anything needing fixed. And in v2.4, thanks to the help of AI, we introduced functionality to the ‘Add Home’ macros to add more than one home at once.


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.

IMPORTANT (COMPATIBILITY): This model is ONLY compatible with versions of Excel newer than 2013. The file is an .XLSB (i.e. Excel Binary Workbook) and as such, if your version of Excel isn’t compatible it will appear as if the file is a zipped/compressed file – Learn more here.


Version Notes

v2.4

  • Updated placeholder values to better represent market values in 2024
  • Added two additional ‘Homes’ to template file to better demonstrate roll up of ‘Project’ cash flows
  • Updated ‘Home Template’ worksheet to have more generic placeholder values
  • Renamed worksheet ‘End’ to <–End’; (Note: Worksheets placed between the ‘Homes–>’ and ‘<–End’ tabs are included in roll up of ‘Project’ cash flows
  • Major updates to VBA code (with help of ChatGPT via this conversation: https://chat.openai.com/share/c77cfa93-34c3-44f2-958a-e00fb9cf39c4)
    • Rewrote Add_New_Home macro to add functionality to add more than one home at once (max 50 at one time)
    • Added functionality to end new home additions by clicking Cancel at any time
    • Improved Error Handling for Add_New_Home and New_Home_Worksheet macros
    • Improved Naming Conventions for Add_New_Home and New_Home_Worksheet macros
    • Avoiding direct ‘Select’ and ‘Copy’ statements in New_Home_Worksheet macro
    • Added Parameterization to New_Home_Worksheet
    • Replaced Sheet7 reference to ‘Home Template’ in Add_New_Home and New_Home_Worksheet macros to make the code easier to understand
    • Added Code Comments to Add_New_Home, New_Home_Worksheet, and Name_Worksheet macros
  • Misc. formatting updates

v2.3

  • Fixed issue where additional Homes added did not have ‘Forecast Method’ logic included
  • Updated VBA code

v2.2

  • Simplified forecast formulas to prepare for ‘Forecast Method’ logic
  • Added the ability to select a ‘Forecast Method’ for modeling cost line items (e.g. hard costs on an s-curve, soft costs straight-line)
    • Drop-down menu created in cells M42:M50 of Home worksheet (Straight-Line, S-Curve, Manual Input)
    • Updated formulas in rows 42:50 to model cash flows based on ‘Forecast Method’ input for each line item
  • Misc. formatting and placeholder value enhancements

v2.1

  • Fixed issue with XIRR() formula on Project tab
  • Fixed issue where levered cash flows were not aggregating correctly at Project level
  • Moved ‘Stories’ input to cell E33, from G33

v2.0 (Major Update)

  • Converted to a .XLSB file to improve speed and size
  • Added ability to analyze a multi-home project
    • Now includes ‘Project’, ‘Homes–>’, and ‘End’ worksheets
    • Duplicated previous ‘Analysis’ worksheet, renamed ‘Home Template’, and set to be hidden
    • Analysis’ tab renamed ‘Home #1’ to denote ability to add additional homes
    • Built ‘Project’ tab to roll up cash flows from all ‘Homes’ tabs placed between the ‘Homes–->’ and ‘End’ tab
    • Wrote macros to add Homes to the Project
    • Inserted clickable shape on ‘Project’ tab and assigned the ‘Add New Home’ macro to the shape
  • Renamed heading labels to reflect ‘Project’ and ‘Home’ naming convention
  • Changed tab color to differentiate between ‘Home’ tabs, ‘Project’ tabs, and separator tabs
  • Recorded video tutorial for using the new ‘Project’ feature
  • Extensive formatting changes

v1.2

  • 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

v1.1

  • 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

v1.0

  • Initial release

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30+ billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently President and member of the founding team at Stablewood. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.