How to Calculate Monthly Mortgage Payments
Something I wish I had learned in college: a tiny change in the initial interest rate can make a MASSIVE difference in how much total interest you end up paying back the bank
Free Mortgage Calculator
- You can find the spreadsheet template here
- Fun activity: In Ali's video he talks about taking out a $800,000 loan at 5% interest with a loan repayment period of 10 years. Is the monthly payment actually $8,485/month? Comment on this post and let me know what you think :)
Watch it in action
Context
So last year, I took out a $ 400,000 mortgage loan to buy this apartment and I spent ages negotiating with the bank because as you’ll soon see, a 0.3% change in the interest rate would mean I pay an additional 23,000 dollars in interest payments
On the flip side, if I work hard…to add as many ad slots as possible in my youtube videos, and make extra payments as soon as possible, I could pay 50,000 dollars less over the lifetime of the loan
Fixed-Rate vs. Adjustable-Rate Mortgage
Please note the calculations we’re going through today is for a fixed rate mortgage, where the 3.3% interest rate does not change over the 30 years, versus an adjustable-rate or variable-rate mortgage, where the interest rate may change over the lifetime the loan, calculations are a little different, I’ll touch on that at the end of the video
The magical =PMT formula
To change the years into months simply multiply 30 by 12. For monthly payment, you want to use the PMT formula, and since this rate is annual, you need to divide 3.3% by 12 to get the monthly rate. The number of periods is 360 - 30 years times 12 months a year - and the present value, the amount you borrowed today, is 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 number to be positive, so you simply add a negative sign in front
If only getting out of debt were this easy in real life.
At this point, you can already start to play around with these 3 numbers to see how the monthly payment is affected. Fun activity - if you watched Ali’s video on buying a house, try plugging in the numbers from his example to see if he made a mistake. Comment down below with what you found out.
Calculating interest payments
Starting with cell B10, let’s say the first payment due date is January 1st, 2022. The beginning balance is 400,000 dollars, the monthly payment we already calculated, and since this doesn’t change, when we reference it, we use FN+ F4 to lock this number in place
The interest is simply the beginning balance multiplied by the monthly interest rate, which is 3.3% divided by 12 - remember to lock the 3.3%. The principal portion of the monthly payment is the monthly payment minus the interest payment. Put another way: interest + principal = monthly payment
We’ll skip over the extra payment column for now, and the ending balance is simply the beginning balance, minus the principal we’ve paid back so far, MINUS any extra payment you made that month
This is month one of paying back our loans. The total interest so far is just month 1 interest, and total principal is the month one principal amount PLUS any extra payment we made. Now, pay special attention to row 2.
First, use the edate formula and reference the first payment due date, and we want to add 1 month each time. The beginning balance is the ending balance from last month, and for the monthly payment, we actually want to use the MINIMUM function here and select monthly payment in the first field, FN+F4 to lock, and in the second: the sum of beginning balance and interest payment
You’ll see why we do this towards the end of the video. Since the way we calculate interest, principal AND ending balance do not change, we can highlight these cells, highlight the row below and CMD/CTRL + D to pull them down
For month number, we simply reference the above cell and plus 1, total interest is the interest we paid this month plus the total interest we paid so far, and total principal is this month’s principal PLUS any extra payment we made PLUS the total principal we paid so far
Now, you can highlight the second row, and drag all this down to row 369, CMD/CTRL + D, BOOM, everything is automagically calculated, and you see at the end of the 360th month or 30th year, your ending balance is zero. Let me change the colors back real quick
Alright, to double check our work, we want to make sure the total cumulative principal we paid back is equal to our initial loan amount, and that’s why we use the MAXIMUM function here, and yes we see this is equal to 400,000
It’s also interesting to see the total interest we have to pay the bank in addition to the principal, so we use the maximum function again. And you can see, the total interest payment is a whopping 230,000 dollars over 30 years
To see how these numbers will change based on our initial loan terms, CMD or CTRL C to copy the total interest, CMD/CTRL + SHIFT + V to paste this number without formatting, and for the difference use the total interest minus original interest, and right now these 2 are the same so it’s 0
If we got a better rate, let’s say 3%, the new total interest we’d owe is 207,000 dollars, meaning we’d pay 23,000 dollars less. If the rate were 4.5%, we’d pay almost 100,000 dollars more
Making extra payments
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
Here, I plan to take 5,000 dollars of my annual bonus and make a one-off extra payment every year for the first 10 years. You’ll see the total principal amount does not change, still 400,000 dollars, but I pay 53,000 dollars less in interest AND if you scroll down, I pay back the entire loan by the 301st month, so in around 25 years instead of 30 years
So by just paying back some extra money earlier, I pay back less overall AND I pay it faster. This is because an extra payment reduces the ending balance of that month, the beginning balance of the next period is therefore lower, decreasing the amount of interest in that period, which means more of your monthly payment goes towards paying off your principal
This is also a great time to bring up why we used the minimum function in the beginning. As you can see, the last payment here is less than the initial monthly payment, so if we didn’t use the minimum function here, we would have paid back more than necessary, and I’m honestly not sure the bank would tell you if you make this mistake
Last pro tip - this is how you calculate monthly payments for any fixed rate loan, it doesn’t have to be a mortgage. You just need to know the initial loan amount, the interest rate, and the repayment period.