Google sheets mortgage calculator with taxes and insurance

How to Calculate Mortgage Payments in Excel With Home Loan Amortization Schedule & Extra Payments

Calculator Rates

Want to Calculate Mortgage Payments Offline?

We have offered a downloadable Windows application for calculating mortgages for many years, but we have recently had a number of people request an Excel spreadsheet which shows loan amortization tables.

Our Excel mortgage calculator spreadsheet offers the following features:

• works offline
• easily savable
• allows extra payments to be added monthly
• shows total interest paid & a month-by-month amortization schedule

Microsoft Excel Mortgage Calculator Spreadsheet Usage Instructions

The calculator updates results automatically when you change any input.

loan amount - the amount borrowed, or the value of the home after your down payment.

interest rate - the loan's stated APR. For your convenience, we publish local Atlanta mortgage rates below to help you see currently available rates.

loan term in years - most fixed-rate home loans across the United States are scheduled to amortize over 30 years. Other common domestic loan periods include 10, 15 & 20 years. Some foreign countries like Canada or the United Kingdom have loans which amortize over 25, 35 or even 40 years.

payments per year - defaults to 12 to calculate the monthly loan payment which amortizes over the specified period of years. If you would like to pay twice monthly enter 24, or if you would like to pay biweekly enter 26.

loan start date - the date which loan repayments began, typically a month to the day after the loan was originated.

optional extra payment - if you want to add an extra amount to each monthly payment then add that amount here & your loan will amortize quicker. If you add an extra payment the calculator will show how many payments you saved off the original loan term and how many years that saved.

Enable Your Worksheet to Calculate Your Loan Payments

How to Enable Your Mortgage Calculation Spreadsheet

When you download Excel spreadsheets from the web they download in PROTECTED VIEW.

You need to click on [Enable Editing] in the yellow banner at the top of the spreadsheet to change variable amounts.

By default this calculator is selected for monthly payments and a 30-year loan term. A person could use the same spreadsheet to calculate weekly, biweekly or monthly payments on a shorter duration personal or auto loan.

Some of Our Software Innovation Awards!

Since its founding in 2007, our website has been recognized by 10,000's of other websites. A few of our software innovation awards are listed below:

Current Mortgage Rates

The following table shows locally available mortgage rates which you can use to help calculate your monthly home loan payments.

Atlanta Homeowners May Want to Refinance While Rates Are Low

The Federal Reserve has started to taper their bond buying program. Lock in today's low rates and save on your loan.

Are you paying too much for your mortgage?

Find Out What You Qualify For

Check your refinance options with a trusted Atlanta lender.

Answer a few questions below and connect with a lender who can help you refinance and save today!

Ever been curious about how to create a loan amortization schedule in Google Sheets? Then you’re in luck! Spreadsheet software like Google Sheets are there to help you get organized and automate everyday tasks, whether you want to maintain a budget, track expenditures, schedule activities or pay off loans on time.

An amortization table can be a really powerful way to see how your monthly payments can affect the overall cost of your loan, be it a mortgage, a car loan, or a personal loan.

In this tutorial, we will demonstrate from start to finish, how you can create your own Loan Amortization schedule just by using Google Sheets. This means you don’t have to go looking for fancy apps and can have complete control over your loan repayment process, simply by using a few Google Sheets formulas.

• What is a Google Sheets Loan Amortization Schedule?
• What Does a Loan Amortization Schedule Google Sheets Spreadsheet Consist Of?
• How to Create a Loan Amortization Schedule in Google Sheets
• Creating the Basic Outline of the Google Sheets Mortgage Amortization Schedule Spreadsheet
• Filling in the Main Amortization Table
  • Period
  • Date
  • Monthly Payment
    • The PMT Function
  • Interest Payment
    • The IPMT Function
  • Principal Payment
  • Loan Balance
• Summarizing the Information in the Last Row in The  Loan Amortization Calculator Google Sheets Template
• Monthly Amortization Schedule Google Sheets FAQ
  • How Do I Create a Loan Amortization Schedule in Google Sheets?
  • Does Google Sheets Have a Loan Amortization Schedule?
  • Can I Make My Own Amortization Schedule?
  • How Do I Calculate Loan Payments in Google Sheets?
  • What Are the Parts of the Amortization Loan Schedule?
  • Does Google Sheets Have PMT Function?
• If You Missed The Template

What is a Google Sheets Loan Amortization Schedule?

An amortized loan is a type of loan that involves periodic payments scheduled over a given period of time. The payments are applied to both the loan’s principal amount as well as the interest.

