In this Watch Me Build video I show you how to build a custom-made rent schedule module for long-term leases. The foundation of the module is an easy INDEX/MATCH combination that takes a rent schedule and models the lease cash flows across the whole hold duration.
I also include an optional file that you can download and use to follow together with the video. That file consists of both a 'Template' and 'Completed' tab. You're also welcome to take the finished variation, and include it straight into your own design.
Are you an Accelerator member? See the 'Modeling Short-Term and Long-Term Leases course in the curriculum. Additionally, if you're an Accelerator Advanced Member take a look at course 4, lesson 7 of the 'Advanced Modeling - Residential Or Commercial Property and Portfolio' endorsement for extra methods for modeling leases. Not yet an Accelerator member? Consider joining the realty financial modeling training program used by leading property companies and elite universities to train the next generation of CRE experts.
A lease schedule is a table that notes out the base rent by date. So for example, 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 lease schedule is consisted of in the lease to outline the base rent paid by the occupant. So using the lease schedule above, we understand that the tenant pays $382,500 lease in 2022 ($ 31,875/ month). The rent schedule hence ends up being a crucial input for modeling the prospective rent of a residential or commercial property.
A rent schedule module is a component of a real estate design that forecasts the potential rental income over the hold duration. So using the example above, the rent schedule module would take the rent schedule and determine exactly the lease for every single duration. And preferably the module would do this in a vibrant way.
The module will include a lease roll noting the prominent inputs for each tenant. So for instance, the lease roll would include renter name, suite, rented area, lease start date, lease end date, existing rent, and so on. Each occupant would then have a lease schedule that outlines how the base rent modifications gradually.
The module then takes the lease roll, the rent schedule, and models the real capital for each period.
Quick Note: Not interested in DIY analysis? Consider dealing with A.CRE Consulting to manage your bespoke modeling task.
Watch Me Build Video - Partitioning the IRR in Real Estate
So in this Watch Me Build video, I reveal you how to construct a lease schedule module for a long-term lease. I initially drop in an export of a rent roll. From there, I include a lease roll for a single tenant (National Insulation, Inc.) and connect the relative details from the lease schedule to the rent roll.
Next, I design out the lease by month over a 132 month analysis period. I use boolean logic to only model lease in periods where the lease is in force. And I utilize an INDEX/MATCH mix to draw in the rent from the lease schedule based on the period.
The result is a fast and simple way to dynamically design a rent schedule.
Download the Rent Schedule Module Exercise File and Module
To make this tool accessible to everyone, it is provided on a "Pay What You're Able" basis without any minimum (enter $0 if you 'd like) or optimum (your assistance assists keep the material coming - normal property Excel modules cost $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 provide our models on this basis, please reach out to either Mike or Spencer.
We routinely update the design (see version notes). Paid factors to the design receive a new download link through email each time the design is updated.
Bonus: Watch Me Model Rent Schedule Cash Flows Using AI
In this fast bonus offer video, I utilize ChatGPT to design the regular monthly cash flow for this very same lease.
Frequently Asked Questions about Modeling a Lease Schedule in Excel and with AI
What is a lease schedule?
A lease schedule is a table that lists the base rent by date throughout the lease term. For example, it might reveal yearly lease increases for a renter on a long-term lease, such as $337,500 in 2018, $348,750 in 2019, and so on. It becomes a key input for modeling rental income.
What is a rent schedule module?
A rent schedule module belongs of a property model that anticipates rental income over the hold duration. It uses occupant lease information from the rent roll and a rent schedule to design lease money flows dynamically for each analysis period.
How is rent designed across time in this module?
Rent is modeled using a 132-month analysis duration. Boolean logic ensures rent is just shown when the lease is active. An INDEX/MATCH formula is then used to pull the appropriate lease from the rent schedule based on the period.
What Excel methods are utilized in the lease schedule module?
The model primarily uses the INDEX/MATCH mix to pull lease values and boolean logic to restrict lease flows to the lease duration. These solutions ensure the module remains dynamic and scalable for longer leases or several renters.
What occupant is utilized in the example model?
The example model uses a tenant called National Insulation, Inc. The rent schedule and lease details for this single renter are utilized to show how to structure and compute lease cash flows.
Is a downloadable file supplied with the tutorial?
Yes. A downloadable file is consisted of with both a 'Template' and 'Completed' version of the model. It is used on a "Pay What You're Able" basis. Contributors get update e-mails when new variations are launched.
How is AI utilized in modeling rent money flows?
A reward video demonstrates how ChatGPT is utilized to model the very same rent money flow as performed in Excel. This showcases how AI can help in structure custom modeling logic using natural language triggers.
Where can I discover more about lease modeling thorough?
Accelerator members can explore detailed lease modeling in the 'Modeling Short-Term and Long-Term Leases' course. Advanced members can dive deeper by means of Course 4, Lesson 7 of the 'Advanced Modeling - Residential Or Commercial Property and Portfolio' endorsement.
Version Notes
v1.01
- Misc. fixes and updates to Versions tab
v1.0
- Initial release
About the Author: Spencer Burton is Co-Founder and CEO of CRE Agents, an AI-powered platform training digital colleagues for industrial realty. He has 20+ years of CRE experience and has underwritten over $30 billion in realty throughout top institutional companies.
