4. Calculate Effective Gross Income
Effective gross income accounts for revenue losses due to vacancy and collection losses. In this lesson, we'll learn how to include these variables in our model.
Effective Gross Income (EGI)
Definition: EGI = Potential Gross Rent - [Vacancy Losses] - [Collection losses]
Using a Holding period variable
- Our client has told us that the asset will most likely be held for 6-9 years
- To model different investment durations, we create a holding period variable on our dashboard
- We then use an IF function to check if the current year is within the holding period
CTRL + F3: Open name manager
ALT + N: Create a new name
F4: Anchor cells
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
In the previous lesson, we entered assumptions regarding rental price changes, estimated vacancy rates and collection losses.
We’ll now use these assumptions to calculate our effective gross income from the asset.
Effective Gross income is a common term in Real Estate and it’s equal to the annual rent, less vacancy costs and collection losses.
Let’s start by calculating the potential gross rent which is equal to the annual rent if the property is fully occupied and all the rent is collected.
The current landlord has confirmed that in 2014 there were no vacancy or collection losses, so the potential gross rent was simply equal to the current annual rent.
In 2015 it’s equal to the current annual rent multiplied by one plus the percentage price change.
So I’ll select the current annual rent and multiply by one plus the rental price change.
And then I’ll copy across for the remaining cells.
When I make this calculation, the model assumes that we hold the property until 2023.
However we don’t know how long the property will actually be held for and ideally we would have an assumption which I’m going to call the holding period, which is an input allow us to test the returns for various time based scenarios.
Off camera I’ll create this assumption in a new tab which I’m going to call The Dashboard, and I’m going to name the cell Holding Period.
So I’ll navigate to the cell, Ctrl+F3 to bring up the Name Manager, Alt + N for a new name, and I’ll call it HOLDING_PERIOD.
Now back in my Gross rent calculation I’ll simply check if the current year is within the holding period.
And if it is, I’ll include the potential gross rent, and if it isn’t, I’ll set the rent to zero, because we’ll have the asset sold by that time.
So I’ll write a new formula “=if” and the logical test will be if the current year minus 2014, which will 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 formula for the potential gross rent, which is going to be the previous rent multiplied by one plus the percentage increase.
I’ll then close the second bracket and press tab, and then I’ll copy across for the remaining cells.
And as you can see, because my holding period is set to 7, I only have 7 years of gross rent showing in my model, which is exactly what I want.
Let’s now do the same calculation for my vacancy allowance, which is going to be the potential gross rent multiplied by the estimated vacancy rate, but first I must enter my IF function again.
So again the logical test will be if the current year minus 2014, which is anchored, is greater than the holding period.
And if it is, I’ll return zero, and if it isn’t, I’ll multiply the gross rent by minus the estimated vacancy rate.
I’ll now copy and paste across for the remaining years.
And off camera I’ll complete the exact same calculation for collection losses.
To calculate effective gross income, I simply SUM the three values in each column, so “=sum”, then select my three values.
And again, I’ll copy across for the remaining cells.
If I now switch my holding period to say nine years, our revenue projections will update accordingly.
From our calculations, we can see that in the first year the big increase in gross rent is offset by quite a large vacancy allowance, leaving us with an effective gross income of less than $70,000.
However over time, the increases in gross rent can be seen and the effective gross income grows considerably over time, With our revenue projections now complete, in the next lesson, we’ll move on to Expense Calculations.