Compare Today's Home Equity Rates

# Mortgage Calculator Excel

Mortgage Calculator Excel spreadsheet is an advanced PITI mortgage calculator with PMI, taxes, and insurance, monthly and bi-weekly payments, and multiple extra payments options to calculate your mortgage payments. The mortgage calculator spreadsheet has a mortgage amortization schedule that is printable and exportable to excel and pdf.

Home Value
\$
Down Payment
Mortgage Amount
\$
Loan Terms
Interest Rate
PMI (Yearly)
Property Tax (Yearly)
Home Insurance (Yearly)
HOA Fees (Monthly)
Payment Frequency
First Payment Date

Amortization Schedule
Extra Payments
One Time
 \$ On Date
Monthly or Biweekly
 \$ Starting Date
Quarterly
 \$ Starting Date
Yearly
 \$ Starting Date
Check Local Mortgage Rates

## How Does a Mortgage Work?

A mortgage is a loan between a lender and borrower to help the borrower finance the purchase of a house. In return, the borrower agrees to pay back the loan plus interest over the course of 15 or 30 years. The main parts of a mortgage are made up by

• Loan Amount - this is the amount of money that the borrower asks for
• Interest Rate - the borrower must pay back the loan with interest, and the interest rate determines how much the borrower should pay in interest payment.
• Loan Terms - the borrower is given a certain amount of years to pay off the mortgage. 15 and 30-year terms are the most common types of mortgages.

On a 30-year fixed-rate mortgage, the payment is divided up by monthly payments where a borrower will pay the same amount each month for 30 years. In the beginning, most of your monthly payment goes towards paying interest instead of the principal. As time passes, the ratio between interest and principal payment will reverse. With our amortization schedule, you can easily view each payment to learn exactly how much of your monthly payment is for interest.

## How to Calculate Monthly Mortgage Payments?

The monthly payment formula on how to calculate mortgage payments is given below. A = (P x i/100/12) / (1 - 1 / (1 + i/100/12)^ n)), where A = monthly payment P = principal amount i = interest rate n = the loan term in months

## Monthly Mortgage Payment Example

Let's calculate the monthly payment on a \$500,000 30-year fixed mortgage with a 5% interest rate. A = (P *i/100/12) / (1 - 1 / (1 + i/100/12)^ n)) A = 500,000 *5/100/12) / (1 - 1 / (1 + 5/100/12)^ 360)) A = 500,000 x 0.004167/(1 - 1/(1 + 0.004167) ^ 360) A = 2,083.5/(1 - 1/1.004167^360) A = 2,083.5/(1- 1/4.46828) A = 2,083.5/(1 - 0.2238) A = 2,083.5/(0.7762) A = 2,684.11 Therefore, the monthly mortgage payment for a 30-year \$500,000 mortgage with a 5% interest rate is \$2,684.11. There are other costs of owning a house other than the monthly mortgage, such as the tax and insurance, and possibly private mortgage insurance if the down payment is less than 20%. We will go over each of the items below.

## Typical Costs of a Mortgage Payment

A mortgage payment can get complicated when you factor in other costs, such as PMI, taxes and insurance. Taxes and insurance are the two other parts in addition to principal and interest that made up your monthly mortgage payment. Principal, interest, taxes and insurance, or PITI are the core components of a mortgage payment since no matter where you live, you have to pay taxes and insurance on your house. Taxes and insurance vary state by state, and city by city. With the comprehensive mortgage calculator PITI, you can quickly calculate these costs and see how much you need to pay each month. Principal - The amount you borrow. Each payment reduces your principal. Interest - The interest that you pay to the bank for lending you money. Property Tax - A tax that you need to pay the government for your house and land. Insurance - Homeowners are required to buy home insurance to cover their losses in the case of fire, storms, flood, and other damages. PMI - private mortgage insurance is mortgage insurance that homeowners have to pay if their down payment is less than 20%. They have to pay the PMI until their equity in the house is over 20%. Our PITI mortgage calculator has a built-in function to include taxes and insurance, so you can quickly include them in your calculation.

## What is Private Mortgage Insurance?

Private mortgage insurance or PMI is insurance that a borrower needs to pay if his down payment on the house is less than 20%. The PMI is a protection for the banks and lenders to protect themselves in case the borrower defaults or stops making payments. The banks want the borrower to have more equity in the house to reduce their risks of making a loan. The more down payment a borrower puts down on a house, the less likely that he is to walk away from his mortgage payments. For borrowers who put down less than 20%, PMI is an extra cost for the mortgage. Once the borrower's equity on the house is over 20%, then he can contact the lenders and remove the PMI charges. Our mortgage calculator with PMI has a field called "PMI Yearly" that allows you to enter a PMI by percentage or dollar money.

## How to Pay Off Mortgage Earlier?

There are a few ways a borrower can pay off his mortgage earlier and faster, biweekly and extra payments.

