Click To See Today's Rates

Posted 02/13/2015

Make Your Own Mortgage Calculator Using These Popular Formulas

Mortgage payment formulas for popular spreadsheet programs

Use A Mortgage Calculator To Find Your Payment

Mortgage rates change daily and, lately, they've been dropping.

Since the start of 2014, the average 30-year fixed rate conventional mortgage rate has dropped nearly 100 basis points (1.00%) to the mid-3 percent range, and banks are quoting rates and APRs in close to 2 years.

For FHA loans and VA loans, mortgage rates and APRs are even lower.

According to mortgage-software provider Ellie Mae, FHA mortgage rates have averaged close to one-quarter percentage point below a comparable conventional loan rate; and VA mortgage rates have averaged 0.375 percentage points less.

It's an excellent time to comparison shop today's interest rates.

Millions of U.S. homeowners are "in the money" to refinance, which means that they stand to reduce their mortgage rate by 150 basis points (1.50%) or more; and have at least $50,000 and 10 years remaining on their mortgage. Millions more are not "in the money", but stand to make substantial monthly savings.

The typical refinancing homeowner is saving more than 30% monthly via a refinance, says Freddie Mac.

So, how much will you save? That depends on your current interest rate and the rate you can get from today's banks. It also depends on the length of your new mortgage -- 30 years, 15 years, 10 years, or something else.

Discover for yourself what you can save. All you need is a few formulas -- which are shown below -- and your favorite desktop spreadsheet software. Formulas are shown for Microsoft Excel, Google Docs, and Mac Numbers.

Or, you can use this online mortgage calculator.

Mortgage Calculator Formula : Principal + Interest Calculation

Mortgage payment spreadsheet formula : Principal + Interest

A standard, amortizing mortgage payment is comprised of two parts -- principal reduction and interest repayment. Amortizing loans are the most common loan type, and apply to 30-year fixed loans, 15-year fixed loans, and home equity loans.

Finding the monthly (principal + interest) payment on an amortizing loan is among the simplest of the spreadsheet mortgage formulas.

You'll need to know 3 pieces of information about your loan, and how to assign those values to the formula's variables.

The formula's variables are :

The standard mortgage payment formula also contains two extra variables, you'll notice -- Future Value and When Due. Both should be equal to 0. This tells the spreadsheet that the home will eventually be paid off to $0, and that interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell into which you're trying to solve, or (2) in the formula bar at the top of your spreadsheet.

Also, make sure to add a negative (-) in front of the formula.

Click to use the online home mortgage calculator.

Mortgage Calculator Formula : Principal Paid In A Given Month

Mortgage payment formula - Find principal paid in a given month

With an amortizing loan, the amount of principal included in your payment starts off small, then gets bigger over time. If you've ever noticed how your loan balance is barely dented after 5 years of payments, this is why. It's amortization at work.

Whether your mortgage is VA loan, conforming loan, an FHA loan or USDA, amortization schedules are decidedly bank-friendly. This is just the way that mortgages work.

Take the 30-year fixed rate mortgage, for example. With the 30-year fixed, it takes 18 years before your mortgage payment pays more principal than interest. Prior to that 18th year, your monthly payment is more than 50% interest payments.

To calculate how much principal you're paying in any given month, you'll need to know 4 pieces of information about your loan, and how to assign those values to the formula's variables.

The formula's variables are :

  • Periodic Rate : Your mortgage rate divided by 12 (Click to see today's rates (Dec 8th, 2016))
  • Period : The month for which you are solving
  • Number of Periods : Your loan term (in months)
  • Present Value : Your starting loan size

The principal payment formula also contains two extra variables -- Future Value and When Due. Both should be equal to 0. This tells the spreadsheet that the home will eventually be paid off to $0, and that interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell into which you're trying to solve, or (2) in the formula bar at the top of your spreadsheet, and make sure to add a negative (-) in front of the formula.

In the example shown, the homeowner's first mortgage payment contains $395.06 of principal paydown.

Click to use the online home mortgage calculator.

Mortgage Calculator Formula : Interest Paid In A Given Month

Mortgage Formula : Calculate Interest Paid In A Given Month

Interest charges are the "other half" of your monthly mortgage payment; the portion not covered by principal. But unlike principal payments which increase over time, interest charges fall over time.

You pay less interest toward the end of a loan than you do at its start.

To calculate how much interest you're paying in any given month, you'll need to know 4 pieces of information about your loan, and how to assign those values to the formula's variables.

The formula's variables are :

  • Periodic Rate : Your mortgage rate divided by 12 (Click to see today's rates (Dec 8th, 2016))
  • Period : The month for which you are solving
  • Number of Periods : Your loan term (in months)
  • Present Value : Your starting loan size

The interest payment formula contains the same two extra variables -- Future Value and When Due. Both should be equal to 0. This tells the spreadsheet that the home will eventually be paid off to $0, and that interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell into which you're trying to solve, or (2) in the formula bar at the top of your spreadsheet, and make sure to add a negative (-) in front of the formula.

In the example shown, the homeowner's first mortgage payment contains $1125.00 in interest charges. If we add this to the principal payment of $395.06 solved for in Formula #2, we're left with a payment of $1,520.06 -- the exact figure solved for at the top of the page.

Get A Complimentary Mortgage Rate Quote

With mortgage rates down, it's an excellent time to consider your options as a buyer or refinancing homeowner. You can calculate your savings using Microsoft Excel, Mac Numbers, or Google Docs; or you can use an online home mortgage calculator.

Get a live mortgage rate to use for your formulas. Rates are available online at no cost, with no obligation to proceed, and with no social security number required to get started.

Click to see today's rates (Dec 8th, 2016).

The information contained on The Mortgage Reports website is for informational purposes only and is not an advertisement for products offered by Full Beaker. The views and opinions expressed herein are those of the author and do not reflect the policy or position of Full Beaker, its officers, parent, or affiliates.

3 Testimonials

Judy T. Business Owner

I read The Mortgage Reports every day.

Dick B. Director of Special Lending

I read The Mortgage Reports because it delivers timely, up-to-the-minute mortgage news. Keep up the good work.

Ricardo P. Project Manager

The Mortgage Reports is awesome. The site is extremely helpful, keeps you up-to-date, and puts you ahead of the game. Add The Mortgage Reports to your reading list!

2016 Conforming, FHA, & VA Loan Limits

Mortgage loan limits for every U.S. county, as published by Fannie Mae & Freddie Mac, the Federal Housing Administration (FHA), and the Department of Veterans Affairs (VA)