Student Housing Acquisition Model (Updated Apr 2023)

I’ve spent a fair amount of my career analyzing privately-owned student housing investments. Nevertheless, I’ve yet to share a purpose-built, institutional-quality student housing acquisition model. So, I thought I’d take some time and build a model for this very purpose.

To do this, I took my Value-Add Apartment Acquisition model, which has been used by tens of thousands of people and has gone through dozens of revisions, and adapted it to create this student housing acquisition model. This pro forma in Excel seeks to capture the nuance of core, core-plus, and value-add student housing analysis. I hope you’re able to make good use of this model!

Note: While this model is built on the core functionality of an Excel model that has been crowd-tested, it still undoubtedly contains errors. You’ll also likely find features missing that you’d like added. Version 1.2, in particular, contains a significant number of changes. Therefore, if you find a bug or have a feature you’d like added, shoot me a note and I’ll look to include your feedback in a future version of the model.

Basic Objectives – Student Housing Acquisition Model

The general layout and design of the Student Housing Acquisition model (and many other newer real estate models shared here at A.CRE) follow the one-page underwriting format I first shared with my Apartment Development model. This should be a familiar format for many of you – especially those that are learning to build models from scratch in our real estate Accelerator program.

And in particular, those who are proficient using my Value-Add Apartment Acquisition model will be able to jump in and immediately use this model.

As with my other one-page underwriting models, nearly 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. All other tabs are either optional or for tracking version changes.

Overview – Student Housing Acquisition Model

The Student Housing Acquisition Model includes one primary inputs tab, one primary report tab, an optional annual cash flow tab, an optional detailed expenses 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)

The annual cash flow 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 Walkthrough of the Student Housing Acquisition Model

I’ve created a video walkthrough to help you navigate the Student Housing Acquisition Model, and to understand the key differences between my Value-Add Apartment Acquisition Model and this model. This walkthrough video is based on v1.0 of the model. You can find that walkthrough here:

Video Tutorials for Using the Value-Add Apartment Acquisition Model

As mentioned, this Student Housing Acquisition Model is an adaptation of my Value-Add Apartment Acquisition model. I’ve created a lengthy series of tutorial videos for that model, which will help you better use this model. You can find those tutorials here:

---

Features Included in the Student Housing Acquisition 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
• Fixed or variable rate 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
• GP Fees module
• Renovation schedule with option to link hard and soft costs to renovation schedule
• Downtime Vacancy due to vacancy module
• Student Housing lease timing logic (i.e. all new rents start at the beginning of the academic year)

Compatibility

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 Student Housing 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.

---

Version Notes

v1.2

• Expanded the frozen rows on the Detail Expense worksheet
• Fixed issue where cell F17 and G17 on Detail Expenses were hard coded values rather than SUM()
• Combined ‘Units_SF’ and ‘Units_SF_M2’ named ranges into one
• Moved Beds and NRA values on the Detail Expenses into the frozen section in row 3
• Added “/Yr” label to Detail Expense headings
• Fixed ‘Controllable’ navigation button on Detail Expenses to take user to top of Controllable section
• Revised OpEx input logic to use /Bed/Month rather than /Bed/Year
  • Edited OpEx_Detailed() macro to pull /Bed/Year values from Detail Expenses and then convert to /Bed/Month
  • Edited Operating Expense cash flow columns on Underwriting tab
  • Updated labels on Underwriting tab to /Bed/Month
• Fixed cell L203 on Underwriting tab to be black rather than blue
• Fixed Stabilization calculation formula (cell E167 Underwriting) to reflect Student Housing leasing schedule
• Revised Sale Month calculation to pull from cell E167 Underwriting rather than Lease Up inputs
• Added a Pre and Post /Bed/Yr metric in columns N and O of the Operating Income section
• Added an In-Place DSCR (Debt Service Coverage Ratio) and DY (Debt Yield) metrics to the Debt section (E69 and G69) to help size the loan amount
• Changed Begin and Complete Roll logic to only roll units at academic year completion
  • Created list of analysis months when roll occurs (see cell I272 Underwriting)
  • Updated Begin Roll and Complete Roll months input cells to use a list that pulls from the values starting in cell I272
• Fixed ‘Effective Rent/SF/MO’ calculation (row 104 Underwriting)
• Updated the SOFR yield curve
• Various placeholder updates

v1.1

• Fixed issue where ‘Units’ on the Summary tab was linked to ‘Beds’
• Fixed issue where date wrong was off in backend calculation on Underwriting tab
• Added ‘Year Built’ to Summary tab
• Added date row to locked header on Underwriting tab
• Rebuilt lease-up module to only renovate at new ‘Lease Year’ (i.e. new academic year)
• Added ‘Return on Value-Add Cost’ metric to Summary tab
• Misc. updates to formatting and placeholder values

v1.0

• Initial release
• Adaptation of Value-Add Apartment Acquisition Model
  • Revised Unit vs Bed logic
  • Updated Add/delete row macros
  • Updated growth logic to grow rents upon start of new academic year
  • Various formatting updates
  • Various heading/label updates
  • Added two new sections to Summary tab