5 Best FREE Google Sheets Mortgage Calculator of 2024 (How to Create One)

Google Sheets offers powerful features to manage your loans like a pro. You can build fully featured loan calculators like mortgage calculators to know monthly installments, total years required to completely pay off the debt, total years saved by extra payments, and much more.

The demand for land, homes, cars, etc., is rising with an ever-increasing population.

More and more people are seeking short and long-term loans from banks. Most of them don’t know how to calculate monthly installments and the years required to completely pay off the loan if they will pay an extra amount monthly.

This is a quick guide to Google Sheets Mortgage Calculator. Let us discuss the top spreadsheet templates and instructions to use them. We will also learn the steps to create a Mortgage Calculator from scratch in Google Sheets.

What should a Mortgage Calculator template include?

A Mortgage calculator is a simple tool that takes the user’s inputs about the loan amount, interest rate, term, and more to precisely calculate monthly installment amount, total interest paid, reduced number of months in case of extra payments, etc.

Simply put, there are two tables:

Loan Values
Summary

You need to enter loan information in the first table, whereas the second table is fully automated to calculate various things.

The following information is crucial and must be provided by the users in the first table for accurate calculations:

Loan Amount
Annual Interest Rate
Term in Years
Start Date
Number of Payments Per Year
Extra Optional Payment (Monthly)

You can use the same information to create an amortization schedule.

There are various third-party apps and websites offering Mortgage calculator templates for free. However, creating your calculator in Google Sheets allows you to customize the template as per your needs.

Why is a Mortgage Calculator created using Google Sheets a must-try?

Google Sheets is an online spreadsheet program. It boasts numerous tools and functions that precisely calculate various aspects of loans, giving users a quick overview of the entire lifecycle and cash flow.

Here are a few of the benefits of Google Sheets for Mortgage calculations:

Accuracy – Google Sheets is a reliable spreadsheet program used by millions of users. It offers 400+ formulas to perform arithmetic, conditional, and statistical operations accurately.
Ease of Use – An intuitive user interface allows access to most of the powerful tools and functions right from the Google Sheets dashboard. Beginners may hardly take a couple of minutes to use the Mortgage calculator in Google Sheets.
Collaboration – Spreadsheet templates created using Google Sheets can be shared with unlimited users. It boasts a real-time collaboration feature. You, as the owner of the calculator, can quickly view and undo the changes made by the users.
Customization – Google Sheets offers extensive customization. You can easily change font styles, adjust background colors, insert logos, and much more to add a personal touch to your calculator template.
Accessibility – As an online spreadsheet program, Google Sheets can be accessed from anywhere in the world through both mobile and desktop devices.

In a nutshell, Google Sheets offers an excellent way to evaluate your mortgage loan to let you make informed decisions about your future.

Top 5 FREE Google Sheets Mortgage Calculator Template

There are a handful of Google Sheets Mortgage Calculators available online. We have come up with the best ones that automatically calculate various things based on the loan information provided by you.

Let’s begin,

1. Mortgage Calculator by SoftwareAccountant

FREE Download - Google Sheets Mortgage Calculator Template

Click Here To Use This Template!

This calculator template by SoftwareAccountant is our top pick on this list because of its comprehensive and professional-looking design.

You need to provide the information about your loan amount, annual interest rate, term, etc., at the top of the template.

Then, the template uses formulas to calculate monthly installments automatically, the total number of payments that need to be made, and the total interest you will pay over the given period.

It also auto-populates the amortization or payment schedule in the last table of the template. This is an additional feature missing on most of the Mortgage calculator templates available on the internet. You can quickly overview how each monthly payment contributes towards the principal amount and total interest that needs to be paid.

2. Mortgage Payoff Calculator by cloudHQ

Click Here To Use This Template!

If you are not impressed by the design of the previous template, then here is one more for your taste.

It comes with similar features. The basic idea is to get the loan information from the user and calculate various things such as monthly installment, total years to completely pay off the debt, amortization schedule, and more.

The only added feature is that the template also tells how old you would be at the time when the debt will be completely paid off.

The template uses advanced formulas; make sure you do not edit any cell-holding formula; otherwise, the template won’t work as expected.

3. Mortgage, Downpayment, and Affordability Calculator

Click Here To Use This Template!

This advanced template comes with additional features compared to previous ones.

It is going to ask you various things, from annual income to monthly property tax, to answer if you should have the loan or not.

There comes a bit of a learning curve. But don’t worry because it includes clear instructions for every cell in the spreadsheet.

Though the design of the template may not impress you, it is still a super helpful template for everyone.

4. Simple Google Sheets Mortgage Calculator by Spreadsheetpoint

Click Here To Use This Template!

This template has made its place in our list because of its simplicity and ease of use.

Anyone can use it without any prior experience. The template is almost identical to all of the basic Mortgage Calculators by third-party apps and websites.

