Real Estate Pro-Forma Model: Property Operating Scenarios (27:22)

You will learn how to set up different operating scenarios for the property in this lesson, including a decline followed by a recovery and stabilization as well as a high-growth period followed by a decline and a recovery. You will also learn how items such as rent, the vacancy rate, operating expenses, and TIs, LC, and CapEx change in each phase.


Table of Contents:

  • 0:25: The Purpose of Different Scenarios
  • 3:48: Decline and Recovery Scenario – Decline Phase
  • 10:53: Decline and Recovery Scenario – Recovery Phase
  • 14:41: Decline and Recovery Scenario – Stable Phase
  • 16:27: Longer-Term Decline and Recovery Scenario
  • 22:36: Comparing and Contrasting the Scenarios
  • 24:59: Recap and Summary

Transcript: Real Estate Private Equity Case Study 1 – Stabilized Multifamily Acquisition: Property Operating Scenarios

Hello and welcome to the next lesson in this Real Estate Private Equity Case Study module. This time around we’re going to turn our attention to these different operating scenarios and look at the steady growth scenario versus the decline and recovery scenario, versus the longer-term decline and recovery scenario down here.

Now in the previous lessons we’ve already been over some of the reasons why we need to look at different cases for this case study, but just to refresh your memory this particular scenario shown above, where the rent always goes up by 3% per year, other income goes up by 3% and then the vacancy rate and collection loss allowance are 5% every single year, and everything else follows a very stable trend – all of this is extremely unlikely in real life.


Because the truth is that real estate, like any market, is cyclical and there are going to be down markets and up markets, prices will increase, prices will decrease and with a lot of real estate models people missed this all together and all assume that prices just keep increasing without end for 10 years or 15 years or 20 years in the future.

But the truth is that if you look at pretty much any 10-year period in any region worldwide there is pretty much always some change in direction to real estate prices and rents and vacancy rates. So if you’re in a downturn, chances are it’s not going to last for 10 straight years. There’s probably going to be some type of recovery along the way. And if you are in a very frothy market, it’s probably going to come down in the future.

Now I can already anticipate that someone is going to leave a comment and say, “Well what about markets X, Y and Z? Prices stayed low for 15 years there.” One good example of this is Japan where, after the real estate boom in the 1980’s, there was a big bust in early 1990’s and, as you can see here, prices stayed very low for close to 20 years.


There was a moderate uptick and then decline right before 2009, but overall prices there stayed about the same for quite a long time. So yes, there could be cases like this, that’s true. But again, we’re dealing with a very different market here, one where there actually have been clear historical trends of prices increasing and vacancy rates rising, falling, rising and falling again.

Now another question that you might have looking at this is, why do we need to do scenarios at all because why can’t we just look at some sensitivity tables? So for example why can’t we just go down here and say, “Okay we have some sensitivity tables, let’s add another one and look at what happens when rent goes up by less than we expect.” Maybe it goes up by only 1% per year or maybe it goes up by negative 1%, so it falls by 1% per year.


Why not just create some sensitivities around that and just keep it to just be one scenario up here? The problem for doing that for real estate is that many of these assumptions are intertwined and interrelated. So, for example, if rents are falling then it probably means that vacancy rates are rising, cap rates are probably also rising and the owners probably need to spend more to attract new tenants because rents are falling and there are fewer and fewer tenants to begin with.

So many of these assumptions that you see laid out in a property pro-forma are interrelated and so sensitivities and the sensitivity toggles that we cover elsewhere in this course would not work quite as well here. So that is why we’re taking this approach as opposed to just sensitizing a few key variables.

With all that said, I want to now go into Scenario #2, the Decline and Recovery, and talk a little bit about what happens in each phase of the process to each of the key assumptions here.


Rent, Other Income, the Vacancy Allowance and Collection Loss, Expenses, Capital Expenditures, Tenant Improvements and Leasing Commissions – once we’ve done that, then look at the recovery phase and how everything changes there and then look at the stable phase or steady-state growth phase.

Then we’ll take a look at the longer-term decline and recovery scenario, which is very similar in a lot of ways but has timing that’s a little bit different. And then we’ll conclude by comparing and contrasting output in different scenarios here and seeing what this property looks like under different market conditions.

