Sign in or start a free trial to avail of this feature.
7. Mortgage Calculations
Most real estate assets will be bought with loans to increase IRR. This can be tricky to incorporate into a model, due to the tax treatment of interest payments.
Why take out a mortgage?
- A mortgage will reduce the amount of cash our client will need to pay up front
- This can substantially increase the investment IRR
- However, it also increases the risk by reducing operating cash flows
Calculating mortgage payments in the model
1 Create variables for interest rate, amount and loan term in dashboard
2 Calculate total annual debt service using PMT function
3 Calculate annual interest payment using IPMT function
4 Calculate principal paid by subtracting interest payments from total annual debt service
- At the beginning of the investment, the mortgage balance is the loan amount
- Each year this amount should be decreased by the principal paid
- The final mortgage balance must be paid off when the asset is eventually sold
F4: Anchor cells
CTRL + C: Copy selected item(s)
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
The next item on our model diagram, are the mortgage payments. Before we calculate the mortgage payments however, we first need to speak to Joe about what loan terms he expects to receive from the bank. The loan terms will be dependent on the asset itself, Joe's credit worthiness, and also the current economic environment. After a quick chat with Joe, he tells us that he expects to receive an amortizing loan with a loan term of 25 to 27 years, an interest rate between 4.5% and 5.3%, and a loan amount of $1.1 million.
Returning to excel, we can now enter input values for our loan on our dashboard.
On our dashboard, I'm going to set the loan amount to $1.1 million.
The interest rate to 5.3%, and the loan term at 25 years.
When given a range for an input value I tend to use the conservative value as my baseline. Hence the shorter loan term and the higher interest rate. For these three cells, I've also names the cells LOAN_TERM, INTEREST_RATE, and LOAN_AMOUNT. In our model, we will now calculate the interest paid each year, the principal paid each year, the total debt service, and the remaining mortgage balance. Interest paid is actually an expense and can be subtracted from revenues before tax. The principal paid reduces the amount of total debt owing, I subtract it after tax. The total debt service is equal to the interest plus the principal paid each year. And for an advertising loan, the annual total debt service does not change over the life of the loan. And finally, we have the mortgage balance, which is the total loan amount minus the principal paid back over time. When we sell the property, the mortgage balance will need to be paid off as well. Let's start by calculating the total debt service. To calculate the total annual payment for an advertising loan, Excel has an in built formula called PNT. Before we use PNT however, we need to check if the current year is within the holding period. So let's navigate to the total debt service cell for 2015, and we'll start by writing our 'if' statement. And 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 write my PMT function.
The first argument in my PMT function will be the rate. Which in this case, is the loan interest rate. Next is the number of periods. Which is simply the loan term.
And finally we have PV, which is the loan amount.
I can then close the bracket, and then close the bracket for the if function, and press enter.
And this gives me a value of $80,411 that will need to be paid back every year. I'll then copy and paste for the remaining cells.
And as you can see, for each year within the holding period, the total debt service is the same. Now let's calculate the interest paid every year? And to do this, we'll use another in built Excel formula called IPMT.
So again, I'll perform my quick check that we're within the holding period.
And if we are in the holding period, I use the IPMT function. Again the rate is simply the interest rate, this time PER stand for the period. For 2015 the period is one, 2016 the period is two, et cetra. So to calculate the period for this particular function, we'll simply take 2015 subtract 2014. Which would be anchored.
The number of periods would be the loan term, and the present value will be the loan amount.
I'll then close the brackets and press enter.
And then paste for the remaining years.
And as you can see that the interest paid decreases every year as we pay down the mortgage balance.
To calculate the principal paid every year, we simply subtract the interest paid from the total debt service.
So this would simply equal to G63 minus G61.
And again I'll copy across for the remaining cells.
Finally, we need to calculate the mortgage balance. In 2014 the mortgage balance will simply be the loan amount.
And this loan amount will decrease every year by the amount of principal paid back. So I'll simply take the loan amount and add the amount of principal paid back every year. I'll then copy and paste for the remaining cells.
And if I now change the holding period, my mortgage calculations should update accordingly.
And they do.
In the next lesson, we will see how these mortgage calculations help us calculate the income taxes paid on the asset.