For several years now, our library of real estate models has been lacking a robust ground-up apartment development model. Sure, our All-in-One (Ai1) model has the capability to model multifamily development deals, but it’s more a generalist tool than a specialized one. As a result, I’ve found it wanting when used to model developments.
Update: In version 2.4, we added the ability to include a basic Mezzanine Loan as part of the Construction Debt. In v2.5 we added the option to choose the funding order of the mezzanine (or secondary) debt vs. the primary construction debt. A few important assumptions about the Mezz tranche here. First, the Mezz Loan interest is assumed to be interest-only, with interest accruing to an interest reserve when there is insufficient operating cash flow to cover the Mezz Loan payment. And second, the Mezz Loan is assumed to be repaid at the same time as the Construction Loan. Check out the tutorial for using this new feature.
So a few months back, I decided to build the ideal (in my eyes) apartment development model. I laid out some basic criteria for what I viewed as the optimal real estate development model, and then set out to build it for multifamily investments. It’s been a really fun project and I’m excited to share the results with the A.CRE audience. Below you’ll find an overview of the model, a video guide to getting started, and a link to download the model.
I’ve followed up this post with additional tutorials and support, similar to the type of resources I’ve created for the Ai1. The model has also been through several iterations, with recent updates including the addition of an Annual Cash Flow report tab, a detailed lease-up model, and the option to use variable rate construction interest. I hope you enjoy the model!
Basic Objectives – A.CRE Apartment Development Model
As I planned out my ideal apartment development model, there were a few criteria that guided the model’s creation. First, I wanted the model to be robust – meaning the model had to be able to do everything an institutional-quality multifamily development model can do. I’ve seen hundreds of institutional (and non-institutional) real estate models in my career, and I can honestly say this model holds its own in terms of features and functionality.
Second, I wanted it to be simple and intuitive. It seems conventional modeling wisdom is, the more comprehensive a model, the more tabs it must have. And I’m as guilty of this as the next guy – my Ai1 model has 30+ tabs! However, I wanted this project to be different. I set out to build the apartment development model with just one assumptions tab, and one report tab. And while I’ve since added two optional tabs that also have inputs (Detailed OpEx and Retail Income tabs), all assumptions necessary to run a full analysis are housed on one simple to use and navigate tab.
Finally, I wanted the model to be visually appealing. I paid special attention to heading, subheading, input, output, and other formatting elements. I built attractive toggle and recalculate buttons, and separated the various sections using attractive formatting styles. The printer-friendly Summary tab, which displays the high-level outcomes of the analysis, uses custom charts and sections to visualize the cash flow, risk, and return results. While I admit what is visually appealing is in the eye of the beholder, I’m pleased with the results and hope you will be too.
This model likely still contains errors. If you spot an error, have a feature request, or would like to make a suggestion to improve the model, please let me know.
Overview – A.CRE Apartment Development Model
The A.CRE Apartment Development Model includes one primary inputs tab, one summary report tab, one optional cash flow report tab, two optional input tabs, one data tab, and a tab to track version changes to 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 six sections, built from top to bottom. The sections can be accessed either by scrolling down to each or using the buttons along the top of the screen. The six sections are ‘Description’, ‘Development’, ‘Operations’, ‘Reversion (Sale)’, ‘Returns’, and ‘Sensitivity’.
Summary Tab (Visible by default)
While the return metrics levered IRR, levered EMx, and Development Spread are shown shown along the top of the Underwriting tab, the bulk of the risk and return metrics are shown/visualized on the Summary tab. The summary tab also includes six charts, a strengths/weaknesses section, a frame to include a picture/map, and a summary of the investment. The Summary tab is meant to be printed, and as such the view mode is set to Print Preview by default.
Annual Cash Flow (Hidden by default)
In beta v0.7, we added an Annual Cash Flow report tab. The tab rolls up the monthly cash flows to annual periods, so you can view high-level cash flows on one page. The report is printable. You can access the report by pressing the ‘Show’ toggle on the Summary tab with the Property Cash Flow section.
Retail Income Tab (Hidden by default)
On the Underwriting tab, the user has the option to model retail income. When this mode is toggled on, a ‘Retail Income’ tab becomes available. In this tab, the user enters a retail rent roll, expense recovery assumptions, basis retail operating expense assumptions, and leasing cost assumptions. The outcomes from this tab flow back to the Underwriting tab, where retail line items (e.g. ‘Retail Income’ and Retail Leasing Cost Reserves’) are added to the analysis.
Detail Expenses Tab (Hidden by default)
Similar to modeling retail income, on the Underwriting tab the user has the option to toggle a detailed operating expenses mode. Rather than entering annual values for the ten preset operating expense line items, when the user toggles the ‘Detailed’ operating expense mode on the Underwriting tab, a ‘Detail Expense’ tab becomes available where the user can detail out operating expenses. The detail then flows back to the Underwriting tab.
Data Tab (Hidden by default)
Some basic backend settings are housed in a Data tab. These settings are related to the s-curve development cash flow forecasting module, date and period headers, and may include other settings as the model evolves.
Guides and Tutorials for Using the Model
View a series of in-depth written guides and and video tutorials for using the various components of the Apartment Development model.
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365
Download the A.CRE Apartment Development 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 development 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.
- Revised Sources and Uses table on Summary tab to only pull initial Sources (not additional equity required during hold)
- Updated placeholder value for Permanent Debt (Underwriting I17) to also include loan fees in total loan amount
- Now includes ability to add loan and closing fees to permanent debt
- Added ‘Loan Funding Fee + Closing Cost’ assumption to cell H78
- Added ‘Loan Funding Fee + Closing Cost’ amount to cell I78
- Updated formula in row 78 to deduce loan fees and closing costs
- Revised default ‘Sale Month’ input formula to use 120 when ‘Perm. Debt’ module is turned on, otherwise 12 months following stabilization
- Fixed header on Annual Cash Flow tab – “ANALYSIS START” (cell R4) output was not dynamic
- Minor update to dummy input values
- Misc. formatting enhancements
- Changed the ‘Construction Loan’ and ‘Mezzanine Loan’ labels to blue font to denote an input; change order of funding by changing label
- Updated Construction Loan and Mezzanine Loan funding labels to link to the new label inputs for each field
- Misc. formatting updates
- Fixed ‘Analysis Month’ label on ‘Annual Cash Flow’ tab
- Replaced ‘Analysis Date’ with ‘Period Ending’ label on ‘Annual Cash Flow’ tab
- Added ‘Mezzanine Loan’ tranche to Construction Debt (Underwriting tab)
- Construction debt consists of 1. Construction Loan and 2. Mezzanine Loan
- Added ‘Construction Loan’ and ‘Mezzanine Loan’ assumptions (rows 60-63)
- Added variable interest rate line for Mezzanine Debt
- Updated Variable Rate logic to include Mezzanine Loan
- Added Mezzanine Loan and Construction Loan calculation module (ET62:JX62 and EP75:JX76)
- Replaced ‘Annual Rate’ calculation in cell E57 with a Blended Rate calculation between the two forms of construction debt
- Added ‘Funding by Construction Debt Type’ cash flows to rows 56:58
- Updated ‘SOURCES’ section of Summary tab to include Mezzanine Loan
- Misc. formatting enhancements and fixes
- Added Perm. Debt DSCR metric; see Summary tab (Key Assumptions and Return Metrics) and Underwriting tab (cell G74)
- Added Stabilized Yr. 1 Cash-on-Cash Return metric; see Summary tab (Key Assumptions and Return Metrics) and Underwriting tab (cell F136)
- Built a ‘GP Catch Up’ module into ‘Waterfall (GP/LP)’ section
- Drop-down menu appears in cells C229:D229 when ‘GP Distribution %’ in Pref. Return tier is less than ‘GP Contribution Share %’
- When ‘GP Catch Up?’ drop-down is set to ‘Yes’ and once LP hits 1st tier IRR, 100% of cash flow is distributed to GP until it “catches up” with LP IRR
- Misc formatting enhancements
- Added input (F48) to choose percentage of lease-up income to use for interest; unpaid construction interest accrues to interest reserve
- Updated Construction Interest Calculation logic to allow for Construction Loan Takeout prior to stabilization
- Updated Operating Shortfall to allow for Construction Loan Takeout prior to stabilization
- Added data validation to ‘Perm. Debt Funding Month’ input to ensure Perm. Debt funds after construction end
- Fixed error in SUM() for /SF and /Unit totals for first three items on Detail Expenses module
- Fixed error where ‘Insurance’ was growing by up to 2x the growth assumption when ‘Fixed’ was set to some value >0%
- Fixed minor error in Annual Cash Flow date header formula
- Changed default financing cost period to 0; default Waterfall type to ‘Single Promote’
- Renamed ‘Development Period Cash Flows’ section to ‘Investment Period Cash Flows’
- Nominally improved accuracy of construction interest calculation
- Renamed ‘Debt’ to ‘Construction Debt’
- Add Permanent Debt Module
- New section within Investment Cash Flow section entitled ‘Permanent Debt’
- Assumptions include: Funding Month, Fixed Interest Rate, Interest-Only or Amortizing
- Added toggle (row 71 of Underwriting tab) to activate and deactivate the new Permanent Debt Module
- Created ‘PermDebt_Activate’ and ‘PermDebt_Deactivate’ macros to turn module on and off
- Revised ‘Returns – Property-Level Cash Flows’ section to include Permanent Debt Funding, Payoff, and Debt Service
- Removed portions of property-level cash flow on ‘Annual Cash Flow’ tab; will expand in next version of the model
- Misc. formatting
- Now includes option to use square meters or square feet
- Added SF and M2 toggle in cell I8 of Underwriting tab
- Various label and logic updates to accommodate option to use square meters
- Updated header logic to be dynamic to SF vs. M2 toggle
- Added Stabilized Value /Unit and /SF (or /M2) metrics to rows 179 and 180 of the Underwriting tab
- Fixed mislabeled header (/Rentable SF instead of /Unit) in rows 55 and 60 of Underwriting tab
- Added conditional formatting in cell E57 of Underwriting tab; now reads ‘Detail —>’ when ‘Variable Rate’ is used
- Added ‘/Unit’ metric to sale proforma (D175:D179 of Underwriting tab)
- Added ‘/SF/YR/’ (or ‘/M2/YR’) metric to retail income and retail leasing costs lines
- Mill rate metric now included on row 162 of Underwriting tab for Untrended, Trended, and Sale pro formas
- Changed JV waterfall to use Promote assumption rather than ‘Distribution %” assumption (G215:G219, G236:G240 on Underwriting tab)
- Misc. formatting fixes and enhancements
- Fixed issue where Show_Calc macro wasn’t working
- Fixed issue where ‘Analysis Start’ date in header on the Summary tab wasn’t linked to input on Underwriting tab
- Fixed Unlevered ROI formula on summary tab; had been dividing by Total Project Cost including financing costs
- Updated IRR formulas to allow for scenarios where there is no cash flow in time 0
- Added printable ‘Annual Cash Flow’ report tab (shown by default); monthly cash flows from Underwriting tab rolled up for reporting purposes
- Created toggle to hide/show Annual Cash Flow report (see right side of Summary tab – Property Level Cash Flow section)
- Cleaned up Misc. VBA code
- Misc. formatting fixes/improvements
- Fixed issue on Detail Expenses tab where font for ‘Turnover Assumption’ input was black, rather than blue
- Added option to use Variable, rather than Fixed, construction loan interest rate (see ‘Fixed/Variable toggle under ‘Sources’ – Underwriting tab)
- Includes new required input – variable interest rate by month – when Variable is toggled on
- Includes two new macros: Fixed_Rate and Variable_Rate
- Includes Fixed and Variable buttons under ‘Sources’ section of Underwriting tab
- Built new Detailed Lease Up Module (see Row 73)
- Added Basic vs Detailed Lease Up Toggle in the ‘Income’ subsection of the ‘Operating Period Cash Flows’ section
- Coded two new macros – one to activate basic lease up underwriting, the other to activate detailed lease up underwriting
- Added logic such that the ‘Leased %’ row becomes an input (blue font cell) when detailed lease up activated
- Added logic such that when reverting from Detailed to Basic underwriting, Leased % formulas revert to default
- Misc. formatting fixes
- Added Retail Module tutorial
- Added Detailed Operating Expense tutorial
- Renamed Total Construction Cost to Total Project Cost on Underwriting tab
- Added Expense Growth Month and Expense Growth Year line item
- Corrected issue where Income/Expense Growth Year was mislabeled month, not year
- Added option to differentiate between Income growth begin and Expense growth begin month
- Locked line for Development Fee under Soft Costs so that it flows to GP cash flows/returns (if applicable)
- Added “Detail” option to development cash flow modeling
- Added Growth Begin assumption for operating income and expenses
- Added Valuation/Unit metric to Summary tab