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 Amortising 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 am 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 base line, hence the shorter Loan term and the higher Interest rate.
For these three cells, I have also named 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 and is subtracted after tax.
The Total Debt service is equal to the Interest plus the Principal paid each year.
And for an Amortising 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 Amortising loan, Excel has an inbuilt formula called PMT.
Before we use PMT 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 will 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'lll write my PMT function.
he 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 willl 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 inbuilt Excel formula called IPMT.
So again, I'lll perform my quick check that we are 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 stands for the Period.
For 2015 the Period is one, 2016 the Period is two etc.
So to calculate the Period for this particular function, we'lll simply take 2015 and subtract 2014, which will be anchored.
The number of Periods will be the Loan term, and the present value will be the Loan amount.
I will 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 will 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'll see how these mortgage calculations help us calculate the Income taxes paid on the asset.