5 Best Excel Mortgage Calculator [FREE Download] of 2024 & How to Create One

A Mortgage is often the largest financial burden when you are planning to buy a home or property. You can use Excel to create fully-featured loan calculators to know monthly installments, total years or months to pay off the debt, etc.

It is a powerful spreadsheet application featuring a number of built-in tools and formulas to calculate the EMIs precisely.

Welcome to this guide, where we will discuss 5 free Excel Mortgage calculators. At the end, we will also learn the steps to create a Mortgage Calculator from scratch, so please make sure to read the article until the end.

Benefits of using a Mortgage Calculator Spreadsheet

Such spreadsheets are designed to help users estimate the monthly EMIs and total interest paid and understand the financial implications. Their main goal is to determine how much a customer will have to pay each month based on various financial factors.

It consists of two tables:

Loan Values
Summary

In the first table, you need to enter loan information, whereas the second table is automated to calculate various things.

Here are the key things to list for accurate calculations:

Loan Amount: The amount you borrow from the lender or bank to purchase a home/ property.
Annual Interest Rate: It is the interest rate charged by the lender on your mortgage annually.
Loan Term in Years: The time duration in which you have to repay the loan.
Start Date: It is the date on which you have taken the loan.
Number of Payments Per Year: This defines the number of payments you need to make every year to repay your loan.
Extra Optional Payment (Monthly): It is the extra payment you wish to offer to pay off your loan faster.

Top 5 FREE Mortgage Calculator Excel Template

There are tens of Excel mortgage calculators available on the internet. We have compiled a list of the top 5 FREE Excel Mortgage calculator templates to make your life easier.

1. Mortgage Calculator by Software Accountant

Download FREE Excel Mortgage Calculator with Extra Payments

Click Here To Use This Template!

This detailed, professional-looking Mortgage calculator template is designed by experts at SoftwareAccountant. You can download it for free and customize it without any limitations.

Start by entering the loan details like the loan amount, annual interest rate, term, etc., at the top of the template. The template automatically calculates,

Monthly installments
The total number of payments that need to be made.
The total interest that is to be paid over the given period

It contains a massive table to auto-populate the amortization or payment schedule. You can easily see how each monthly payment is divided between paying off the principal and covering the interest due.

This is an extra feature that many mortgage calculator templates available online typically do not include.

2. Home Mortgage Calculator by Vertex42

Click Here To Use This Template!

The above comprehensive mortgage calculator template by Vertex42 provides the quickest and easiest way to do various things,

Calculating your monthly mortgage payments
Tracking extra payments,
View total savings from the extra payments made over time

The best part about this template is that it allows you to project balances and payments over time and provides a visual representation of interest rate history and outstanding balances.

Note that it allows you to analyze fixed or variable-rate home mortgages.

The template features sections for loan amount, interest rate, term length, and start date. It also calculates PITI payments accounting for property taxes, insurance, and private mortgage insurance (PMI).

3. Mortgage Calculator Template

Click Here To Use This Template!

Are you looking for a template that can help you quickly repay your home loan?

Have a look at this template. It offers a simple way to calculate monthly payments and visualize the amortization schedule. At the top, you can enter values like loan amount, interest rate, term length, and start date. A considerable table provides a detailed breakdown of each payment, showing you how much principal and interest you have to pay.

The template also includes an option for extra payments, which helps to reduce the loan term and interest paid.

4. Simple Mortgage Calculator by Spreadsheetpoint

Click Here To Use This Template!

This is the simplest mortgage calculator on the list, thanks to its minimal design and straightforward terms.

Note that the template is automated with built-in formulas to determine the Mortgage loan size, Mortgage payment, Monthly Principal, Interest, Taxes, and Insurance (PITI) Payment for you.

Enter the inputs in the yellow-colored cells, and do not touch the gray-colored ones, as they contain the formula. If you have accidentally changed any of the cells, you can revoke the changes by pressing “Ctrl + Z.”

5. Mortgage Payoff Calculator by cloudHQ

Click Here To Use This Template!

This template is our final pick. It helps you gather loan details and calculate various aspects, such as monthly payments, total repayment years, and an amortization schedule.

This template’s unique feature is that it estimates the age at which you will be when the loan is fully paid off.

The template uses built-in formulas, so it’s important not to edit any formula cells, as doing so might affect the template’s performance.

How to Create a Mortgage Calculator in Excel from Scratch

If you are not satisfied with the above-discussed templates, you can create a new mortgage calculator template from scratch in Excel. It will take only a few minutes.

Refer to the following image; we will be creating the same template as shown below.

This is a simple mortgage calculator which contains two tables. In the first table, you need to enter the inputs, and the template auto-populates the second table.

