Base Year Stop Workout
- 06:29
Calculate the amount of expense reimbursement based on tenant terms
Transcript
Real estate investing base year stop workout. We have a workout here for a base year stop, which is going to be implemented in a full service gross lease. If we go to the workout tab, we see that we have a tenant that has a five-year full service gross or FSG lease with a base year stop at year one. We're going to calculate the amount of expense reimbursement for the following tenant terms. So we have some information on the building, the revenue growth, expense growth, tenant square footage, gross leaseable area, et cetera. So the first thing we're gonna need to do is actually calculate the pro rata share of this tenant because they will be paying a percentage of the total building expenses after the base year. So that's simply going to be the tenant square footage divided by the net leaseable area, 13.4. And now we have to build out a little model here, which shouldn't be too difficult. We're just going to calculate the revenues going forward, as well as the expenses going forward based on the growth rate above. So the revenue calculation will be the revenue times one plus the revenue growth for the building and we can copy that across. And then the expense reimbursement for this tenant is what we're going to calculate. So we need to actually hold off on this for a minute and calculate our expenses. So we have our total operating expenses in year one, and then our net operating revenue will be the total revenue. Once we have that, less the operating expenses and we probably can just go in here and fill this out as well. And what's going to happen with these expenses is that they're also going to grow by three and a half percent per year. So it's going to be the previous year times one plus the building expense assumption. And if we anchor that in terms of the row and the column, we'll be able to actually calculate this for all of our expenses going down. And then, we can also copy across, calculate as well our total expenses and our net operating income. So now what's going to happen is the expense reimbursement is going to be equal to simply the difference between each of these operating expenses in their second year compared to their base stop year. Every year, they will simply have to pay that difference. And so in the first year, we're not going to pay anything. So the first thing we have to do here is we have to link our formula to the year that we're in, so that we know that if we're in year one, which is our base stop year, we're not paying anything. And then if we are in years two, three, four and five, the difference between the sum of the expenses in that year over the base stop year is what we will be applying our pro rata share to. So the first thing we'll do here is we'll do an if statement that says if, and I've named some of these cells. So we'll do equals base year equals zero, then zero. We don't want anything in that year. If we're not in the base year, then what's going to happen is we need the sum of the expenses in the year that we're in minus the sum of the base year expenses. And that difference times our pro rata share is going to give us the amount that this particular tenant must reimburse the landlord for. So it's going to be the sum of the expenses in the year that we're in, and these are going to float to the right as we copy to the right, minus the sum of the base year and those are going to be anchored. So I'll anchor them at the end. And I'm gonna close off the parenthesis here so that it does that calculation first. And now, here's where I'm going to apply the pro rata amount to and the pro rata cell has been named. So we can simply just link to that, not have to worry about absolute referencing it. I do have to go back and absolute reference the second summation because this summation is going to stay and then this summation will need to float. So if I copy this across, I should get the correct... If we look, it's taking the amount that it's over the base stop each year and it's applying the pro rata expense to that, so that the landlord is being reimbursed for expense growth.