Watch Me Build a Tenant Rollover Analysis Model
The feedback has been positive on the ‘Watch Me Build a Multifamily Model’ video I recorded earlier this year. So I thought I’d follow that up with another. This time I build a Real Estate Tenant Rollover Analysis Model and talk through the methodology, keyboard shortcuts, and formulas I use as I build the model.
In addition to the written instructions and video tutorial, I’ve included the option to download a pre-formatted template worksheet as well as a completed worksheet.
Are you an Accelerator member? See the ‘Modeling Short-Term and Long-Term Leases course in the core curriculum. Additionally, if you’re an Accelerator Advanced Member check out course 4, lesson 7 of the ‘Advanced Modeling – Property and Portfolio’ endorsement for additional techniques for modeling leases. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
Watch Me Build – Performing tenant rollover analysis
What is Tenant Rollover Analysis?
Tenant rollover risk is the possibility that a tenant will vacate its space at the end of its lease term. Tenant rollover analysis describes the process by which the real estate investment professional identifies the lease expiration date of each tenant in a property’s rent roll and then analyzes how much rollover risk exists in each year of the proposed hold period.
So for instance, if you’re considering buying a strip center with ten tenants, you’ll first want to determine when each existing tenant’s lease expires. You’ll also want to estimate when future tenants’ leases might expire. Then with this data, you’ll aggregate the expiring square footage (or gross income) in each year and assess which years have greater exposure to rollover.
Why Analyze Tenant Rollover?
The value of any commercial real estate asset is a product of its future cash flows and the level of certainty that those cash flows will occur. The less certain an investor is that the cash flows will occur, the greater the return the investor expects.
When an investor is analyzing whether to purchase a real estate investment, the investor will perform various analyses to estimate the potential future cash flows and vet the likelihood that those cash flows will occur.
One area of uncertainty is in tenant lease expiration. The more leases that expire, either in terms of square footage or gross income, the greater the uncertainty. The Tenant Rollover Analysis is a great tool for assessing how much rollover risk exists in an investment.
How this Rollover Analysis Model Works
The rollover model I build in this video is basic, but handles the most important aspects of rollover analysis. The outputs that I’m working towards in this model are:
- the average remaining lease term of the existing rent roll,
- the percent of total net rentable area expiring in any given year,
- the cumulative percent of total net rentable area having expired from year to year as the hold period progresses
I arrive at the above outputs by taking the following steps:
- I first drop in the basic rent roll assumptions needed to perform this analysis; namely lease start and end dates, lease term length, net rentable area occupied by each tenant, and future lease assumptions including downtime between leases and average lease length.
- I then model the various lease generations’ start and end dates, meaning I estimate when present and future leases will start and end using the assumptions in step 1.
- Next, I model the hold period by month. I write logic that asks, is there a lease expiring in this month and if so, set that cell to the net rentable area for that tenant, if not set that cell to zero.
- Finally, I add up the expiring square footage values by month and then by year.
Having performed the above steps, calculating the desired outputs is fairly simple. In my video, I finish by creating a column chart to visualize the rollover by year – a nice way to present your findings to partners, lenders, and friends.
Download the Template and Completed File
To get the most out of this video, I suggest you use the practice model (download link below) and build the model yourself as I go. The download link includes a Workbook containing both a pre-formatted blank template worksheet and a completed worksheet. The practice worksheet has identical formatting to the completed worksheet, but the formulas have been deleted.
To make these modules accessible to everyone, they are 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 – similar real estate training exercises sell for $100+). 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.
Follow Along with the Video – Tenant Rollover Analysis
Open up a blank Excel workbook (or use the pre-formatted template provided) and follow along as I build a tenant rollover analysis model.
