WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to Calculate Mortgage Payments in Excel: Templates + Ways

December 15, 2022
6.2K Views

Mortgage payments are the installments that a borrower pays to repay the loan to the lender. Mortgage is a common way to buy property and homes. However, the calculations regarding mortgage payments can become very complex and time-consuming. Besides, inaccurate calculations of mortgage payments can lead to wrong payments, late payment fees, and foreclosure. One of the easiest ways to manage your mortgage payments is using Excel spreadsheets. There are various Excel templates available that can provide you with easy calculations of mortgage payments based on your specific interest rate and load amount.

1. Home Mortgage Calculator

Home Mortgage Calculator: This mortgage calculator excel template can help you determine your monthly mortgage payments, as well as the total cost of the loan and how much interest you will pay over the life of the mortgage.

2. Fresh Mortgage Calculator Chart

Fresh Mortgage Calculator Chart: This mortgage calculator excel template can help you create a mortgage calculator chart to track your mortgage payments, interest rates, and other important loan information.

3. Mortgage Payoff Calculator

Mortgage Payoff Calculator: This mortgage calculator excel template can help you calculate how much you will need to pay off your mortgage loan and when you will need to do it.

4. Home Mortgage Calculator

Home Mortgage Calculator: This mortgage calculator excel template can help you determine your monthly mortgage payments, as well as the total cost of the loan and how much interest you will pay over the life of the mortgage.

5. Extra Payment Mortgage Calculator

Extra Payment Mortgage Calculator: This mortgage calculator excel template can help you calculate what your monthly mortgage payment would be if you made an extra payment each month.

6. Retirement Calculator for HR

Retirement Calculator for HR: This mortgage calculator excel template can help human resources professionals calculate how much money their employees will need to retire.

7. Payment Requisition Calculator

Payment Requisition Calculator: This mortgage calculator excel template can help you calculate the amount of money you will need to requisition for your mortgage payments.

8. Debt Reduction Calculator

Debt Reduction Calculator: This mortgage calculator excel template can help you calculate how much money you will save by paying off your debts early.

9. Loan Payment Calculator

Loan Payment Calculator: This mortgage calculator excel template can help you calculate your monthly loan payments and the total interest you will pay over the life of the loan.

10. Traditional IRA Calculator

Traditional IRA Calculator: This mortgage calculator excel template can help you determine whether a traditional IRA or a Roth IRA is right for you. You can also use this template to calculate how much money you will need to save for retirement.

Excel mortgage calculator overview

1. What is the formula for mortgage calculation?

You can easily calculate the mortgage payments through various templates if you want to buy a house on loan. However, the basic formula is the same in all the templates of mortgage calculation. Following is the equation of the formula:

Where:

m represents the monthly payments

p represents the principal amount

r represents the monthly interest rate

n represents the number of payments

You can input your specific value in this formula to get the exact amount of the monthly payment for the mortgage.

2. How to create a mortgage calculator using Wps forms?

The mortgage calculator determines the monthly payments for the mortgage of a home or property. You can create your own customized mortgage calculator using the WPS forms. Following are the steps to create and use the mortgage calculator with templates in WPS Office:

Step 1: Go to the WPS office home page by clicking on the link:

https://www.wps.com/?source=academy&direct

Step 2: Click on the resources tab and go to templates

Resources Tab to access templates

Step 3: Type mortgage calculator in the search bar of the templates

Search bar to search for mortgage calculator

Step 4: Choose the desired template of mortgage, e.g., Home Mortgage Calculator.

List of home mortgage calculators

Step 5: Now you can input your desired loan amount, interest rate, term length in years, etc.

Template of home mortgage calculator

3. How to calculate adjustable-rate mortgage in Excel?

In an adjustable-rate mortgage, the interest rate remains fixed for a certain period, and after that, the interest rate can vary. Based on the changes in the interest rate, your monthly payment can also change. You can create your adjustable-rate mortgage calculator by following the below-mentioned steps:

Step 1: Click on the following link: https://www.vertex42.com/ExcelTemplates/arm-calculator.html

Step 2: Click on the download button to download Excel adjustable-rate mortgage calculator

Download link for an adjustable rate mortgage calculator

Step 3: Input your specific loan information in the loan information section:

Adjustable rate mortgage calculator in Excel

Step 4: You can calculate your monthly payments as per adjustable rate by inputting the interest rate in each row by overwriting the formula.

4. How to calculate mortgage payments in Excel? 

Besides using various templates available for the mortgage payments, you can calculate the monthly payments in Excel by inputting the information related to the mortgage. Here are a few simple steps to calculate mortgage payments in Excel:

