While there are plenty of phone apps and online calculators, a Google Sheets mortgage calculator template can make working within spreadsheets related to buying a home much easier. The best way to use one is to simply copy and paste an existing template into your new spreadsheet. Luckily for you, we have a template ready to go. Weâ€™ll also tell you how it works and how to build one for those who want to learn the nuts and bolts. Read on to learn more.

**Download The Google Sheets Mortgage Calculator Template**

Table of Contents

## How to Build a Mortgage Calculator Google Sheets Template

So you donâ€™t have to build it yourself, we have created a free spreadsheet templateÂ that you can download to calculate your mortgage. Although the Google loan payment calculator spreadsheet may look very simple on the surface level, a lot is going on in terms of the formulas used.

Related: I made a golf calculator spreadsheet too!

Let’s take a look at some of the elements in the mortgage calculator.

The first 6 cells in the spreadsheet require the user to enter the values. These include data such as the **Home Purchase Price**, **Down Payment**, **Mortgage Interest Rate**, **Mortgage Years**, **Annual Taxes**, and **Annual Insurance**.

The first thing to do is to find out the **Mortgage Loan Size**. You do this by subtracting the purchase price from the down payment. To enter the formula, click on the cell and enter the **Equals (=)**Â sign. Now, enter the cell addresses containing the values you wish to add. In this example, the formula used is **=B3-B4**.

Now, we will use the formula to calculate monthly payments to find the **Mortgage Payment**. To do this:

- Click to select the cell where you wish to enter the formula and enter the starting part of the PMT formula. This will look like
**=-PMT(**. - Enter the
**rate**Â parameter, which is the**Mortgage Interest Rate**Â divided by**12.**Enter a comma to separate the parameters. - Enter the
**period-number**, which is the**Mortgage Years**multiplied by**12**. Add another comma. - Enter the
**present-val**Â which is the**Mortgage Loan Size**Â we calculated before. - Add a closing bracket and press the
**Enter**key to save the formula.

To find the monthly PITI, we are going to use the following formula:

=Mortgage Payment + ((Annual Taxes + Annual Insurance) / 12)

In the Google Sheets debt payoff template, this formula looks like this:

=B11+((B7+B8)/12)

For our spreadsheet template, we separated the values the user needs to input from the values calculated using the fill color. Values that need to be input by the user are indicated in yellow. The results are shown in a grey color.

**Related:**Â Debt Snowball Spreadsheet Template + Guide (Debt Free 2024)

## Types of Loan Payments

The process for calculating loan payments is straightforward. However, there are a lot of different types of loans to consider, and they interact with a mortgage calculator differently. Here are some types of loans, and how you can calculate the payments.

### Interest-Only Loans

An interest-only mortgage requires the borrower to pay the interest on the loan for a fixed amount of time. The payments can be either the entire sum at a specified date or subsequent payments at fixed intervals. In interest-only payments, you will be making smaller payments for a while. However, this stops you from building equity, and there will be a massive jump in the payments whenever the interest time ends.

You can calculate monthly payments for an interest-only loan by multiplying the total loan amount by the interest rate. Then divide the amount by 12 to determine how much you need to pay monthly. Here is the formula youâ€™d use:

=(interest-rate * loan-amount) / 12

### Fixed-Rate Mortgage

In a fixed-rate mortgage loan, the interest rate is kept the same for the loan’s lifetime, which means that the monthly mortgage payments will never change. In most countries, fixed loans generally come in 1-5 year fixed terms, although some allow you to pick a period of up to 10 years. Then the loan will switch to an adjustable rate. But, in the US, fixed-rate home loans can be for the loan’s entire life (30 years).

However, if the interest rate falls, you will have to refinance to get lower rates. Interest rates generally are higher than the rates on mortgages with adjustable rates.

### Adjustable-Rate Mortgages

The interest rates can fluctuate in adjustable-rate mortgages due to varying market conditions. As we mentioned, some ARM terms have fixed interest for the first few years before the lean term changes to a changeable interest rate for the remaining term.

Although it can save you substantial money, the monthly payments can become unaffordable, resulting in a loan default if the interest rates were to skyrocket.

### Amortized Loans

In an amortized loan, scheduled payments are applied to the loan’s original sum and the interest accrued. In amortized loans, the interest expense for the period is paid off first, after which the remaining payments are made to reduce the total sum. As the interest part of the payment decreases, the original loan amount is increased.

The formula to calculate amortized loans is a bit complex. The formula is:

=loan-amount / [{((1 + interest-rate) ^ number-of-payments) - 1} / {interest-rate (1 + interest-rate) ^ number-of-payments}]

**Related:**Â Loan Amortization Schedule Google Sheets Template + Guide

## What Is PMT Google Sheets?

The PMT is a Google Sheets amortization formula that allows you to calculate the loan’s monthly payments. This Google Sheets mortgage formula can be used to budget for loans or compare various loan options. The formula considers key factors like time, interest rate, and loan amount.

## Google Sheets PMT Function Syntax

We will use the PMT formula to make a Google Sheets loan payment template. Let’s take a look at the formula:

=PMT(rate, period-number, present-val, future-val, end-or-start)

Now that we know the formula’s syntax let’s look at the parameters. These are:

**rate:**this parameter defines the interest rate.**period-number:**this parameter defines the number of payments that need to be made.**present-val:**this is the current annuity value.**future-val:**this is an optional parameter that defines the value remaining in the future after the final payment is made.**end-or-start:**This is an optional parameter that is used to specify whether the payments are due at the start or the end of each period. It can be either 1 or 0, with 1 specifying the start of the period while 0 specifying the ending.

## Similar Formulae That You Can Use

The PMT formula does not account for compounding interest which can cause your calculations to be incorrect. PMT is also unsuitable for use when you wish to calculate the current value in a series for recurring future payments, as this calculation also considers compound interest. You can learn more about Google Sheets Compound InterestÂ in our article.

You can use some similar formulae that work similarly to the PMT formula. One of these is the IPMT formulaÂ which will calculate the interest for a given time period. Another one is the PPMT formula, which calculates principal payments for a given time period.

## Frequently Asked Questions

### Do Sheets Have the PMT Function?

Google Sheets have a PMT function that allows you to calculate the monthly payments for taking out a loan. The formula considers key factors like the time period, interest rate, and loan amount.

### What Is the PMT Formula?

The PMT formula is =PMT(rate, period-number, present-val, future-val, end-or-start) and requires three basic parameters. These are the **rate **that defines the interest rate, **period-number**Â that defines the number of payments that need to be made, and **present-val**, which is the current annuity value.

## Wrapping Up

Hopefully, this Google Sheets mortgage calculator template will help you make some smart financial decisions. If you found it useful, you may also like some of our other templates and finance content in the related section below.

**Related:**

- Free Google Sheets Task List Template
- Easy-to-Use Google Sheets Project Management Templates
- Timesheet Templates for Google Sheets
- How to Build a Dashboard in Google Sheets
- NPER function in Google Sheets
- How to Use the NPV Google Sheets Formula
- GOOGLEFINANCE Function: An Easy Beginnerâ€™s Guide
- How to Convert Currency in Google Sheets (Google Finance Function)
- Google Sheets Forecast Function: Easy Beginnerâ€™s Guide