Multifamily (Apartment) Acquisition Model (Updated Dec 2020)
Our library of real estate Excel models has a variety of decent apartment models, including our All-in-One model and our Apartment Development Model – both robust models in their own right. However, if you’re looking for a straight multifamily (apartment) acquisition model, and don’t want all of the unnecessary components of the All-in-One (ie. office, retail, industrial modules), this is a decent model to take a look at.
Note: This model is NOT compatible with Excel for Mac. However, since building this model, I’ve released a more robust alternative called the A.CRE Value-Add Apartment Acquisition Model. I recommend you look at that model instead.
History of the Apartment Acquisition Model
This model came about more by chance than anything. I’d recently built an office, retail, and industrial valuation model, and one of our readers asked to alter the model to work with an apartment project. Removing the office/retail/industrial operating module and replacing it with an apartment operating module turned out to be a bit more work than I’d initially anticipated! But eventually, the adaptation was done and this Apartment Acquisition Model was born.
Since that time, various updates (see version notes at the end of this post) have been made, including the addition of a value-add module (in-place vs market rent), and various other enhancements and bug fixes. As with all of our models, this model may contain errors and certainly can be improved on. So if you spot a bug or have a feature request, please let me know and I’ll roll out an update.
Underwriting an apartment acquisition model
About the Apartment Acquisition Model
The project started out simple enough, but became more complex as I came to the conclusion that I needed to add several modules that are specific to apartments. I’ve also since built a version of this model that includes stochastic assumptions and a monte carlo simulation module.
The model is similar in many respects to the office/retail/industrial acquisition model, only the DCF is far more robust. I’ve added several calculation modules to model free rent, releasing costs, lease-up days between leases, unit types, etc.
For value-add situations, the model has the option to enter a market rental rate different from an in-place rental rate, with an assumption that determines when rent will roll to market. In addition, on the Expense tab the model includes a ‘Renovation/Improvement Cost’ line item for entering renovation costs.
The debt module is fairly simple (no monthly amortization shown), but it could be easily enhanced if such a situation warranted it. I’ve included a the latest version of my real estate equity waterfall model, with both IRR and equity multiple hurdles, to calculate partnership level returns.
How to Use the Apartment Acquisition Model – A Video Tutorial
I’ve recorded a video of how to use the model, during which I assess an actual property from Loopnet that was for sale at the time of the video. Keep in mind that I’m not at all familiar with the property or the sub-market, and thus many of the assumptions are made up. So take the resulting value with a big grain of salt. Nevertheless, the exercise should be more interesting and valuable this way than how I’ve done previous tutorial videos where I’ve simply walked you through the inputs and mechanics of the model.
Also keep in mind, the video was recorded using v1.0 of the model. Since recording this tutorial and as mentioned above, a few things have changed – see version notes below for complete list of changes. The core functionality of the model though is the same.
A few quick notes about the model
- This multifamily financial model includes 15 tabs, of which three are input tabs, four are output tabs, two are organization tabs, and six are calculation module tabs.
- The model allows for up to 34 unit types.
- The debt module is not overly sophisticated, only allowing for senior debt and not displaying monthly amortization. If you need a more complex debt module, try adding our advanced real estate amortization table.
- Value-add options are found on the MF Rent Roll tab (In-Place Rent, Market Rent, Month to Roll to Market) as well as on the Expense tab (Property Renovation/Improvement Costs)
- For instructions on how to model partnership level returns using the Investor Returns tab, you might check this out
Important Note for Excel for Mac users: This model is not compatible with Excel for Mac. We’re not entirely sure why, as all of the rest of our models have no issue running on Excel for Mac. We are in the process of rolling out an alternative apartment acquisition model that will be compatible for users of PC and Mac alike.
Download the Multifamily (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 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
v2.3
- Improved Sponsor Distribution formula on Investor Returns tab to make it easier to audit
- Added option to toggle between SF and M2 (cell H2 on the Property Summary tab)
- Misc. formatting enhancements
v2.2
- Added new Versions tab
- Fixed ‘Capitalization’ box on Property Summary tab; now properly links to Investor Returns tab
- Updated default ‘Purchase Price Method’ to ‘Manual Input’ on Property Summary tab
- Updated default ‘Loan Amount’ to include formula linked to ‘Purchase Price’
- Moved LTV and LTC metrics immediately below ‘Loan Amount’ input
v2.1
- Added logic such that if LP is excluded from analysis, outputs result in NA instead of #Value! Errors
- Updated Equity Multiple logic on both property-level and partnership-level returns
v2.02
- Updated promote structure language to avoid confusion
- Fixed formula on Property Returns tab (R12) that caused the model to error our when modeling a 15 year hold
v2.0
- Misc formatting improvements
- Added In-Place and Market Rent assumptions on MF Rent Roll tab for value-add opportunities
- Added ‘Roll to Market Rent’ assumption on MF Rent Roll tab for value-add opportunities
- Used Absolute Value formula to ensure OpEx is negative on Annual Cash Flow tab
- Removed $ USD signs to give the model a cleaner look; make it more compatible with other currencies
- Updated Investor Returns tab with waterfall that includes option for IRR or equity multiple hurdles
- Added ‘Property Renovation/Improvement Costs’ input on the Operating Expenses tab for value-add situations
- Renamed ‘Operating Expenses’ tab, ‘Expenses’ to reflect non-operating expense assumptions
V1.15
- Loan amount changed to an input cell
- Added LTV and LTC to the Property Summary tab
V1.13
- Corrected error in debt service calculation
- Fixed minor formatting issues
- Moved Investor Returns inputs to the Property Summary tab
- Added a few ratios to the Annual Cash Flow tab