The process consists of first paying off the relevant interest for the period, so that the rest of the payment can be put towards reducing the principal amount.

What Does a Loan Amortization Schedule Google Sheets Spreadsheet Consist Of?

A loan amortization spreadsheet consists of a schedule of periodic loan payments. The schedule shows the principal amount and amount of interest for each payment from the start till the loan is paid off at the end of the term.

The repayment process consists of paying the same amount in each periodic payment. However, during the start of the process, most of the amount pays off the interest, while towards the end, most of the amount covers the principal amount.

This, in turn, makes the repayment process comfortable for the borrower and reduces the overall cost of the loan.

The last line of the table shows the borrower’s total interest and principal payments for the entire term.

How to Create a Loan Amortization Schedule in Google Sheets

So now that we know the basics, let’s get down to actually building the spreadsheet.

We will first start by creating a basic skeleton of the sheet, in which we will enter the basic timeline, the starting date, interest rate of the loan and the total amount to be paid off.

After this, we will create the table and fill in the first two rows with the appropriate formulas. In the end all we need to do is simply drag down the formulas to fill in the rest of the schedule. The last row should display an amount of 0.0 in the loan balance column.

Creating the Basic Outline of the Google Sheets Mortgage Amortization Schedule Spreadsheet

Let us start with the main spreadsheet outline.

Click here to view our Loan Amortization Schedule Spreadsheet template that we have created for you.

Simply click File -> Make a Copy, so that you can edit and save your own document.

The top part of the worksheet (rows 1 and 2) consists of all the values that are going to remain constant throughout the loan period:

• The Principal amount for the loan, or the total loan amount that needs to be paid off.
• The interest rate that is to be applied on the loan.
• The term (in years) or the time period within which the loan is to be repaid.
• The starting date from when the loan repayment schedule begins.

Let us assume we have a home loan of $100,000 to be repaid in 10 years, with an interest rate of 5%, starting from 06/29/2021. So let us fill these values in the table:

Once this is done, you can start filling in the lower part of the worksheet.

Filling in the Main Amortization Table

Now it is time to fill in the main part of the worksheet. Here are the columns you will need to fill:

Period

The Period column will consist of the serial number of the payment period. So for the first installment, the period will be 1, for the second installment it will be 2, etc.

Enter 0 in the first row of this column and 1 in the second row.

Date

The Date column consists of dates when each payment is due. In the first row, the date is the same date as the starting date (of cell E2). After this, each date will be one month apart (since you are paying off each installment on a monthly basis).

So in the first row of this column, enter the formula: =E2

In the second row, enter the formula:

=DATE(YEAR(B7),MONTH(B7)+1,Day(B7))

This will return a date exactly one month after the date in cell B7.

Monthly Payment

Next we need to calculate the monthly payment for each row. To calculate the monthly payment we can use Google Sheets’ PMT function.

The PMT Function

The PMT function calculates the periodic payment for an annuity investment, where the amount and interest rate are constant for each periodic payment.

The syntax for the PMT function is as follows:

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

Here,

• rate is the rate of interest for the payment period.
• number_of_periods is the number of payment installments.
• present_value is nothing but the principal amount.
• future_value is optional. It is the future value remaining after the final payment has been made.
• end_or_beginning is also optional. It specifies if the payments are due at the end (0) or start (0) of each period. By default this value is 0.

In our example, here are the values that we will need to plug in:

• rate: The interest rate in cell B2 is for a whole year, but this parameter calls for the monthly interest rate. So this value will be B2/12.
• number_of_periods: This parameter will contain the number of periods in months. Since cell E1 contains the period in years, this value will be E1*12
• present_value: The principal amount is in cell B1, so that is what we will enter in this parameter.

Since you don’t need to pay the monthly fee on the starting date, this value can be 0.00 for the first row.

In the second row, you can enter the following formula:

=PMT($B$2/12, $E$1*12,$B$1)

We locked all the cell references with $ symbols, because we don’t want these references to get updated when we copy the formula down to the rest of the rows.

Remember, the monthly payment is going to be the same amount for each period. So all the rows of this column will basically have the same value.

Note: The value returned is negative because this is an outgoing payment. In financial terms, outgoing payments are always represented as a negative number while incoming payments are represented as a positive number.

Interest Payment

This column will show the interest to be paid in each installment. To calculate the monthly interest payment, we can use the Google Sheets’ IPMT function.

The IPMT Function

The IPMT function calculates the interest payment for an annuity investment, where the amount and interest rate are constant for each periodic payment.

The syntax for the IPMT function is as follows:

IPMT(rate, period, number_of_periods, present_value, [future_value, end_or_beginning])

Here,

• rate is the rate of interest for the payment period.
• period is the amortization period serial number.
• number_of_periods is the number of payment installments.
• present_value is the principal amount.
• future_value is optional. It is the future value remaining after the final payment has been made.
• end_or_beginning is also optional. It specifies if the payments are due at the end (0) or start (0) of each period. By default this value is 0.

In our example, here are the values that we will need to plug in:

• rate: The interest rate in cell B2 is for a whole year, but this parameter calls for the monthly interest rate. So this value will be B2/12.
• period: This will contain the serial number of the payment period. This value is contained in column A for each corresponding row, so for the second row, the value will be A8.
• number_of_periods: This parameter will contain the number of periods in months. Since cell E1 contains the period in years, this value will be E1*12
• present_value: The principal amount is in cell B1, so that is what we will enter in this parameter.

Since you don’t need to pay the interest amount on the starting date, this value can be 0.00 for the first row.

In the second row, you can enter the following formula:

=IPMT($B$2/12, A8, $E$1*12,$B$1)

We locked all the cell references with $ symbols, because we don’t want these references to get updated when we copy the formula down to the rest of the rows.

The only reference we did not lock is the second one (representing the period), because this value needs to change with every row.

Principal Payment

This column consists of the principal amount that needs to be paid for the current installment. This value can be obtained by subtracting the current interest payment from the current monthly payment value. So, for the second row, enter the formula:

=C8-D8

The first row will again carry the value 0.00.

Loan Balance

This column will hold the amount of loan yet to be repaid. This value can be obtained by subtracting the current principal payment from the previous loan balance.

In the first row, since no money has been paid yet, this value will contain the entire principal amount (or a reference to cell B1).

In the second row, the value will be:

=E8+F7

Since the Principal payment amount is a negative number, we used the ‘+’ sign, instead of ‘-‘.

Once you have the first two rows filled in, all that’s left to do is copy down the values to the rest of the rows. Do this by selecting the entire row and pulling down the fill handle to the number of rows needed.

This means, if you have to repay the loan in 10 years, you should have 10*12=120 rows.

Your last row should have a loan balance of 0.0.

Summarizing the Information in the Last Row in The  Loan Amortization Calculator Google Sheets Template

The last row of the amortization table should show the borrower’s total interest and principal payments for the entire term.

The total interest can be calculated by summing up the values in column D . So in cell C129 enter the formula:

=SUM(D7:D)

The principal payments for the entire term is calculated by summing up the values in column E. So in cell C130 enter the formula:

=SUM(E7:E)

Your loan amortization schedule Google Sheets spreadsheet is now ready, and since the formulas have been interlinked with the appropriate cell references, any changes in the principal amount, interest rate, term or starting date will get automatically updated everywhere.

Monthly Amortization Schedule Google Sheets FAQ

How Do I Create a Loan Amortization Schedule in Google Sheets?

You’ll need to use a combination of the following financial functions, you could consider each a Google Sheets amortization formula:

• PMT
• IPMT

Alongside quite a few standard functions. You can learn how to build one in this article, or simply download our template.

Does Google Sheets Have a Loan Amortization Schedule?

There is no default loan amortization schedule in Google Sheets. But, you can build one from scratch or use a template like the one in this article.

Can I Make My Own Amortization Schedule?

Yes, it’s relatively straightforward to make your own amortization schedule in Google Sheets once you understand the PMT function.

How Do I Calculate Loan Payments in Google Sheets?

To calculate loan payments in Google Sheets, you need to use the PMT function.

The PMT function calculates the periodic payment for an annuity investment, where the amount and interest rate are constant for each periodic payment.

The syntax for the PMT function is as follows:

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

What Are the Parts of the Amortization Loan Schedule?

There are three necessary components of an amortization loan schedule. They ere:

• Scheduled payments
• Interest expenses
• Principal repayment

Does Google Sheets Have PMT Function?

Yes, Google Sheets does have a PMT function. It works similarly to the equivalent function in Microsoft Excel.

If You Missed The Template

Here is the Google Sheets Loan Amortization template, with all formulas included.

Again, simply click File -> Make a copy. Now you can add in whatever custom data you like and the formulas will automatically update.

This amortization schedule in Google Sheets can be easily customized to any kind of loan. All you need to do is customize the values to suit your requirements. We hope this was helpful.

Read also:

• How To Make a Schedule on Google Sheets

How do I calculate mortgage payments in Google Sheets?

What is the PMT formula in Google Sheets?

What is the formula for mortgage calculation?