The Vertex42® Mortgage Payment Calculator is a very simple spreadsheet that lets you compare different mortgages side-by-side. It calculates your monthly payment and lets you include additional extra payment (prepayments) to see how soon you could pay off your home, or how much you could save by paying less interest. You can also estimate the equity in your home after a specified number of years.

If you are looking for a mortgage payment calculator that includes taxes, insurance, and other home ownership expenses, try our Home Expense Calculator.

Advertisement

Mortgage Payment Calculator

for Excel, OpenOffice, and Google Sheets
Mortgage Payment Calculator
⤓ Download
For: Excel 2007 or later & Excel for iPad/iPhone

Other Versions

Template Details

License: Personal Use
(not for distribution or resale)
Support
Visit Support Page
Advertisement

Description

- Use this free mortgage payment calculator to determine the monthly payment for different mortgages.

- See how soon you could pay off your home by making extra payments.

- Estimate the equity in your home after a specified number of years.

"No installation, no macros - just a simple spreadsheet" - by


How to Use the Mortgage Payment Calculator

Below are explanations of 4 ways to use this template as well as short descriptions describing the terms used in the mortgage payment calculator.

1. Compare Mortgage Payments

This simple mortgage calculator was designed for making side-by-side comparisons of different monthly mortgage payments, not including closing costs, mortgage insurance, or property taxes.

Compare the monthly payment for different terms, rates, and loan amounts to figure out what you might be able to afford. You should also consider how much interest you'll end up paying in the long run. Generally, for longer terms, your payment may be lower, but the total interest may be a lot more. You also might want to try the Home Expense Calculator to take into account some of the other expenses associated with home ownership.

The calculator is set up to compare 5 different mortgages, but you can easily expand this by inserting more columns. Just copy and paste one of the existing columns.

2. Pay off Your Mortgage Early

Making extra payments on the principal or "prepayments" can help you pay off your mortgage early and save money by not paying as much interest. This calculator lets you evaluate the effect of making an extra payment each month. The home mortgage calculator lets you add annual payments as well as occasional lump sums.

When considering the interest savings, the simple way to view making extra payments is that it is very similar to investing the money in a interest bearing account with the same interest rate as your mortgage rate (not including the effect of taxes). The main difference is that your money may not be as liquid because you'd need to sell your house or use your equity as collateral to make use of the extra money you've put into your house.

3. Estimate Accelerated Biweekly Mortgage Payments

An "Accelerated Biweekly Payment" plan usually refers to a strategy for paying off your mortgage early and paying less interest overall. Normally, your payments would be set up automatically to be made every two weeks. The payment would be equal to one half of a normal monthly payment. You'd end up making 26 payments instead of 24 within a year, resulting in prepayments on the principal equivalent to one monthly payment.

So, to estimate the effect of making accelerated biweekly payments, you just include an amount in the Extra Monthly Payment field equal to the Monthly Payment divided by 12. You can use a formula for this by entering "=C13/12" in cell C14.

4. Calculate Home Equity

The bottom rows of the calculator are for estimating the amount of equity in your home after a number of years. This can be useful if you are planning to sell your home in a few years. The calculation of owner's equity is simply the value of the home minus the amount still owed on the loan. The value will increase or decrease over time based on the real estate market, but for an estimate you can enter the purchase price.

Descriptions of Terms Used in the Calculator

Compound Period: The number of times per year that the quoted annual interest rate is compounded. US mortgage rates are quoted based on a monthly compound period (enter 12 for US mortgages). Canadian mortgage rates are quoted based on a semi-annual compound period (enter 2 for Canadian mortgages).

Loan Amount: This is the amount that you have borrowed. You can also enter your current balance, if you also adjust the Term of Loan to be the number of years left to pay off the mortgage.

Annual Interest Rate: This is the rate that is usually quoted by the lender. This calculator assumes a fixed annual interest rate. See the note under "Compound Period".

Term of Loan (in Years): The total number of years it will take to pay off the mortgage. Mortgages usually have 15 or 30-year terms. If you enter your current mortgage balance in the Loan Amount, then enter the number of years you have left on your mortgage. You can enter a formula to a specify the number of months. For example, to enter "10 years + 3 months", enter the following formula: =10+3/12

For Canadian mortgages, the definition of "Term" is different, so for Canadian mortgages you may want to change this label to "Amortization Period."

Monthly Interest Rate: The monthly interest rate is calculated from the annual interest rate and the compound period.

Monthly Mortgage Payment (PI): Consists of both principal (P) and interest (I). Derived from the amount borrowed, the term of the loan, and the mortgage interest rate.

Extra Monthly Payment: The extra amount you want to pay towards the principal each month (a regularly scheduled prepayment). This assumes no penalties for making prepayments.

To estimate Accelerated Bi-Weekly payments, enter an Extra Payment that is equal to the normal Monthly Mortgage Payment divided by 12. Normally, accelerated bi-weekly payments are set up such that each year the total amount of extra payments is equal to one normal monthly payment.

Number of Payments: This would normally just be 12 months times the Term of the loan, except that making extra payments can result in paying off the mortgage early. The NPER formula is used to calculate the number of payments required to pay off the mortgage, taking into account extra payments.

Total Payments: The total amount paid (both principal and interest) over the life of the loan.

Total Interest: The total amount of interest paid over the life of the loan.

BALANCE at Year N: Enter a year to determine the amount due on your mortgage and how much equity you will have in your house at that time.

Loan Balance Due: The amount of principal that you still have to pay.

Property Value: The value of real estate property changes based on the market. Therefore, you would need to estimate the future value of the home to calculate the estimated owner's equity at that time.

OWNER'S EQUITY: This tells you how much equity you may have in your home after the specified number of years based on the estimate of the property value. It is equal to the value of the home minus the amount owed.

More Mortgage Calculators

  • Home Mortgage Calculator - Our feature-rich mortgage payment calculator that includes an amortization table, flexible prepayment options, and optional adjustable rates.
  • Home Equity Loan Calculator - For second mortgages (hopefully you won't ever need this one).
Disclaimer: This calculator is only for educational purposes. The results are only estimates. Please consult a qualified professional regarding financial decisions.

Related Content

 

   Share: