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