Step 1: Open a new spreadsheet in Excel installed on your computer.

Button to open a blank spreadsheet

Step 2: Write the following categories in cells in the spreadsheet

  • Loan Amount

  • Annual Interest Rate

  • Time period of Loan (in years)

  • Number of Payments per Year

  • Total Number of Payments

  • Payment per Period

  • Total Cost

  • Interest Cost

List of categories for mortgage payment calculation

Step 3: Enter your specific values in the column next to categories

Values against the loan, interest rate and period

Step 4: Calculate the total number of payments by multiplying the time period of the loan with payments per year.

The calculation for the total number of payments

Step 5: Calculate the mortgage payment by inputting the formula of payment as =-PMT(Annual Interest Rate/Payments per Year, Total Number of Payments, Loan Amount,0)

Calculation for payment through formula

Step 6: You can calculate the total cost by multiplying the payment with the total number of payments

Calculation of total cost

Step 7: The interest cost can be calculated by subtracting the loan amount from total cost

Calculation of interest cost

5. How to create an Excel mortgage calculator with extra payments in Excel?

If you want to make extra payments to pay off your mortgage along with the steady mortgage payments, you can create this mortgage calculator in Excel by following these easy steps:

Step 1: Open the new spreadsheet in Excel

Button to open a blank spreadsheet

Step 2: Input your loan amount, load period (years) and interest rate

Inputting values of loan, interest and period

Step 3: Calculate your monthly mortgage payment by inputting the formula =-PMT(Interest Rate/Payments per Year, Total Number of Payments, Loan Amount,0)

Calculator formula for payment

Step 4: Create a table in excel with the following columns:

  • Month: It will be the starting month of payments, so it will be 1 in the first row

  • Opening balance: it will be equal to the total loan amount

  • Effective term: total number of payments to payoff the loan

  • Principle paid: amount paid every month

  • Extra payment: The extra payment that you want to make

  • Closing balance: it will be equal to opening balance minus principal paid and minus extra payment

Table to calculate the mortgage with extra payments

This calculator will help you to keep track of your mortgage, including the extra payments

6. How to create an Excel mortgage calculator with taxes and insurance in Excel?

Step 1: Open a new spreadsheet in Excel installed on your computer

Button to open a blank spreadsheet

Step 2: Write the following categories in cells in the spreadsheet

  • Loan Amount

  • Annual Interest Rate

  • Time period of Loan (in years)

  • Number of Payments per Year

  • Total Number of Payments

  • Payment per Period

  • Total Cost

  • Interest Cost

  • Yearly taxes

  • Yearly insurance

  • PITI payment

Inputting categories including tax and insurance for mortgage

Step 3: Enter your specific values in the column next to categories

Inputting values of loan, interest and period

Step 4: Calculate total number of payments by multiplying time period of loan with payments per year:

Calculation for total number of payments

Step 5: Calculate the mortgage payment by inputting the formula of payment as =-PMT(Annual Interest Rate/Payments per Year, Total Number of Payments, Loan Amount,0)

Calculator for payment through formula

Step 6: You can calculate the total cost by multiplying the payment with the total number of payments

Calculation for total cost

Step 7: The interest cost can be calculated by subtracting the loan amount from the total cost

Calculation for interest cost

Step 8: Calculate the yearly tax by multiplying the yearly tax percentage with total loan amount

Calculation for tax with 5% annual rate

Step 9: Calculate the yearly insurance by multiplying the yearly insurance percentage with a total loan amount

Calculation of insurance with 2% annual rate

Step 10: Calculate the PITI (payment including interest, tax, and insurance) by adding the monthly payment, monthly tax, and monthly insurance amount

Calculation of PITI (payment with interest, tax, and insurance amounts)

Conclusion

In conclusion, the WPS office provides mortgage calculator templates with easy and flexible mortgage calculations. You can use these templates with your specific information and save a lot of time and effort for manual calculations. The templates provide detailed information and insights about your mortgage and keep you informed about your financial obligations. Along with various templates, the article also provides useful tips and tricks to create your own mortgage calculator in Excel in easy steps. If you are the owner of a home, a potential buyer, or a financial person, these WPS templates and Excel mortgage calculation formulae will help you manage your financials in an efficient and effective way.

WPS Office offers a complete suite of office applications and programs, including word processing, spreadsheets, presentations, and PDF tools. To conveniently download WPS Office, simply click on the provided link.

What sets WPS Office apart is its provision of free access to all the features of these applications, making it an appealing choice for users. You can also get a membership at an affordable price to access many premium templates and features as well.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.