As promised, I’m following up the release of the A.CRE Apartment Development model with a series of walk-throughs and tutorials to help you better understand the various elements of the model. The tutorials start with the basics – a guide to getting started, and then get progressively more advanced.
If you’ve already visited the model’s download page, some of the material below is redundant – but I’ve included everything in this post so you have one place to come to learn to use the model.
If you haven’t already, download the model here.
Overview – A.CRE Apartment Development Model
The A.CRE Apartment Development Model includes one primary inputs tab, one 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.
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.
Tutorial #1 – Guide to Getting Started Video Walk-through
To help you get started using the model, I’ve recorded a quick video overview of the model to get you started.
Tutorial #2 – Using the Model to Assess a Ground-Up Development (Merchant-Build)
This is the second tutorial in a growing series on using the model. In this video, I use the model to assess a hypothetical ground-up development opportunity.
- Click here to download the assumptions used in this example.
Tutorial #3 – Using the Retail Module
Have a retail component to your apartment development project (e.g. ground floor retail)? Use the Retail Module to account for the retail income, expenses, and capital expenditures.
Tutorial #4 – Accessing the Detailed Operating Expenses Module
In this tutorial, I cover how to use the Detailed Operating Expenses module to really dig into your operating expense assumptions. Detail each operating expense item by numerous sub items, list out personnel on payroll, underwrite make ready (i.e. turnover) using a renewal probability, and set mill rate and assessed value in your property tax calculation.
Tutorial #5 – Modeling Complex Partnership Structures including Double Promotes
In this tutorial, I take a hypothetical partnership structure and model it in the Apartment Development model. Here’s the structure:
- GP = Developer + Friend/Family
- Developer – 10% of GP share of equity required
- Friends/Family – 90% of GP share of equity required
- 12% preferred return split 10/90
- All excess cash flow above a 12% IRR split 50/50
- LP = Limited Partner
- GP – 10% of total equity required
- LP – 90% of total equity required
- 8% preferred return split 10/90
- 20/80 split to a 12% IRR
- 30/70 split to a 15% IRR
- 40/60 thereafter
Quick Tutorial – Toggling Between Fixed and Variable Interest Rates
A 50 second tutorial on how to toggle between fixed and variable rate construction loans.
Using the Permanent Debt Module
In version 2.0 of the model, I added a Permanent Debt Module. What this means is you now have the option to model a build-to-core scenario (i.e. build-lease-hold) rather than just merchant-build scenarios (i.e. build-lease-sell).
Solve for a Specific Construction Loan Amount
This is another tutorial in a growing series on using the Apartment Development model. In this video, I use the model to assess a hypothetical build-to-core (i.e. build, lease, hold long-term) ground-up development opportunity.
- Click here to download the assumptions used in this example.
Adding a Mezzanine Loan to the Construction Debt
In version 2.4 of the model, I added a Mezzanine Loan module in order to model mezz debt in your apartment development scenarios. Below find video instructions for using that module.
Note that the mezz debt facility is assumed to be a) interest-only, with unpaid interest accruing to an interest reserve account, and b) paid off simultaneously with the construction loan at sale or refinance.
Choosing the Funding Order of the Construction Debt
In version 2.5 of the model, we expanded on the Mezzanine Debt feature to allow for the user to choose the funding order of the two tranches of construction debt. In this video, I show you how to use that feature.
In version 2.7, I made significant changes to the recalculate logic and backend VBA for that feature. I also added a basic troubleshooting feature that tells you if you have a circular reference in your model that is causing the Recalculate button not to work. Watch to learn more about using this feature.