Arguably the most powerful, and least appreciated and understood, functionality in Excel is its use of boolean logic. Or in other words, the use of TRUE and FALSE binary logic. This form of logic can be used to dramatically simplify formulas in real estate financial modeling, ease auditing work, and speed up the modeling process. In this post, I’ll show you an example of Boolean logic to quickly and dynamically model multiple generations of tenant improvements.
What Do I Mean by Multiple Generations of Tenant Improvements
When I speak of 1st generation, 2nd generation, and 3rd generation tenants, what I’m speaking of is the succession of one lease to the next. So the 1st generation tenant would be the first lease in an analysis, with each subsequent lease being a subsequent generation. Consequently, each generation has its own unique lease characteristics (e.g. leasing costs, free rent, contract rent, etc) that can be accurately modeled by tracking when each generation begins and ends.
In other words, in order to model the cash flow events that occur at the inception of a new lease, or the cash flow events that occur at the beginning of a new generation of tenant, it’s important to calculate when each generation of lease starts and ends. And when we successfully build a formula that tells us when a new lease occurs, we can then easily model cash flow events that occur at these points; such as the payment of tenant improvements at the inception of a new lease.
What is Boolean Logic in Microsoft Excel
Per the interwebs:
“Boolean logic is “a form of algebra in which all values are reduced to either TRUE or FALSE. Boolean logic is especially important for computer science because it fits nicely with the binary numbering system, in which each bit has a value of either 1 or 0. Another way of looking at it is that each bit has a value of either TRUE or FALSE.”
As you’ve probably noticed, Microsoft Excel makes common use of this concept. Ask formulaically, does one cell equal another, and Excel will return either a TRUE or a FALSE result. What you may not know, is that the TRUE result that Excel spits out is equal to 1, and the FALSE result is equal to 0. Such that, if you multiple the TRUE by a given value, the result will be equal to the given value (1 x any number = that number). And likewise, if you multiple the FALSE by a given value, the result will always be 0 ( 0 x any number = 0).
When used effectively in your formulas, Boolean logic (TRUE or FALSE logic) can greatly shrink the size of your formulas and simplify your logic. Gone are the days of stupidly long nested IF statements when you learn to effectively use Boolean logic.
A Method for Modeling Multiple Generations of Tenant Improvements
If you’ve spent much time modeling real estate, you know how cumbersome modeling multiple generations of leases can be. As each tenant rolls, a new set of TIs, LCs, downtime, and free rent must be charged to the cash flows with the amount highly dependent on a renewal probability. On top of that, an appropriate rental rate and reimbursement method must be assigned to the new lease and modeled accurately.
And while this post is not meant to cover all of the nuances of dynamically modeling a multi-generation DCF, I will show you a simple technique – based on Boolean logic – for modeling tenant improvements at the inception of each new lease. This technique can then be used to model other cash flows reliant on tracking the transition from one lease generation to the next.
The Basics of Tracking Lease Start via Boolean Logic
Below you’ll find a video where I walk you through this technique, step-by-step. But allow me to first describe the basics of the logic. Essentially, once we’ve dropped in our assumptions for when each lease generation begins and ends as well as the TI charge for each lease generation, we’ll build out one simple formula to return the amount of TI owed and in the period owed.
The formula is built into three sections, one for each generation lease. The logic goes like this:
(The current period = the first generation lease start month) x (the TI owed the first generation tenant)
(The current period = the second generation lease start month) x (the TI owed the second generation tenant)
(The current period = the third generation lease start month) x (the TI owed the third generation tenant)
If the logic in the first half (underlined text) of each section returns FALSE, than the result for that section is 0. If the logic in the first half of each section is TRUE, than the result of that section is the TI owed in that generation.
Since no lease generation can start at the same time as any other, no more than one section will return a value greater than 0. Thus, in most cases the result will be 0 + 0 + 0, except when a new lease begins in which case the result will either be ‘1st Gen TI + 0 + 0’, ‘0 + 2nd Gen TI + 0’, or ‘0 + 0 + 3rd Gen TI’.
Video Walk-through of Technique for Modeling Multiple Generations of Tenant Improvements
I find it much easier to show, rather than trying to explain, how this concept works. I recorded a ~15 minute video. The video walks you through how to use a formula with Boolean logic to model TIs at the inception of each new lease generation. As a companion to the video, I’ve posted below two Excel Workbooks to help you further understand and apply the technique.
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.
Model Multiple Generations of TIs Using Boolean Logic (Two Workbooks)
- Two Excel workbooks, a template file with formatting and no formulas and a completed file
- Excel workbook used in video showing a method for modeling multiple generations of tenant improvements
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.
About the Author: Born and raised in the Northwest United States, Spencer Burton has nearly 20 years of residential and commercial real estate experience. Over his career, Spencer has helped close $4.5 billion and underwrite $30 billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently the Head of Real Estate Investments and member of the founding team at Stablewood Properties.