## What Are Bi-Weekly Payments?

By default, most mortgage payments are paid on a monthly basis. However, some people may want to choose bi-weekly payment. A bi-weekly payment is not the same as paying twice a month. When making monthly payments, a borrower pays 12 times a year. There are 52 weeks in a year, making bi-weekly payments means a borrower makes 26 payments a year. Since the bi-weekly payment is half of the monthly payment amount (not including tax & insurance & other fees), a borrower who chooses a bi-weekly payment is essentially making 13 payments a year compared to the regular monthly payments. The main reason why anyone would want to make bi-weekly payments is that it allows a borrower to save a lot of money on interest, and pay off the mortgage many years earlier than the monthly payment. One extra payment a year may not seem like a lot. However, each extra payment towards principal reduces both your mortgage and the interest you are paying. For example, a borrower pays about \$373,023.14 in interest payments on a mortgage of \$400,000 with a 30-year term and a fixed interest rate of 5%. The amount of interest that a borrower pays at the end of the term is almost the size of the mortgage amount. However, if the borrower chooses to make a bi-weekly payment, he or she will pay about \$304,367.27 in interest payment, which is a saving of \$68,655.87. On top of that, the borrower is able to pay off his mortgage 5 years earlier. If we include other costs such as PMI, the savings may be even bigger with the biweekly mortgage payments. Our mortgage calculator allows you to choose payment frequency (monthly & bi-weekly) and get an amortization schedule with a breakdown of each payment in detail. You will also get a comparison table that shows you exactly how much you will save in interest payments, and how many years earlier you will pay off your mortgage.

## What Are Extra Payments?

There are other ways that a borrower chooses to pay off his or her mortgage earlier and save money on interest payments. One such option is to make extra payments toward the principal. A borrower can make a one-time lump sum payment or increase his monthly payment. By increasing the monthly payment, you pay off your mortgage faster and cut down on interest payments.

## Is Bi-weekly Payment or Extra Payment Right For You?

There are situations where a borrower does not want to make bi-weekly payments or extra payments.

• Are you planning to move out of the house after a couple of years? If you are, making an extra payment or bi-weekly payment may not be worth the effort. Most of the savings on interest payments are saved after you live in the house for a decade or more.

• Can you use the extra money for other better investments? If you have better use for your money such as stocks, bonds, real estate, business, or other opportunities.

• Can you afford it? If your salary doesn't increase and you are not able to cut your spending, then you may not afford to make extra payments.

## PITI Mortgage Calculator

Our advanced mortgage payment calculator with PMI, multiple extra payments, tax, and insurance, has all the variables built in so that you can estimate exactly how much you will be paying each month. Mortgage Calculator With PMI is a mortgage amortization calculator that has an option to include Private Mortgage Insurance or PMI. The PMI is calculated only if the down payment is less than 20% of the property value, and the borrower will have to pay for the mortgage insurance until the balance is less than or equal to 80% of the home value. One can enter the PMI as a dollar amount or as a percentage of the home value. The mortgage calculator with taxes and insurance allows a borrower to include property taxes and homeowner's insurance so that one can get a complete breakdown of the amortization schedule and see how much one has to pay monthly or biweekly. Every little thing adds up such as taxes and fees, a borrower should make sure he or she is able to pay the monthly payments before making the down payments for a house. The mortgage affordability calculator will help you estimate how much house can I afford using your annual income and monthly debt. The mortgage calculator with extra payments gives borrowers four ways to include extra payments for their payments in case they want to pay off their mortgage earlier. The extra payment options are a one-time extra payment, recurring biweekly, monthly, quarterly, and yearly plan. All you have to do is to toggle the yes button next to the extra payment option and enter the amount, and a starting date accordingly.

## Amortization Schedule

The mortgage calculator with PMI comes with a downloadable and printable mortgage amortization schedule that you can save and print as a pdf file. The mortgage amortization schedule has all the details of each monthly mortgage payment such as monthly fees, principal, interest, taxes and insurance, and the total monthly or biweekly payment. You will see the payoff date, the total interest, principal as well as the total payments made after the mortgage is paid off.

You will also get a mortgage comparison table that compares the monthly and biweekly payment options. A borrower will be able to see the total interest, extra payment, fees, payoff date for each payment frequency as well as the amount of savings if one chooses the bi-weekly payment option.

## Mortgage Calculator App

The all-in-one mortgage calculator is available for download on the Play Store. If you have an Android device, you can install the free mortgage calculator app in your pocket. The advanced mortgage calculator app offers the same functionalities as the online mortgage calculator which includes options for extra payments, PMI, taxes and insurance. There is also a simple version that requires only the mortgage amount, loan terms, and interest rate which can be used as a loan calculator for any type of loan. Just like our online mortgage calculator, you can print, save and export the results to pdf. The app is one of the best mortgage calculator apps for Android with a rating of 4.6.