Mortgage Payment Calculator for Excel (2024)

Download a Mortgage Payment Calculator for Excel® and Google Sheets | Updated 7/17/2019

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

Online Mortgage Payment Calculator

Use our new online calculator below to do some quick calculations. The online calculator requires your browser to support Javscript. If it doesn't work for you or if you want something you can customize, download the spreadsheet or use the Google Sheets version below.

Mortgage Payment Calculator

Mortgage Payment Calculator

for Excel, Google Sheets and OpenOffice

Download

⤓ Excel

For: Excel 2007 or later & Excel for iPad/iPhone

⤓ Google Sheets

License: Personal Use (not for distribution or resale)

Other Versions

OpenOffice (.ods)

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 Jon Wittwer

Update 7/17/2019 - Added "Initial Monthly Interest" and "Initial Monthly Principal" to the results to show the amount of interest vs. principal being paid on the first payment.


related blog articles
10 Strategies for Paying Off Your Mortgage Early

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.

Initial Monthly Interest: The monthly interest payment will go down each month, but for purposes of comparing the interest to the principal payment, the initial monthly interest payment (and the initial monthly principal) is shown.

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.

Mortgage Payment Calculator for Excel (2024)

FAQs

How to calculate the monthly payment on a mortgage in Excel? ›

What Is the Formula for Monthly Payments in Excel? Use the PMT function in Excel to create the formula: PMT(rate, nper, pv, [fv], [type]). 1 This formula lets you calculate monthly payments when you divide the annual interest rate by 12, for the number of months in a year.

How to easily calculate mortgage payment? ›

If your loan amount is $100,000, you would multiply $100,000 by 0.005 for a monthly payment of $500. A simpler calculation may be first multiplying the loan amount of $100,000 by the interest rate of 0.06 to get $6,000 of yearly interest, then dividing that $6,000 by 12 to get your monthly payment of $500.

How do I use PMT in Excel for mortgage payment? ›

=PMT(5%/12,30*12,180000)

the result is a monthly payment (not including insurance and taxes) of $966.28. The rate argument is 5% divided by the 12 months in a year. The NPER argument is 30*12 for a 30 year mortgage with 12 monthly payments made each year.

What formula is used to calculate the payment for a loan in Excel? ›

Example
DataDescription
FormulaDescription
=PMT(A2/12,A3,A4)Monthly payment for a loan with terms specified as arguments in A2:A4.
=PMT(A2/12,A3,A4,,1)Monthly payment for a loan with with terms specified as arguments in A2:A4, except payments are due at the beginning of the period.
DataDescription
8 more rows

Which function calculates your monthly mortgage payment in Excel? ›

The PMT function in Excel

Each argument has the following meaning: rate: The interest rate for the loan. nper: The total number of payment periods for the loan. pv: The principal amount of the loan.

What is the formula for the monthly payment? ›

Monthly Payment = (P × r) ∕ n

Again, “P” represents your principal amount, and “r” is your APR. However, “n” in this equation is the number of payments you'll make over a year. Now for an example. Let's say you get an interest-only personal loan for $10,000 with an APR of 3.5% and a 60-month repayment term.

What is the PMT formula? ›

The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount. "PMT" stands for "payment", hence the function's name.

What is the formula for mortgage principal payment in Excel? ›

Examples
DataArgument description
FormulaDescription
=PPMT(A2/12, 1, A3*12, A4)Principal payment for month 1 of the loan
DataArgument description
8%Annual interest rate
7 more rows

What is the formula for calculating loan amount? ›

all you need are the details like the amount borrowed, interest rate, and loan tenure to calculate your monthly EMI. the formula for calculation is: EMI = [p x r x (1+r)^n]/[(1+r)^n-1]

How to calculate monthly loan payments? ›

The formula is: M = P [ i(1 + i)^n ] / [ (1 + i)^n – 1], where M is the monthly payment, P is the loan amount, i is the interest rate (divided by 12) and n is the number of monthly payments.

How to calculate interest only mortgage payments in Excel? ›

The formula to be used will be =IPMT( 5%/12, 1, 60, 50000). In the example above: As the payments are made monthly, it was necessary to convert the annual interest rate of 5% into a monthly rate (=5%/12), and the number of periods from years to months (=5*12).

What is the formula for PMT and IPMT in Excel? ›

“=PMT” → The “PMT” function in Excel calculates the periodic payment on a loan, inclusive of both the interest and principal. “=IPMT” → In contrast, the “IPMT” in Excel calculates only the interest paid on a loan, as suggested by the “I” in front that stands for “interest.”

How to calculate monthly installments of a loan? ›

For example, If a person avails a loan of ₹10,00,000 at an annual interest rate of 7.2% for a tenure of 120 months (10 years), then his EMI will be calculated as under: EMI= ₹10,00,000 * 0.006 * (1 + 0.006)120 / ((1 + 0.006)120 - 1) = ₹11,714.

What is the formula for calculating monthly EMI in Excel? ›

Formula to calculate EMIs using MS Excel

Calculating EMI is easy with the following formula using MS Excel: EMI = (P X R/12) X [(1+R/12) ^N] / [(1+R/12) ^N-1]. Here, P is the original loan amount.

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Virgilio Hermann JD

Last Updated:

Views: 6046

Rating: 4 / 5 (41 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Virgilio Hermann JD

Birthday: 1997-12-21

Address: 6946 Schoen Cove, Sipesshire, MO 55944

Phone: +3763365785260

Job: Accounting Engineer

Hobby: Web surfing, Rafting, Dowsing, Stand-up comedy, Ghost hunting, Swimming, Amateur radio

Introduction: My name is Virgilio Hermann JD, I am a fine, gifted, beautiful, encouraging, kind, talented, zealous person who loves writing and wants to share my knowledge and understanding with you.