We’ve shared a handful of apartment models over the years. Several of those are capable of analyzing apartment acquisitions (e.g. All-in-One, Apartment Valuation Model), but none was built for the express purpose of modeling value-add apartment deals. As a result, each has its limitations in value-add scenarios. So today, I’m excited to share a new apartment model; this one built specifically for value-add apartment investments.
Note: As of v0.4, the model now a detailed operating expense module, an operating history analysis tool, and buttons to automatically add/delete budget line items.
History of this Purpose-Built Value-Add Apartment Model
About a year ago, in response to numerous reader requests, I modified the aforementioned apartment valuation model to accommodate value-add situations. While it now technically can accommodate a buy, renovate, raise rents, and sell scenario, it lacks a lot of the features value-add investors need in an analysis tool. Plus, that model is also not compatible with Mac for Excel. So I knew I needed to roll out a true value-add apartment model at some point.
The challenge with getting this released has been time. With a full-time job and family, I’m limited to nights and weekends to take on new projects. And considering Michael and I have been consumed over the last year with creating and launching our signature A.CRE Accelerator real estate financial modeling training program, this model has been slow to come to fruition.
What really helped make this happen was our final capstone course in the Accelerator. In that course, the case study involves a hypothetical REPE technical interview in which the student must build a value-add model from scratch. While I was creating the Watch Me Build tutorial late last year for that course, I developed various apartment value-add calculation modules that I was able to incorporate into this model. That exercise was ultimately the catalyst for this model.
Are you an Accelerator member? Check out the value-add apartment model Watch Me Build tutorial.
Basic Objectives – A.CRE Value-Add Apartment Acquisition Model
Given that the general layout and design of the Value-Add Apartment Acquisition model was inspired by my Apartment Development model, all of the criteria that drove the process remained the same. I wanted the model to be robust – meaning it had to be able to do everything an institutional-quality value-add model can do, while being simple and intuitive.
The result is a model where 100% of inputs are on one, easy to use tab (i.e. worksheet) – the Underwriting tab. Cash flows are shown immediately to the right of the inputs on the Underwriting tab, and the outcomes are reported on an attractive and printer-friendly Summary tab. The only other tab (for now) is a Versions tab, where changes to the model are listed.
With that said, in the coming months I’ll be releasing enhancements to the model that will likely increase the number of worksheets in this model. The list of features I plan to add are detailed later in this post.
Overview – A.CRE Value-Add Apartment Acquisition Model
The A.CRE Value-Add Apartment Model includes one primary inputs tab, one report 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 ‘Investment Description’, ‘Investment Cash Flows’, ‘Operating Cash Flows’, ‘Reversion Cash Flows’, ‘Property-Level Returns’, and ‘Partnership-Level Returns (Waterfall)’.
Summary Tab (Visible by default)
While property-level return metrics levered IRR and levered EMx are 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 two operating cash flows 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 Tab (Hidden by default)
In beta v0.3, 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.
Detail Expenses (Hidden by default)
On the Underwriting tab the user has the option to toggle a detailed operating expenses mode. Rather than entering per unit values for pre-renovation (i.e in-place) and post-revonation (i.e. stabilized) 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.
Video Tutorials for Using the Value-Add Apartment Acquisition Model
I am in the process of building out a series of video tutorials for using the model. You can find those tutorials here:
Features Included in the Model
Here is a list of a few of the features included in this model:
- Dynamic analysis period up to 120 months (minimum 12 months)
- Model renovation costs on a straight-line, s-curve, or manually
- Up to four sources in your capital stack, two equity and two debt
- Choose the order in which sources are deployed (e.g. GP equity first, LP equity second, Senior Loan 3rd, Junior Loan 4th)
- Option to either refinance or sell at stabilization
- Robust operating cash flow module, with differentiation between in-place and stabilized (i.e. fully renovated) income and expenses
- Compare In-Place and Stabilized pro formas and return metrics on a untrended (i.e. without income/expense growth) or trended basis
- 4-tier, European waterfall (i.e. LP return of capital first before GP is paid a promote) with annual compounding IRR hurdles
- Key assumptions and return metrics, strengths and weaknesses, and investment description on the Summary tab
- Fully dynamic charts on Summary tab
- Detailed expense module
- Operating history analysis
- Buttons to automatically add/delete budget line items
Features on the Way
Below is a list of features I plan to add to the model in the coming months:
Button to automatically add/delete cost line items
- Construction forecasting automatically and dynamically linked to renovation schedule
New module (separate Worksheet) to detail income and expense line items, compare underwriting to historical operating statements
- New module to add a small amount of retail
Button to automatically add/delete unit types, other income line items, and expense line items
- Sensitivity analysis
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365
The 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.
Download the A.CRE Value-Add Apartment Acquisition 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 acquisition models sell for $200 – $500+ 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.
- Fixed issue where contributions from equity was not properly covering operating shortfall in waterfall calculation
- Added option to model Asset Management Fees as either ‘% of EGR’ or as ‘% of LP Equity’ (cell F221 on Underwriting tab)
- Fixed debt yield calculation in ‘Subsequent Financing’ section
- Created ‘Fixed Roll to Market %’ metric for each unit type to fix issue related to lease-up rent not calculating properly
- Minor update to how ‘Non-Revenue Model Units’ Pre-Reno amount is calculated in In-Place pro forma
- Misc. formatting improvements
- Added option to automatically add/delete project budget line items
- Fixed issue where Management Fee assumptions disappeared when Vacancy was set to ‘Detailed’
- Added Detailed Operating Expense module
- Created toggle buttons in row 125 to switch between Basic and Detailed operating expense underwriting
- Wrote macro so that ‘Detail Expense’ tab appears when toggle is set to ‘Detailed’
- Built ‘Detail Expense’ tab with Pre-Renovation and Post-Renovation assumptions
- Wrote macro to link assumptions on Underwriting tab to assumptions on Detail Expense tab when ‘Detailed’ is toggled
- Added ‘Operating History Vs. Underwriting’ analysis section to Detail Expenses
- Misc. formatting fixes and enhancements
- Revised growth calculation formulas such that growth year 1 begins in month 1 rather than month 13
- Forcing growth assumptions to start year 2, such that no growth is assumed in year 1 (i.e. set Growth Year 1 to black font cells)
- Added note below growth assumptions on ‘Underwriting’ tab clarify that growth begins month 13
- Fixed issue where Post-Renovation operating expenses were not growing per the correct assumptions
- Changed ROI output on Summary tab to ROI/Yr
- Added Mill Rate output under Reversion section of Underwriting tab
- Added Rent/SF and Rent/Unit for Total Effective Rental Income on Underwriting tab
- Added new ‘Annual Cash Flow’ report tab
- Incudes toggle on ‘Summary’ tab in row 34
- Includes two new macros that hide/show the ‘Annual Cash Flow’ tab and toggle the Hide/Show buttons
- Includes summary cash flow report, with monthly cash flows rolled up to annual periods, on the ‘Annual Cash Flow’ tab
- All return metrics are still calculated on a monthly, not annual, basis
- Updated header in ‘Returns’ section to make dates/months dynamic to hold period
- Fixed error where OpEx and CapEx were growing off of Other Income growth rate assumptions
- Removed data validation lists from K73 and K74 on the Underwriting tab
- Fixed issue where Initial Fund months were not displaying correctly (J73 and J74 of Underwriting tab)
- Removed data validation lists from K73 and K74 on the Underwriting tab
- Broke out Credit Loss (i.e. Bad Debt) from General Vacancy
- Fixed error where GP and LP ROI calculations on Summary tab were reversed
- Changed ‘Asset Management Fee – Max’ language to make it easier to understand; now asks “Fee Accrues” with response Yes or No
- Simplified AM Fee/Mo formula
- Added ‘Term’ to Subsequent Financing; note that Subsequent Financing Term is NOT an assumption but rather tied to Analysis End month
- Updated various input cells to include data validation
- Added option to model GP IRR Catch Up
- Created drop-down menu in cell F222 to toggle GP Catch Up on or off (i.e. GP Catch Up toggle)
- Added conditional formatting where GP Catch Up toggle becomes available when GP distribution % in pref tier is less than GP contribution %
- Updated waterfall module to include GP Catch Up calculation
- Added option to model GP asset management fee
- Included assumption in cell F217 to enter annual asset management fee %; paid out monthly per cell G217
- Added assumption (Max): ‘None’ means AM fee shortfall accrues to GP, ‘Available’ means GP is paid lesser of AM fee or available CF
- Updated waterfall module to include GP AM fees
- Added misc. labels to help better navigate model
- Added Cash-on-Cash return metric to Underwriting and Summary tabs
- Fixed issue where two GP ownership assumptions existed
- Added option to perform GP-only analysis (i.e. exclude LP)
- Updated GP and LP equity multiple formula to match logic used for property-level equity multiple formula
- Removed property-level ROI formula
- Fixed issue where Market Cap Rate in Development Spread calculation was not dynamic to ‘Untrended’ vs ‘Trended’
- Fixed issue where the ‘As of mo.’ output in the Reversion section was not displaying the month correctly for the ‘Stabilized’ pro forma
- Added navigation links at top of Underwriting tab
- Added ‘Refinance vs Sell’ toggle
- Repositioned debt cash flows to accommodate hiding Subsequent financing rows when model is set to ‘Sell’ mode
- Misc. logic fixes
- Misc. formatting improvements
- Initial release of the Value-Add Apartment Acquisition Model
About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.