Watch Me Build – Modeling a Rent Schedule in Both Excel and Using AI (Updated May 2024)
In this Watch Me Build video I show you how to build a custom rent schedule module for long-term leases. The foundation of the module is a simple INDEX/MATCH combination that takes a rent schedule and models the rent cash flows across the entire hold period.
I also include an optional file that you can download and use to follow along with the video. That file includes both a ‘Template’ and ‘Completed’ tab. You’re also welcome to take the completed version, and incorporate it directly into your own model.
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.
What is a rent schedule?
A rent schedule is a table that lists out the base rent by date. So for instance, the rent schedule for a 75,000 SF spaced leased to an industrial on a 7-yr lease with 0.15/SF bumps every year.
Rent/Yr
-
- Jan 1, 2018 – $337,500
- Jan 1, 2019 – $348,750
- Jan 1, 2020 – $360,000
- Jan 1, 2021 – $371,250
- Jan 1, 2022 – $382,500
- Jan 1, 2023 – $393,750
The rent schedule is included in the lease to outline the base rent paid by the tenant. So using the rent schedule above, we know that the tenant pays $382,500 rent in 2022 ($31,875/month). The rent schedule thus becomes an important input for modeling the potential rent of a property.
What is a rent schedule module?
A rent schedule module is a component of a real estate model that forecasts the potential rental income over the hold period. So using the example above, the rent schedule module would take the rent schedule and calculate exactly the rent for every period. And ideally the module would do this in a dynamic way.
The module will include a rent roll listing the salient inputs for each tenant. So for instance, the rent roll would include tenant name, suite, leased area, lease start date, lease end date, current rent, etc. Each tenant would then have a rent schedule that outlines how the base rent changes over time.
The module then takes the rent roll, the rent schedule, and models the actual cash flow for each period.
Watch Me Build Video – Partitioning the IRR in Real Estate
So in this Watch Me Build video, I show you how to build a rent schedule module for a long-term lease. I first drop in an export of a rent roll. From there, I add a rent roll for a single tenant (National Insulation, Inc.) and link the relative information from the rent schedule to the rent roll.
Next, I model out the rent by month over a 132 month analysis period. I use boolean logic to only model rent in periods where the lease is in force. And I use an INDEX/MATCH combination to pull in the rent from the rent schedule based on the period.
The result is a quick and simple way to dynamically model a rent schedule.
Download the Rent Schedule Module Exercise File and Module
To make this tool 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 Excel modules sell for $100+ 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.
Bonus: Watch Me Model Rent Schedule Cash Flows Using AI
In this quick bonus video, I use ChatGPT to model the monthly cash flow for this same lease.
Version Notes
v1.01
- Misc. fixes and updates to Versions tab
v1.0
- Initial release
