Using Boolean Logic to Model Multiple Generations of Tenant Improvements
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.
1 x Y = Y, whereas 0 x Y = 0
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 Boolean Logic Tutorial Files
To make this exercise accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you need) or maximum (your support helps keep the content coming – similar real estate tutorials sell for $50+). 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.
Frequently Asked Questions about Using Boolean Logic to Model Multiple Generations of Tenant Improvements
Version Notes
v1.1
- Combined Template and Complete Workbooks into one
- Updated header
- Updated cell formatting to accounting
- Added version tab
v1.0
- Initial release
