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.
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.
Use Practice Model and Follow Along with the Video
To get the most out of this video, I suggest you use the practice model (download below) and build the model yourself as I go. The download link includes a zip file containing both the practice model and the completed model. The practice model has identical formatting to the completed model, but the formulas have been deleted.
If you have any questions or comments, please don’t hesitate to reach out to either Mike or I.
Download The 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 models sell for $100 - $300 each). 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.
Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.
Tenant Rollover Analysis Model
- The download is a zip file containing two Excel Workbooks
- One Excel file includes just formatting, to use when watching the "Watch Me Build - Rollover Analysis Model" video
- The second Excel file includes the completed model with formulas included
- Models the square footage expiring of three generations of tenants on a five tenant rent roll
- Meant as a template for one way of modeling tenant rollover risk
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software. www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.
www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:
1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.
Follow Along with the Video
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.