5. Expense Projections
Expense projections are often ignored in real estate valuation but can have a big impact on final returns. Learn how to add them to your model in this lesson.
Adding expenses projections to your model
- Expense projections are best included underneath revenue projections
- If you expect an expense to vary considerably over time, enter individual assumptions for each year
- Otherwise, use a single inflation assumption that will be applied to every year
- I'll take the latter approach for this particular model
ALT + I , R: Insert row
F4: Repeat previous action
CTRL + F3: Open name manager
ALT + N: Create a new name
CTRL + C: Copy selected item(s)
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
F4(when typing): Anchor cells
Our investment property comes with a long list of expenses which investors often ignore when calculating the value of a property.
In our model, we could create a new tab for our expense projections, or we could place them on the same sheet as our revenue projections.
Companies often have different modeling guidelines and it may be the case that you will have to follow theses specific guidelines when building your model.
In truth however, as long as you clearly lay out the model, your choice shouldn’t make too much of a difference.
In our model, we’ll ultimately be subtracting our expense projections from our effective gross income, so I’m going to include my expense projections and calculations on this current page.
Underneath my revenue projections I’ll create a new rule with ALT + I R and repeat this action a couple of times by pressing the F4 button.
Off camera, I’ll add in our list of expenses and their current values.
Often when you’re creating expense projections you vary one or two items over time as we have done with our revenue projections.
However, the majority of these expenses will simply increase by some annual inflationary amount, and in this model, I’m going to assume that all of our operating expenses increase by a constant inflationary percentage every year.
And I’ll set this inflation percentage at 3%.
I’ll also name the cell by pressing Ctrl + F3 to open the Name Manager and I’ll call it Expense Inflation.
Once we have this inflation assumption confirmed, we can now calculate our expenses and subtract these values from the effective gross income which will enable us to calculate net operating income, which is a very common rhetoric used in property valuation.
So skipping down a page, I’ll enter the text for each expense item underneath Effective Gross Income.
In the Before column, our operating expense is simply going to be equal to minus the value we have stored in our expense projections, and I can copy and paste this for the remaining expenses.
For 2015 our expenses are going to equal to the previous year’s expense multiplied by one plus the inflation rate.
We’re also going to perform the holding period check that I used for my revenue projections.
So again I’ll write an IF function which will check if 2015, which will be anchored vertically, minus 2014, which will also be anchored, is greater than the holding period.
And if it is, I’ll return zero, and if it isn’t, I'll return my operating expense for the previous year multiplied by one plus the expected inflation.
I’ll then close my brackets and press Enter.
And I can copy and paste this across for each year and vertically for each expense with ALT + E S F.
If I now change the holding period let’s say to seven years, my expenses projections should update accordingly, and they do.
We can now calculate total expenses by simply adding together these values, so I’ll write “=sum”, open a bracket and select the values.
And again, I can copy and paste for the remaining columns.
And finally we can calculate net operating income by subtracting total expenses from the effective gross income.
So I’ll simply select the Effective Gross Income, which in this column is G42 minus G52, and again I’ll copy and paste for the remaining columns.
With net operating income now calculated, we’re getting closer to finding our final annual cash flows from the asset.
However, they still need to take our mortgage payments and our income taxes into account.
As you can see, our model is growing in size all of the time and we’re now already on row 54.
For valuation models it’s often easier to build a model vertically on one sheet rather than on multiple sheets because formula auditing and navigation tends to be that bit easier.
In the next lesson I want to show you a very simple but useful trick for making models such as these much easier to navigate.