Create A Dynamic Revenue Row to Calculate Multiple Tenant Leases (Updated Nov 2022)
I recently received an email from one of our readers asking how to create a dynamic revenue row for a pro forma that can capture rent changes for multiple tenants. I thought that this would make a great post for the site and would be a good piece of information to share with our readers. What makes this a bit challenging to figure out is that all the tenants will most likely have rent increases at different time periods and the rent for each tenant will also increase at different rates.
The way many people may begin to approach this problem is by asking themselves ‘how can I create a formula that will capture everything I need to capture in one row and with one formula?’ This question in itself presents the first road block to conquering this challenging problem as well as other complex excel problems you may face when modeling for real estate deals. Of course, it is almost always possible to use brute force and hammer out a 20-line formula that may take you a few hours and may or may not contain errors, but is this really the best way?
Also in this Post:
- In Depth Explanation of the INDEX MATCH function
- Excel Modeling Philosophy for Complicated Scenarios
Breaking Down a Dynamic Revenue to Calculate Multiple Tenant Leases
I’d like to offer a different way to think about complex modeling challenges that was imparted on both Spencer and I in our early days of grad school by Professor Daniel Lebret. One of the most valuable takeaways from his teachings was to think about how to break out these complex problems in to simpler components. Essentially, solve the pieces to your total problem individually and then link them back together once they are solved. If this means creating a few external tables in your model, then so be it. In fact, it is preferable. Although we might feel great when and if we get that 20-line formula to work, the last thing a third party wants to do when trying to understand your financial underwriting is click on a cell and have to decipher an insanely long and confusing formula.
It is in that spirit that this exercise was created. The essential components to figure out this issue are all broken out in the Excel sheet and then linked together in the revenue row with one simple formula. To clarify, each tenant’s essential rent information is laid out in individual tables and the dates are displayed above the revenue row. Then they are all linked in the revenue row with a simple formula.
The downloadable excel file below along with the video guide will walk you through how to go about modeling this. The exercise contains inputs for two tenants, both have a rent start date and two rent bump dates. You should be able to easily add additional tenants to this model if you so desire and I mention how to do this towards the end of the video.
Using the INDEX + MATCH Functions
The exercise should be fairly simple to understand and primarily takes advantage of the INDEX MATCH formula. The INDEX MATCH function works much in the same way as VLOOKUP, but is a great alternative and worth mastering. If you want to understand why this formula is preferable over VLOOKUP, check out what ExcelUser & mbaexcel say about it.
Additionally, if you are new to INDEX MATCH, I explain it thoroughly in the video with regards to how it works within this particular model. However, there is additional information that is important to understand about INDEX MATCH, so I decided to create a second video that goes in depth with how INDEX MATCH works, which is also included within this post.
The excel file in this post contains two sheets. The first sheet is the dynamic revenue exercise and the second sheet explains in detail how INDEX MATCH works. The file and two corresponding videos are below.
Video Tutorials for the Dynamic Revenue Row to Calculate Multiple Tenant Leases tool
Capturing Multiple Leases In a Revenue Row
Understanding INDEX MATCH
Dynamic Revenue Row to Calculate Multiple Tenant Leases
- How to model for multiple tenant leases and calculate them together in the revenue row of your proforma
- An in depth understanding of the INDEX MATCH function.
Compatibility
This version of the module is only compatible with Excel 2013, Excel 2016, and Excel 365.
Download the Dynamic Revenue Row To Calculate Multiple Tenant Leases tool
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 tools sell for $25 – $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 file (see version notes). Paid contributors to the tool receive a new download link via email each time the tool is updated.
I hope you find this post valuable and as always, contact me with any questions or comments.
