Mortgage Calculator: A Simple Tutorial (template included)!

Tech More
36 followers
1 Video View·Oct 28, 2022
So last year I took out a $400,000 loan to buy this apartment and I spent ages negotiating
with the bank because as you soon see, a 0.3% change in the interest rate would mean I pay
an additional $23,000 in interest payments.
On the flip side, if I work hard to squeeze in as many ads as possible on the videos,
I can make extra payments as soon as possible and I can actually pay $50,000 less over the
course of the loan.
Interested to see how all this works?
Let's get started.
Hey friends, welcome back to the channel.
If you're new here, my name is Jeff and today we're walking through step-by-step how to
calculate your monthly mortgage payments given three things.
The amount you borrowed, the interest rate you agree to, and the loan repayment period.
I've linked this entire template down below, so feel free to make a copy and follow along.
You only need to input some numbers and formulas in the cells highlighted in yellow and everything
else will be automatic.
So I bought around $400,000 and the interest rate I got in the end was 3.3% and it was
a 30-year mortgage, one of the more common timeframes.
Please note the calculations we're going over today is for a fixed rate mortgage where the
3.3% interest rate does not change over 30 years versus an adjustable rate or variable
rate mortgage where the interest rate may change over the lifetime of the loan.
Calculations are slightly different.
I'll touch on that at the end of the video.
To change the years into months, simply multiply 30 by 12.
And for monthly payment, you actually want to use the PMT payment formula.
And since this rate is annual, but you want the monthly payment, you need to divide this
by 12, right?
The number of periods is 360, 30 years times 12 months.
And the present value is simply the loan amount.
Now, you see this number is negative, which makes sense since you are paying back the
bank or whoever loaned you the money.
But for the purposes of today's monthly mortgage payment calculation, we need this to be positive.
So you simply add a negative sign in front.
If only getting out of debt were this easy in real life, oh wait.
At this point, you can actually already start to play around with these three numbers to
see how monthly payment is affected.
Fun activity.
If you watch Ali's video on buying a house, you can actually plug in the numbers from
his video to see if he made a mistake.
Comment down below with what you found out.
Okay, but you still don't know how much total interest you owe the bank and how much less
you can pay.
If you make extra payments on top of monthly payments within those 30 years, starting with
self B10, let's say the first payment due date is January 1st, 2022.
The beginning balance is $400,000 and the monthly payment.
Since this does not change, we can actually press function F4 to lock this number in place.
The interest is simply the beginning balance multiplied by the monthly interest rate, which
is 3.3% function F4 to lock it divided by 12.
And the principal payment is simply the monthly payment minus the interest payment.
Put another way, interest plus principal equals monthly payment.
We'll skip over the extra payment for now.
The ending balance is the beginning balance minus the principal amount you've paid back
so far, minus any extra payment you made that month.
This is month one of paying back our loans and the total interest is just month one interest
so far, and the total principal is actually month one principal plus any extra payment
we made because extra payment goes towards paying off your principal, not the interest.
Very important to remember.
And now pay special attention to row two.
First use the E-date formula and reference the first payment due date and want to add
one month each time.
The beginning balance is just the ending balance of last month.
The monthly payment, we actually want to use the minimum function.
First field, the monthly payment function F4 to lock.
Second field is actually the beginning balance of this month plus the interest of this month.
You see why we do this towards the end of the video.
Since the way we calculate interest, principal, and ending balance do not change, highlight
these cells, highlight the row down below, command or control D to drag down.
For month number, we simply add one to the cell above.
The total interest is this month's interest plus the total interest we've paid back so
far.
The total principal is this month's principal plus any extra payments we made plus the total
interest we've paid back so far.
So three numbers there.
Now we can highlight the second row, drag it all the way down to row 369, command or
control D and boom, everything is now automatically calculated.
You can see at the end of the 360th month or 30th year, the ending balance is now zero.
Let me quickly change the colors back real quick.
All right.
To double check our work, we want to make sure the total cumulative principal we pay
back is equal to our initial loan amount.
And that's why we use the maximum function on this row right here.
And yes, we see the $400,000 is equal to our initial loan amount.
It's also interesting to see the total interest we need to pay back to the bank.
So that's why we use the maximum function again on the total interest row.
As you can see, the total interest payment we owe is a whopping $230,000 over the course
of 30 years.
I'm so screwed.
To see how total interest is affected by the initial loan terms, command or control C and
then command or control shift V to paste without formatting.
And for difference, use total interest minus original interest.
And since these two are the same right now, it's zero.
If we got a better rate, say 3%, the new total interest we owe is $207,000, meaning we paid
$23,000 less.
If the rate were 4.5%, just a tiny 1.5% increase, right?
We'd actually pay an additional almost $100,000 more.
On the flip side, let's see how making extra payments on top of your monthly payment will
actually decrease the total interest you pay and your loan repayment period.
Here I plan to take $5,000 of my annual bonus and make a one-off extra payment every year
for the first 10 years.
You'll see the total principle doesn't change.
It's still $400,000, but I actually pay $53,000 less in interest payments.
And if you scroll all the way down, I actually finish all my loan repayments by the 301st
month.
So in around 25 years instead of 30.
By paying back some extra money earlier, I paid back less overall and I pay it back faster.
This is because an extra payment decreases the ending balance of that month, right?
So the beginning balance of the next month is lower, decreasing the interest amount you
pay for that month.
So more of your monthly payment goes towards paying off your principle.
This is also a great time to bring up why we use the minimum function at the beginning.
If you scroll all the way down for this last payment, this last payment amount is actually
less than the initial monthly payment.
So if we didn't use the minimum function, let's just copy and paste the initial monthly
payment here, you'll see we actually pay back more than necessary.
And I don't think your bank would tell you if you made this mistake.
Last pro tip, you can use this template to calculate any fixed rate loan.
It doesn't have to be a mortgage.
You just need to know the loan amount, the interest rate, and the loan repayment period.
Let me know in the comments, if you want adjustable rate mortgage calculator, it's basically this
just over and over again.
See you on the next video.
In the meantime, have a great one.