As always, I have some extensive notes over here on the right- hand side of the page. This time around I’m not really going to cover these or go through them step by step. These are really here for your own reference because I want to focus on entering the numbers and explaining the numbers as I actually enter them in this Excel spreadsheet. So let’s go up and first things first, we’ll enter frame here so you can see all this a bit better.


So in Scenario #2 we have an immediate decline. Looking at this chart of rents in Seattle you can see that even when there was a decline, rents didn’t really fall by all that much. We’re talking maybe 2 or 3% per year if you actually go through and run the numbers and try to figure out what each dot and this line represents. So we’re going to have something very similar in our model and we’re going to say here that there is simply a decline of (3%), really a decline of 3%, so (3%), followed by another (3%) decline and then a (2%) decline.

Now whenever rents are falling, the ancillary income from parking and amenities and gym access and other things like that is also going to fall because you have fewer tenants and the tenants you do have are starting to save more. They don’t want to spend as much on those types of items when the economy is worse and when people are losing their jobs. This income tends to fluctuate and be a lot more volatile than rental income as well because tenants don’t have to pay for this.


So if things get worse, people will stop driving to work. They’ll start taking the train or the bus or using public transportation to save money on a car and on parking. So we’re going to assume actually a (7%) change here followed by a (5%) and (3%) one.

So this actually declines by a greater percentage than the rental income.

Now for the Vacancy and Collection Loss here, remember going back to our chart, the peak vacancy rate was around 9% just over 9%, more recently it was around 7%. So realistically either one of these scenarios could happen if there is a downturn in the market because it happened before and real estate is cyclical. For the decline phase of this, we’re going to assume a gradual rise up to around 9%. Remember it’s at 5% in the last period, so it jumps up to 7% and then goes to 8% and then goes to 9%.

Now the 9% here, remember, represents the vacancy allowance and the collection loss together.


So really we’re probably closer to the 2009 peak for the vacancy rate of around 7% simply because we still have a collection loss of around 2% here. For the other expenses, so for operating expenses there are replacement reserve and then the cash flow line items below this. Generally whenever there’s a decline, these expenses are actually going to fall and I’m going to just enter a (2%), (1%) and (1%) for the operating expense growth rates and say (3%), (2%) and (2%) for the replacement reserves.

So the reason why these are going to fall is because the property owner is probably going to start cutting costs whenever there’s a market decline. So maybe the owners don’t need as many front desk staff or as many people on staff, in general, to help manage this property.


They’re also going to have fewer tenants in general. So something like utilities, which is clearly linked to the tenants and how much they use utilities like water and electricity, which is going to fall because you have fewer tenants and the owners are finding more ways to save money on this. The Replacement Reserve is also going to fall because you don’t have as many people using the property. So the individual units are not going to see as much use and then even the common areas are not going to see as much use because people are staying in more, they’re probably not going out as much, fewer people are walking around and so you can expect some sort of decline here.

Also, the other thing that we would note is that with the Replacement Reserves, some of these payments may be deferred to future periods because the owners probably think it’s a decline. They don’t want to spend money unnecessarily if they don’t have to. So they’re probably going to defer some of these as long as possible into the future and so in general we expect to see a decline here that is greater than the decline for operating expenses.


Then for these cash flow line items. Capital Expenditures as with the other expenses the owners are going to try to save money in the beginning, so we’re going to assume a (3%) change and then a (5%) change. So they’re going to try to defer these as long as possible. But the problem is that you can only do that so much because if you think about it, if they’re not spending to upgrade the property they’re going to have an even harder time attracting tenants. So this vacancy rate may go even higher if they keep postponing things, if the roof breaks and they can’t replace it or if competitive properties for example have much better gyms and their gym doesn’t compare. They’re going to eventually have to spend something on this.

So toward the end of the decline period, we’re actually going to assume here that capital expenditures increase by 5%. Then for these last two items, Tenant Improvements and Leasing Commissions. Tenant Improvements, we’re going to assume go up by 7% then 10% and then 10%. The reason being that in a poor market you have to offer more of these to attract tenants, so these are generally going to go up.


And same with leasing commissions. In a poor market you have to pay real estate agents more to attract the right tenants. So we’re going to see a big bump here and say 20%, 15%, and 15%. It will be better to back all these assumptions with actual historical data, but it’s very hard to come by for these types of items. And remember the whole point of a model is not to get to an exact number down to two decimal places for the IRR. It’s to get an idea of whether or not the project is actually feasible or whether or not the acquisition is actually feasible. So don’t get too stressed out about exactly where we’re getting these numbers from.