STEP #1 – Create the Header

The header is the most crucial element of any template. You can add titles or any specific information to make it look more personal.

Here are the steps for creating a header:

Open a new Excel spreadsheet
Select the entire first row
Increase the height of the first row
Select the cells “A1” to “D1”
Hover to the main menu
Click on the “Merge & Center” option from the “Alignment” group
Type “Mortgage Calculator”
Increase the size of the heading to 20
Make the font bold
Adjust the alignment to the center
Change the background color to green (Hex Code – #00ac46)
Change the font color to white

Now, your header must look like,

STEP #2 – Create the Input Table

In this step, we will create a table to input the essential loan details. We will be adding the critical loan details like,

Loan amount
Annual Interest rate
Loan term
Number of payments per year
Extra optional monthly payment

Let’s get started,

Select the cell “B3”
Type in “Loan Amount”
In the cell “B4”, enter “Annual Interest rate”
Similarly, repeat the above steps to complete the table below
Select the column B
Increase the width of the column to accommodate all the fields
Select the entire table from “B3” to “B7”
To apply the borders, click on the “Borders icon” in the “Home” tab
Select the “More Borders” option
In the Format Cells dialog box, go to the Borders tab, click on the “Color” dropdown
Select the color light gray and click the “OK” button

That’s it! Our input table is ready.

Now, we need to format the cells so that whenever you enter the values, the entered data is taken as Currency or Percentage.

Note that the cells C3 and C7 should be formatted as Currency.

For that,

Select the cell “C3”
Hover to the main menu and click on the “Home” tab
Select the dropdown in the “Number” group that displays “General”
Select “Currency” from the dropdown menu

Doing this will ensure that the number entered in cell C3 is taken as Currency, with a dollar symbol displayed at the leftmost side of the cell.

Next, the cell C7 can be formatted as currency using the steps discussed above.

Finally, let’s format cell C4 to display the entered number in percentage,

Select the cell “C4”
Hover to the main menu and click on the “Home” tab
In the “Number” group, click on the “General” dropdown
Click on “Percentage”

Now, whenever you enter a value in cell C4, it is taken as a Percentage.

STEP #3 – Create Loan Summary Table

In this step, we will be using various built-in formulas to calculate the following things:

Monthly Installment
Total Number of Payments
Total Payment
Total Interest Cost

The most popular Excel functions for determining the Monthly installment are SUM and PMT. We all know how to use the SUM function; it is pretty straightforward and has a simple syntax.

However, you may have yet to hear about the PMT function. So, let’s discuss it in detail.

Explained: The PMT Function in Excel

The PMT stands for payment.

It is a financial function that calculates the monthly payment (EMI) for a loan based on a constant interest rate, the number of periods, and the loan amount.

For example, if you plan to apply for a 5-year home loan with an annual interest rate of 7% and a loan amount of $4000, the PMT formula will give you the monthly installment fee.

The General Syntax of the function is as follows,

=PMT(rate, nper, pv, [fv], [type])

Where,

The “rate” is the constant interest rate per period.
The “nper” is the total number of periods over which the loan should be paid.
The “pv” is the present value, which is the total amount that all future payments are worth now.
The “fv” is the future value, that is, the balance amount you want to have after all the payments are made.
The “type” specifies when the payments are due. You can put “0” to denote that the payments are due at the end of each period and “1” to denote that the payments are due at the beginning of each period.

Before using this formula, we will have to create a summary table.

Follow the steps below:

Select the cell “B10”
Start typing “Monthly Installment“
In the cell “B11”, enter “Total Number of Payments”
Next, complete the table as shown below,
Select the entire table
To apply the borders, click on the Borders icon dropdown in the “Home” tab
Click on the “More Borders” option
In the Format Cells dialog box, go to the “Borders” tab and click on the “Color” dropdown as shown below
Select the color light gray
Click the “OK” button

The summary table is now ready.

Let’s use the formulas to automate the calculations.

We will first calculate the monthly installment amount.

Double-click on the cell “C10“
Type “=PMT”
Select the first option from the popup or press the “Tab” key
Replace the rate argument with “C4/12“
(Here, the C4 cell contains the annual interest rate)
Press “,” to move to the next argument
In the place of “nper” argument, enter “C5*12“
Press “,” on your keyboard to move to the next argument
Replace the “pv” argument with “-C3“
(The cell C3 includes the total loan amount)
End the formula with a closing parenthesis “)”

Here’s how the final formula looks,

=PMT(C4/12,C5*12,-C3)

To find the Total Number of Payments, enter the following formula in the cell C11,

=C5*C6

To calculate the Total Amount, we will simply multiply the Monthly installments by the Total Number of Payments. Select the cell C12 and enter the below formula,