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.
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.
- Single-Family Home Construction Pro Forma for Home Builders – For modeling ground-up single family home construction
- Residential Land Development Pro Forma – For analyzing single-phase residential (lot) land development
- House Flip Pro Forma – A robust back-of-the-envelope fix and flip model built in Excel
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