The main idea is to enter some rough estimates and see whether or not it’s going to work, can it work under reasonable assumptions or will it only work if we’re very optimistic. That’s all we’re really trying to get to here. So that’s a little bit about the decline phase and again I have notes on all this over here on the right hand side.


We’re going to go over to the recovery phase now and go through some of the changes that you see there because no downturn in the real estate market lasts forever. Going back to that Japan example, even there they didn’t really have a downturn lasting forever. It did last quite a long time, about 10 years or more, more like 12 years by the looks of this. But then eventually rent flattened out and it actually started rising.

So even if something like this lasts for quite a long time, nothing lasts forever. Prices fall, prices rise over time. And so here the rental income we’ll assume, will eventually bottom out in Year 2 of this recovery and then start rising again by 1% and then 2%.

And for the Other Income Growth – this will take a little bit longer to change. They’ll still decline by 2% and then 1%, but then it’ll jump back up as the economy improves and more people start paying for parking and using other amenities. So we’ve said 5% and 5% here.


For the Vacancy Allowance and Collection Loss, I’m going to have this falling to 8% and 7% and 6% and 5.5%, so it’s gradually declining throughout the recovery.

Then for the Operating Expense Growth here. So we’re going to hold us flat in the first year but then this could grow by 4%, 3.5% and 3.5% because as you start attracting more tenants, utilities are going to go up. You can then afford to pay for more staff because you’re saying things are improving. We might as well have more people on our team, we can get better tenants and increase the rate of our growth and really reduce that vacancy rate that we’ve assumed.

For the Replacement Reserve Growth Rate, it keeps declining a little bit in Year 1 of the recovery, but then after that then it starts reversing and starts rising because we start getting more tenants and then we really start getting more tenants as the vacancy rate falls even further. So it jumps up to more like 5% and then we will say 4% over here.


So those long-deferred payments that we probably should have paid for a long time ago, start coming up in this recovery period and that’s why we see greater than normal growth for something like this. If you think about it, it makes logical sense even though again we don’t have hard numbers backing up all this, exactly.

For the CapEx Growth, we see a very similar trend here. Remember even at the end of our decline period this had already jumped up. We can only defer payments for so long. So we’re going to say 4% than 10% because we start getting more and more tenants. And we say might as well spend on upgrades and spend to improve the property if things are improving and if we’re getting more tenants anyway. And then we’ll say 5% and 5% over here.


For the Tenant Improvements and Leasing Commissions, these are both going to fall in the recovery period because we just don’t need to offer as much to get new tenants into our property anymore. So Tenant Improvements will fall by 5% per year. And then Leasing Commissions in the first year may still be rising, so I’ll say 10%, but then after that they’re going to fall by 5% each year. And so we have that set up.

So that’s the recovery phase. It’s really the opposite, the reverse many ways of the decline phase where rent goes up by a higher rate and other income also starts rising back to its old level, the vacancy rate falls, operating expenses start rising again and a lot of these long deferred payments for things like CapEx, start appearing in this period.

Now for the stable period, this is pretty easy because it’s strongly linked to everything we have above up here for these last three years, and then the so-called “Stabilized Year” all the way at the end.


So for the Rental Income Growth Rate I’m going to say 3% all the way across, at least for these next few years. And then Other Income, I’m going with that same 3% assumption. The Vacancy and Collection Loss Percentage will seem falls to 5% by the stage of the process. And then Operating Expense Growth rate we’ll say it’s 3%. Same for the Replacement Reserve Growth Rates, so these are both 3%. Copy them cross.

Capital Expenditures, TIs, and LCs, we have to be a little more careful about because these are still at very elevated levels from those earlier years in the decline period. So with these we probably want to have a bit more of a decline in all of them. So I’m going to say a 5% decline for CapEx in the first two years followed by 2% growth in the final two years. And then Tenant Improvements we have a 5%, 5%, 3% decline followed by 2% in the stabilized year, one year after the end of fiscal 2024.


