, , , ,

Single Family Residential Investment Analysis Model (Updated Apr 2023)

Pre-2008, I bought and sold my fair share of single family homes – both for investment purposes (rental or flips) and to live in. In those days, almost everyone was “investing” in residential real estate – hence the market crash. I personally don’t have any plans to get back into buying and selling homes, beyond my own residence, but I recognize that many of you do. I decided to put together a single family investment analysis model in Excel (also commonly referred to as a real estate pro forma) for those of you who need a model to use when buying homes either to fix and flip, or to fix and rent out.

Note: A partnership waterfall module was added to v3.0 of this model to calculate returns for up to three partners; enhanced forecast of sources of uses cash flows was added in v3.3. See the version notes for the latest changes. 

How the Single Family Residential Investment Model Works

The model works with either the fix and flip, or the rental strategy. Using the Assumptions tabs, you input the parameters of your investment, and than check out the results on the Cash Flow and Summary tabs. The model can handle investments that last up to 84 months, details your monthly cash inflow/outflow, and calculates the maximum equity required to execute on the investment. Return metrics include unlevered and levered internal rate of return and equity multiple, cash on cash return, as well as net equity gain with and without financing.

Navigating the Six Worksheets in the Model

Version – See what changes have been made to the model in recent versions.

Assumptions – Most investment-related inputs are entered in this worksheet. These include property description, investment timing, acquisition and investment costs, disposition assumptions, fixed and variable operating expense assumptions, financing assumptions, and rental operation assumptions. It is also on this tab where you will choose between a ‘Hold’ strategy and a ‘Flip’ strategy.

Construction Budget – The second of two input sheets, it is on the Construction Budget worksheet where you detail out the renovation budget by line item. If you aren’t ready to itemize the construction budget, set a global renovation budget in cell D5.

Summary – A summary of the cash flow and returns of the proposed investment are shown on this worksheet.

Monthly Cash Flow – The meat of the calculations are performed on the Monthly Cash Flow worksheet. Assumptions entered on the Assumptions and Construction Budget sheets flow to the Monthly Cash Flow sheet where a forecast of all cash flows, by month, are calculated. Monthly returns on an unlevered (i.e. without debt) and a levered (i.e. with debt) basis are then calculated at the bottom of the worksheet.

Annual Cash Flow – Monthly cash flows are rolled up on the Annual Cash Flow sheet, and then annual returns on an unlevered and levered basis are calculated.

Partnership Cash Flow – Inputs, calculations, and outputs for partnership cash flows. Use this tab to forecast contributions from and distributions to up to three partners to the investment.

Quick Note: Not interested in DIY analysis? Consider working with A.CRE Consulting to handle your bespoke modeling project.

Other Real Estate Financial Models to Consider

Since building this model, I’ve put together a few other single-family related models that you might also consider.

You can also check out our entire library of real estate financial models.

Video Tutorial of the Single Family Residential Investment Model

For instructions on how to use the model, I recorded a walkthrough video using v1.0 of the model. While the model has since been updated, the core methodology of the model remains the same.

Download the Single Family Residential Investment Model

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.


Version Notes

v3.41

  • Added link to ‘Best Practices in Real Estate Financial Modeling’ guide on the Version tab
  • Removed ‘Net Equity at Sale’ metric on the ‘Summary’ tab’

v3.4

  • Fixed instruction box on Construction Budget that erroneously referenced cell C4 rather than C5
  • Added Unit Type input on Assumptions tab
  • Added building size and land size inputs on Assumptions tab

v3.3

  • Fixed instruction box on Construction Budget that erroneously referenced cell C4 rather than C5
  • Added Unit Type input on Assumptions tab
  • Added building size and land size inputs on Assumptions tab
  • Added ‘/SF and /M2’ metrics on Assumptions tab, Construction Budget tab, and Summary tab
  • Updates to placeholder values
  • Added more modern header design
  • Added auto-calculation of sources of uses cash flow on Partnership Cash Flow tab (rows 17:20)
  • Misc. formatting enhancements

v3.2

  • Removed ROUND() function from formulas in row 18 of Parntership Cash Flow tab; ROUND() function had created false error alert in edge cases
  • Created placeholder formula in cell G26 of Partnership Cash Flow to handle template scenarios with no Investor partners
  • Added logic to Investor #1 and Investor #2 formulas on Partnership Cash Flow to allow for 0% contribution by either partner
  • Misc. formatting updates

v3.1

  • Misc. changes to dummy input values
  • Misc. formatting enhancements
  • Added year and month label to header on Partnership Cash Flow tab
  • Added year and month label to header on Monthly Cash Flow tab

v3.0

  • Added Partnership Cash Flow module (see Partnership Cash Flow tab)
    • Model up to three partners (Owner/Sponsor, Investor #1, Investor #2)
    • Include preferred return (accrues but does not compound)
    • Include return of capital (pari passu)
    • Define distribution of excess cash flow after return of capital to partners
    • Calculate profit, IRR, and equity multiple for each of the three partners
    • Added Master Error Check
  • Show partnership returns (IRR, Equity Multiple, Contributions, Distributions, Profit) on the Summary tab
  • Misc. formatting enhancements

v2.0

  • Fixed issue where formulas in column F of Construction Budget tab were not calculating correctly; removed column F entirely from Construction Budget tab
  • Removed reference to $ USD to accommodate non-US users
  • Added ‘Occupancy Begin’ assumption to Assumptions tab
  • Complete revamp of Cash Flow tab
    • Renamed ‘Monthly Cash Flow’
    • Changed ‘Purchase Date’ assumption to Month/Year format, thus assuming all periods end on last day of the month
    • Updated date header row on Cash Flow tab
    • Improved all formulas on Cash Flow tab
    • Moved Renovation line into the ‘Acquisition + Investment’ section of Cash Flow tab
    • Revised Equity Multiple calculation and labels on Cash Flow tab
    • Cleaned up formatting
    • Added monthly Cash-on-Cash return metric (row 34 of Cash Flow tab)
    • Updated income, OpEx Hold, Management Fee, and Capital Reserve to begin at ‘Occupancy Begin’ month
  • Renamed ‘Net Equity Gain from Investment’ to ‘Net Profit from Investment’ on Summary tab
  • Complete revamp of Proforma tab
    • Renamed ‘Annual Cash Flow’
    • Updated line items to exactly match Cash Flow tab
    • Rolled up monthly cash flows to annual
    • Calculated returns on an annual, rather than monthly, basis
  • Various updates to description boxes
  • Misc. formatting enhancements

v1.4

  • Added version tab
  • Misc. formatting improvements
  • Added annual income and expense growth assumptions

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.