A Beginner’s Guide to Using a Mortgage Calculator

Understanding Monthly Payment Calculations

In the past year, I obtained a loan of $400,000 to purchase an apartment. Negotiating with the bank was a lengthy process because even a slight 0.3% change in the interest rate could result in an additional $23,000 in interest payments. However, there's a flip side to this: by maximizing the number of ads in my videos, I can make extra payments and potentially reduce my overall loan amount by $50,000. If you're curious about how all of this works, let's dive in and explore the calculations together.

Introduction to Mortgage Calculator Template

Hello, everyone! Welcome back to our website. Today, we’ll be guiding you through the process of calculating your monthly mortgage payments using a step-by-step approach. All you need are three key pieces of information: the amount you borrowed, the agreed-upon interest rate, and the duration of the loan repayment period. To make things easier, I’ve provided a link below to access the complete template. Simply make a copy of it and follow along.

In the template, you’ll notice that certain cells are highlighted in yellow. These are the areas where you’ll need to input your numbers and formulas. The rest of the calculations will be automated for your convenience.

Let’s begin by considering a loan amount of approximately $400,000.

Calculating Monthly Payments for a Fixed-Rate Loan

After finalizing the loan agreement, I secured an interest rate of 3.3% for my 30-year mortgage. This duration is one of the more commonly chosen timeframes. It’s important to note that the calculations we’ll be discussing today are specific to fixed-rate mortgages, where the interest rate remains constant over the entire 30-year period. In contrast, adjustable or variable rate mortgages have fluctuating interest rates throughout the loan term. The calculations for those types of loans differ, and I’ll touch on that later in the post.

To convert the 30-year duration into months, simply multiply it by 12. In order to determine the monthly payment, we’ll be using the PMT payment formula. Since the interest rate is expressed annually but we need the monthly payment, we divide it by 12. The number of periods is 360, which is calculated by multiplying 30 years by 12 months. The present value represents the loan amount.

Now, you’ll notice that the resulting value is negative, which is logical since it represents the repayment to the lender. However, for the purpose of our monthly mortgage payment calculation, we need this value to be positive. To achieve this, simply add a negative sign in front.

At this point, you can start experimenting with these three numbers to observe the impact on the monthly payment. As a fun activity, if you’ve watched Ollie’s video on buying a house, you can input the numbers from his video into the calculator to check for any discrepancies. Share your findings in the comments below!

However, we still need to determine the total interest owed to the bank over the loan term and explore the potential savings if we make extra payments alongside the monthly installments within the 30-year timeframe.

Calculating Monthly Interest Payments

Let’s begin in cell B10, assuming the first payment due date is January 1st, 2022. The initial balance is $400,000. Since the monthly payment remains constant, you can press FN + F4 to lock this value in place. To calculate the interest, multiply the beginning balance by the monthly interest rate (3.3%, locked by FN + F4) and divide by 12. The principal payment is simply the monthly payment minus the interest payment. In other words, the sum of interest and principal should equal the monthly payment. For now, we’ll skip the extra payment calculation.

The ending balance is derived from subtracting the principal amount you’ve paid back so far, along with any extra payment made during that month, from the beginning balance. This is the first month of loan repayment. The total interest at this point is equal to the interest accrued in the first month. Similarly, the total principal is the sum of the first month’s principal payment and any extra payments made, as the extra payment is allocated towards reducing the principal, not the interest. It’s important to keep this distinction in mind.

Now, pay close attention to row two. Begin by using the EDATE formula and referencing the first payment due date. Increment the month by one each time. The beginning balance for each month is simply the ending balance from the previous month. To calculate the monthly payment, apply the Minimum function. The first field is the monthly payment (FN + F4 to lock), and the second field is the beginning balance for that month plus the interest for that month. The reason for this calculation will become clear later in the video. Since the interest, principal, and ending balance remain constant, highlight these cells and the row below, then use the Cmd or CTRL + D shortcut to drag the formulas down.

For the month number, simply add one to the cell above. The total interest is the sum of this month’s interest and the total interest paid back so far. Similarly, the total principal is the sum of this month’s principal, any extra payments made, and the total interest paid back so far. These three numbers need to be calculated.

You can now highlight the second row and drag it all the way down to row 369 using Cmd or CTRL + D. Voila! Everything is now automatically calculated. At the end of the 369th month or the 30th year, the ending balance should be zero. You can quickly change the colors back to their original settings.

Verifying Your Calculations

Now, let’s double-check our calculations to ensure accuracy. We want to confirm that the total cumulative principal we pay back matches our initial loan amount. To accomplish this, we utilize the Maximum function on the corresponding row. As we observe, the value of $400,000 matches our initial loan amount precisely.

It’s also worth noting the total amount of interest we need to repay to the bank. To determine this, we once again employ the Maximum function on the Total Interest row. As displayed, the total interest payment we owe over the course of 30 years amounts to a staggering $230,000. It can be quite overwhelming to comprehend.

To analyze how the total interest is impacted by the initial loan terms, you can use the Cmd or CTRL + C shortcut to copy the values. Then, use Cmd or CTRL + Shift + V to paste without formatting. Calculate the difference by subtracting the original interest from the total interest. Currently, since these two values are identical, the difference is zero.

Impact of Interest Rate on Total Interest

Suppose we were able to secure a more favorable interest rate of 3%. In this scenario, the new total interest owed would be $207,000. Consequently, we would pay $23,000 less in total interest compared to the previous rate. On the other hand, if the interest rate were to increase by a mere 1.5% to 4.5%, our additional payment would amount to approximately $100,000.

Now, let’s explore the effect of making extra payments in reducing both the total interest paid and the loan repayment period. As an example, let’s consider taking $5,000 from our annual bonus and applying it towards the loan.

Impact of Extra Payments on Total Interest

Let’s consider the scenario where I plan to make a one-time extra payment of $5,000 from my annual bonus every year for the first 10 years. Despite the total principal remaining unchanged at $400,000, I end up paying $53,000 less in interest payments. If you scroll down to the bottom, you’ll notice that I actually complete all my loan repayments by the 301st month, which is approximately 25 years instead of the original 30 years.

By making these additional payments earlier on, I reduce the overall amount I have to repay and expedite the repayment process. This occurs because the extra payment reduces the ending balance for that particular month. As a result, the beginning balance for the next month is lower, leading to a decrease in the interest amount paid for that month. This allows a larger portion of my monthly payment to be allocated towards paying off the principal amount.

Useful Tips for this Mortgage Calculator

Now is an opportune moment to discuss why we utilized the Minimum function at the beginning. If you scroll down to the very last payment, you’ll notice that the amount is actually lower than the initial monthly payment. Had we not employed the Minimum function and simply copied and pasted the initial monthly payment, we would have ended up paying more than necessary. It’s unlikely that your bank would notify you of this error.

One final pro tip: This template can be used to calculate any fixed rate loan, not just mortgages. All you need to know are the loan amount, the interest rate, and the loan repayment period. If you have any requests for an adjustable rate mortgage calculator, please let me know in the comments. Essentially, it would be a repetition of the same calculations, but with differing interest rates over time.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top