And then for Leasing Commissions we’ll have a 10% decline, a 5%, 6% decline and then 2%. So roughly doing the same thing, which is falling, and then finally by the end it starts rising like it normally does.

So that’s about all I wanted to say on Scenario #2 here for the Decline and Recovery. Again, I have some extensive notes over here on the right hand side. But now I want to go to Scenario #3 and look at what happens with this longer-term decline and recovery.

This is pretty much the same, but we do have a high-growth phase in the beginning where we’re going to see rents keep increasing by more than 3% because, remember, in the overall area, they increased by 6 or 7% in the last year. So rents are going to go up by a greater-than-3% number. Other Income will grow and we’ll assume that the Vacancy Allowance and Collection Loss Percentage actually falls, even below the 5% that we’ve assumed.


Now the reason why this scenario is different from the other one we looked at is because first off, the timing is different. And also because if you think about the numbers and the magnitude here, if this decline happens in Years 2, 3, and 4 or Years 3, 4, and 5 rather, as opposed to Years 1, 2, and 3, we are starting the decline from a much higher base number, or at least a somewhat higher base number. And so it may end up being more significant in the scenario, although we’ll have to look at it to tell for sure.

So for the Rents I’m going to say 6% and 5%. Other Income will grow by 8% and 7% because, again, it is more volatile and it fluctuates a lot more than the rental income. The Vacancy and Collection Loss will decline to 4.5% and then 4% and then the Operating Expense Growth Rate will be 4% and then 3.5%. The Replacement Reserve Growth Rate will be 3% and 3%.


And the rest of these items all have normal values. These are not necessarily going to go up or increase by much of anything in a high- growth period because, again, you just don’t have to pay anything extra if you’re already getting a lot of tenants and if demand really exceeds supply by so much. In fact, you could even make the argument that they should maybe be changing by 0% because you don’t need to spend any money in this phase of the process. But we’ll be a little bit more conservative and keep it at those 2% growth rates.

Now the decline phase is largely the same as what we saw up here. The difference is that it happens a little later on and also the recovery phase is compressed a bit because it’s three years as opposed to four years. So some of our numbers here are also going to be compressed.

For the Rental Income Growth Rate, I’m going to say (3%), (3%), and (2.5%). Other Income will be a 6% decline, than a 5% decline and then a 4% decline. Our Vacancy and Collection Loss once again, will go up to 7%, 8% and then 9%. Very much in line with the market data that we looked at.


And then the Operating Expense Growth Rate will be a 2% decline, 1% decline and then 1% once again. Replacement Reserves, as always these are going to fall by some amount during a decline. So we’ll say 3%, 2%, 2%. And then for the CapEx growth rate will have (3%) and (5%) and 5% under the same logic. In its decline, the owners are going to postpone these types of items for as long as they can. And they’re all going to start doing them once they really need them to attract new tenants to the building.

Tenants Improvements and Leasing Commissions are largely the same logic, so say 7%, 10% and 10% for the TIs and 20%, 15% and 10% for the Leasing Commissions. So you need to spend more on these items to attract higher quality tenants during this decline period.


Now for the Recovery Phase here, this is very much compressed and condensed. So for the Rental Income, it will still be falling a little bit in the first two years, but then it’ll flip and start reversing and start becoming a positive by the end. Other Income Growth will be falling by more than rent once again, but then it’ll flip and also become positive by the end.

The Vacancy and Collection Loss will decline from 8% to 7% to 6%. Operating Expense Growth will be frozen in the first year and then jump up and start rising by more than that because the owner can look out there and realize that the market’s about to turn around, so he starts spending in advance of that.

For the Replacement Reserve Growth Rate, we’re going to say (1%), 2% and then 5%. So these payments are generally deferred and not as much is being spent here because there are fewer tenants. But as the tenants jump up and the vacancy rate falls, you need to start setting aside more for this type of item.


Then for the CapEx Growth Rate, this one, as with the other declined scenario. This one is going to jump up by a bit more than usual in the recovery period. So we’ll say 4%, 10%, and then 5%. And then for the TI Growth Rate we’re going to say a 5% decline, 5% decline, and 5% decline.

Leasing Commissions will jump up at first, but quickly reverse themselves and start declining once again because you don’t need to spend as much to attract tenants. And then in the Stable Phase, we’ll have three percent for Rental Income Growth and for the Other Income Growth Rate, in-line with the stable numbers, and then for the Vacancy and Collection Loss we’ll say 5%. Operating Expense Growth we’ll say 3% and the same thing for Replacement Reserve Growth. And then for CapEx Growth, we’ll keep this set to (5%) because it’s still going to decline a little bit as it approaches stabilization. But then it’ll even itself out at 2%.


And then for the Tenant Improvement Growth Rate we’re going to have this keep falling. So it’ll be (5%), (3%), (3%) before stabilizing at 2%. And then Leasing Commissions fall by (10%), (6%) and then end up at 2% growth in the final stabilized year.

So that’s really it for this scenario. As I said, it’s largely the same idea as the decline in recovery we explained in more detail in Scenario #2 before. But I did want to show you what it might look like on a more compressed timeframe here. With all that said, we can now move on to the next step of this process and compare and contrast output in different scenarios.

So remember, our model is already set up to handle multiple scenarios on the Performa down here and we’re in Scenario #1 right now where Effective Rental Income goes up to almost $10 million by the end in this final stabilized year. NOI is $7.4 million and Adjusted NOI is about $6.4 million.


Let’s change it to scenario two and see what happens here. So go up here and change it from the drop down. So now you can see that rental income declined substantially in those first few years and by the end it’s about $7.5 million as opposed to closer to $10 million. NOI is $ 5.2 million, quite a bit less. And Adjusted NOI is $4.4 million, which is also substantially less than what we had before.

If you go to Scenario #3 now, Longer Term Decline and Recovery. If you go down here, we get to numbers that are a little bit higher, but are still much lower than Scenario 1. So our Effective Gross Income is around $7.8 million, NOI of $5.5 million and then Adjusted NOI of $4.6 million.


So the interesting part here is that if we just look at the stabilized year, the NOI values are not that much different. So we might actually get about the same price for selling the property in this final year if we pick the same cap rate. However, the cash flow profile in between is very different, and this is going to create differences when we actually value this property.

It’s also going to create differences when we look at the coverage and leverage ratios and the debt service covered ratio for example, because in this intermediate period, we’re going to have different amounts of interest, payments and principal payments on the debt. And so for NOI and adjusted NOI are also different then we may run into issues with lenders. If we show them these scenarios and they don’t like them, we may not be able to borrow as much. So we need to think about the impact these scenarios are going to make, not just on NOI and rental income and what the final returns look like, but also on other issues that may make the deal harder to actually complete.


So to do a quick recap and summary. We’re doing this because nothing really increases by 3% or 2% per indefinitely. You always want to look at multiple scenarios when you have a projection period that’s this long. Is there going to be decline or recovery? Is there going to be high growth then a decline and then a recovery? Will there be a recovery then a decline then a recovery? You have to think about the market and pick what makes sense. In this particular market, we think that it is very reasonable to assume that there will be some type of decline within the next few years followed by recovery and stabilization after that.

To summarize what happens in all these different periods, when you have high growth rent and other income will rise by more than the rate of inflation. The vacancy rate will fall. Expenses will also rise by some percentage but probably less than the percentage by which rent is increasing. In the decline phase, rents and other income will both fall, other income by more than rents, the vacancy rate will rise and expenses are cut on the operating expense side.


But CapEx, tenant improvements and leasing commissions may actually rise because you have to pay more to attract new tenants.

In the recovery phase, rent and other income will bottom out and then eventually will start rising again. The vacancy rate will also start falling and go back to the long term stabilize level. Expenses will also jump up because there’s more demand so you can afford to pay for more staff and pay for more replacements and more CapEx. CapEx could actually jump up again before falling back to its stabilized growth rate. And then tenant improvements and leasing commissions will generally decline here because you don’t need to spend as much anymore to attract tenants.

And then in the stable phase, everything is going to move back toward the long-term trends toward the end of the period. So when we look at items like tenant improvements and leasing commissions, in Scenarios 2 and 3 in a stabilized year we shouldn’t be dramatically higher than the starting values anymore because they have risen and then fallen back. So that’s the type of thing that you want to check as you go through this.


That is pretty much it for our lesson, so coming up next we’re going to take a look at the acquisition and exit assumptions for this transaction and figure out how assumptions like the exit cap rate and the debt that’s used in the deal might change depending on the scenario that we’re in.