You need to enter the loan information in the first table.

All the cells highlighted with yellow background color are used for adding the loan information to the calculator.

Once the information is provided, the template calculates the exact loan size, monthly installment, and PITI (Principal, Interest, Taxes, and Insurance Total).

5. Mortgage Calculator for Lenders by Lido

Click Here To Use This Template!

This is our final pick on this list, which is again similar to most of the mortgage calculators you may see on third-party apps and websites.

It is mainly helpful to loan providers. Then can quickly calculate the monthly installment amount during the client call.

Please avoid editing the cells containing the formulas as it will prevent the proper functioning of the calculator.

How to Create a Mortgage Calculator in Google Sheets from Scratch

Creating a mortgage calculator in Google Sheets should not take more than a couple of minutes with the following step-by-step guide.

Have a look at the following image,

It is a simple mortgage calculator with two tables. The first gets the inputs from the users, and the second one auto-calculates various things using those inputs.

Before we jump into the actual steps, let us understand one crucial function that we are going to use to create our mortgage calculator.

Explained: The PMT Function in Google Sheets

This formula is helpful in case of any long-term payment situations such as mortgage, home, or car loan. It calculates EMI (Equated Monthly Installments) over a given period.

Here’s the general syntax for the function,

=PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])

There are five different arguments in the formula. You need to replace them with the proper information as follows:

“rate” – It represents the rate of interest for the loan
“number_of_periods” – The total number of payments that need to be done between the given loan period
“present_value” – Here, you need to enter the exact principal amount during the first month. It is also called the current value of the annuity
“future_value” – It is an optional argument where you need to specify the remaining amount after the final payment
“end_or_beginning” – This is another optional argument in the formula with a default value of 0. It means the payments will done at the end of each period

You can either enter the values manually or provide the cell references in the place of the above arguments.

Now, without any further ado, let’s use this formula to create a mortgage calculator.

STEP #1 – Add the Header

The header is a crucial component of any template. It allows us to add titles or specific information that adds a personal touch to a template.

Open a new Google Sheet by clicking here

Create a new Google spreadsheet by clicking here
Select the entire first row
Increase the height as shown below
Next, select all the cells from A1 to D1
Hover to the toolbar section that sits below the main menu
Click the “Merge cells” icon as shown in the above image
Type “Mortgage Calculator“
Increase the font size to 20
Make the font bold
Change the background color to green (Hex Code – #00ac46)
Change the font color to white
Align the text to the middle

Here’s how our header should look after following the above steps.

STEP #2 – Create Loan Values Table

In this step, we will learn to add a table to Google Sheets with proper formatting. It will be used to add your loan information to the calculator.

The table should include the following fields,

Loan Amount
Annual Interest Rate
Term in Years
Number of Payments per Year
Extra Optional Monthly Payment

Let’s begin,

Select the cell “B3“
Type “Loan Amount“
Now, repeat the above steps and complete the table as shown below,
Select the entire column B
Increase the width
Now, select the table range “B3:C7“
Go to the toolbar and click the “Borders” icon
Change the border color to light gray
Again, click the Border icon from the toolbar section and choose “All borders”

Our table is ready. But we need to format cells so that whenever we enter the values, they are reflected as currency or percentage.

Select the cell “C3” and “C7”
Hover to the main menu and click on “Format” tab
Choose the “Number” option from the popup
Next, select the “Currency” from the list

Now, whatever values entered in the cells C3 and C7 will be displayed as currency.

Similarly, let’s format the cell C4 so that it will be reflected as a percentage.

Select the cell “C4“
Hover to the main menu and click on the “Format” tab
Next, choose the “Number” option followed by the “Percentage” from the list, as shown in the following image,

With these steps, the Loan Values table is all done.

STEP #3 – Create Loan Summary Table

Here, we will be using various formulas in Google Sheets to calculate the following things automatically,

Monthly Installment
Total Number of Payments
Total Payment
Total Interest Cost

We will use the SUM and PMT functions discussed earlier in this section.

Select the cell “B10“
Type “Monthly Installment“
Next, complete the table as shown below,
Select the range “B10:C13“
Hover to the toolbar section and click on the “Borders” icon
Then, select “All borders” from the available options

The table is ready; now, let us use the formulas. We will start with calculating the monthly installment amount.

Click on the cell “C10“
Type “=pmt”
Select the first option from the popup
Replace the rate argument with “C4/12“
(Here, the C4 cell contains the annual interest rate)
Press “,” to move to the next argument of the PMT function
Put “C5*12” in the place of the number_of_periods argument
(The cell C5 contains the term in years or the period required to pay off the loan completely)
Press “,” on your keyboard
Replace present_value argument with “-C3“
(The cell C3 includes the total loan amount)
Complete the parentheses using “)” on your keyboard