-
Posts
-
Hi Spencer and Mike,
My question is about the calculation when you’re looking for what month a tenant will start when accounting for the renewal possibility. This would be column R in the “Rent Roll” worksheet tab.
I understand that you only need to do the possibility that there is not a renewal when you’re looking at the first 3 “vacant” tenants because there is no possibility for renewal for a tenant that is not there. But, how come you don’t account for that in the other tenants that are there? Shouldn’t R10-R13 weight the possibility of renewal and non-renewal, such that there’s a 70% chance the tenant starts in the month following expiry and a 30% chance that the next tenant comes one month after the post-expiry downtime assumption?
Hope this question makes sense. Maybe I’m missing something easy. Thanks.
P.S. If you could also break down the “escalation month” calculation (Column V), that would be great too.
Hi Francisco,
Downtime and Renewal Probability
Glad you brought this up. I think first it’s important to point out that there are various ways to handle downtime assumption and renewal probability. I taught you one method in this course. I use a different method in my All-in-One model. And there are others I’ve seen and used. One method isn’t necessarily better or worse than another, just different. What’s most important is that you understand why the method is being used. Allow me to explain my rationale in this instance.
What column R is solving for is when the 2nd generation lease begins. Remember, from a modeling perspective a tenant labeled “Vacant” is really no different than a tenant with a name. The only difference is we know the contract terms of the named tenant, whereas we’re making an educated guess (i.e. assumption) as to the terms of the “Vacant” tenant’s lease. But in both instances, column R is making an approximation of when the next lease (i.e. the 2nd generation lease) begins.
So take the example of the exercise from this course. In the case of both the Vacant and named tenants, the 2nd generation lease is expected to start 9 months following the end of the 1st generation lease. However, we also set a 70% renewal probability. Meaning, we give this instance a 70% probability that there will be 0 months between the 1st and 2nd generation lease and a 30% probability that there will be 9 months between the 1st and 2nd generation lease. Thus, we use a formula that calculates the weighted average downtime of these two scenarios (70% x 0 + 30% x 9 = 2.7 months). I then round up to the nearest month, 3 in this case. Thus, I assume that there will be 3 months of $0 income between each generation of lease for this tenant.
Again, that’s just one method. Another method, using a 70% renewal probability and 9 months downtime, is to assume no months of $0 income. But instead to assume income is only 70% of gross potential for that tenant during the 9 months between each lease generation. That is the method I use in the All-in-One.
Escalation Formula
Consider that rent bumps for contract leases occur at different times in the year, usually on the anniversary month of the lease start. So if a lease started in July, and has annual bumps, you’d expect future rent increases to occur in July.
The challenge is, every tenant has a different “lease start anniversary month”, so how do you model rent bumps in different months? The Escalation Month value (column V) is part of a module I created that does that calculation.
Essentially, the formula in cell V7: ‘=12-(ROUNDUP(R7/12,0)*12-R7)‘ finds which month of the year (i.e. Jan = 1, July = 7, Nov = 11) that tenant’s rent should increase. It’s a formula I developed myself, and it took some time create. If the lease starts between month 1 and 12 of the analysis, it’s simple. But when the lease starts beyond month 12, say in month 91, it becomes more difficult.
Take month 91 as an example. Which month of the year is month 91? Well month 91 is in year 8 of the analysis – we know this by doing the following calculation: ROUNDUP(91/12,0) = 8. Year 8 starts in month 85 (Jan = 1). Thus 86 = Feb (2), 87 = Mar (3), 88 = Apr (4), 89 = May (5), 90 = Jun (6), and 91 = Jul (7).
To do the above calculation, I developed the formula 12-(ROUNDUP(‘Lease Start Month’/12,0)*12-‘Lease Start Month’). Such that 12-(ROUND(91/12,0)*12-91) = 12-(8*12-91) = 12-(96-91) = 12-5 = Month 7.
Thanks again for the questions!
Spencer
- You must be logged in to reply to this topic.
