Create your own mortgage calculator using these popular formulas


Use a mortgage calculator to find your payment

Mortgage rates change daily and recently they have gone down.

Since the start of 2014, the 30-year average fixed-rate conventional mortgage rate has fallen almost 100 basis points (1.00%) to the mid-range of 3%, and banks cite rates and APRs over nearly 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 are on average almost a quarter of a percentage point below a comparable conventional loan rate; and VA mortgage rates are on average 0.375 percentage points lower.

Now is a great time to compare interest rates today.

Millions of US homeowners are “in the money” to refinance, which means they’re willing to cut their mortgage rates 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 saves over 30% per month through refinancing, explains Freddie Mac.

So how much will be you to save? It depends on your current interest rate and the rate you can get from banks today. It also depends on the length of your new mortgage – 30 years, 15 years, 10 years, or whatever.

Find out for yourself what you can save. All you need are a few formulas – which are shown below – and your favorite desktop spreadsheet. The formulas are displayed for Microsoft Excel, Google Docs, and Mac Numbers.

Or, you can use this online mortgage calculator.

Mortgage calculator formula: Calculation of principal + interest

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

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

You will need to know 3 pieces of information about your loan and how to assign these values ​​to the variables in the formula.

The variables of the formula are:

The standard mortgage payment formula also contains two additional variables, you will notice – Future value and When due. The two should equal 0. This tells the spreadsheet that the house will ultimately be paid off at $ 0 and the interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell you are trying to solve, or (2) in the formula bar at the top of your worksheet.

Also make sure to add a negative () before the formula.

Click to use the online mortgage calculator.

Mortgage calculation formula: principal paid in a given month

Mortgage payment formula - Find the principal paid in a given month

With an amortizing loan, the amount of principal included in your payment starts out small and then increases over time. If you’ve ever noticed how barely dented your loan balance is after 5 years of payments, here’s why. It is depreciation at work.

Whether your mortgage is a VA loan, a compliant loan, an FHA or USDA loan, amortization schedules are decidedly bank friendly. This is how mortgages work.

Take the example of the 30-year fixed rate mortgage. With the 30 years fixed, it is necessary 18 years old before your mortgage payment pays more principal than interest. Prior to this 18th year, your monthly payment is over 50% interest payments.

To calculate the amount of principal you pay in a given month, you need to know 4 pieces of information about your loan and know how to assign these values ​​to the variables in the formula.

The variables of the formula are:

  • Periodic rate : Your mortgage rate divided by 12 (Get today’s rates here (December 11, 2021))
  • Period : The month for which you solve
  • Number of periods : Duration of your loan (in months)
  • Current value : Your starting loan amount

The principal payment formula also contains two additional variables – Future value and When due. The two should equal 0. This tells the spreadsheet that the house will ultimately be paid off at $ 0 and the interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell you are trying to solve, or (2) in the formula bar at the top of your worksheet, and make sure to add a negative () before the formula.

In the example shown, the owner’s first mortgage payment contains $ 395.06 of principal repayment.

Click to use the online mortgage calculator.

Mortgage calculation formula: Interest paid in a given month

Mortgage formula: calculate the interest paid in a given month

Interest charges are the “other half” of your monthly mortgage payment; the part not covered by the principal. But unlike principal payments which increase over time, interest charges decrease over time.

You pay less interest near the end of a loan than at the beginning.

To calculate the amount of interest you pay in a given month, you need to know 4 pieces of information about your loan and know how to assign these values ​​to the variables in the formula.

The variables of the formula are:

  • Periodic rate : Your mortgage rate divided by 12 (Get today’s rates here (December 11, 2021))
  • Period : The month for which you solve
  • Number of periods : Duration of your loan (in months)
  • Current value : Your starting loan amount

The interest payment formula contains the same two additional variables – Future value and When due. The two should equal 0. This tells the spreadsheet that the house will ultimately be paid off at $ 0 and the interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell you are trying to solve, or (2) in the formula bar at the top of your worksheet, and make sure to add a negative () before the formula.

In the example shown, the owner’s first mortgage payment contains $ 1,125.00 in interest charges. If we add that to the principal payment of $ 395.06 resolved in Formula # 2, we end up with a payment of $ 1,520.06 – the exact figure resolved at the top of the page.

Get a free mortgage rate quote

With mortgage rates falling, now is a great time to consider your options as a buyer or refinance owner. You can calculate your savings using Microsoft Excel, Mac Numbers, or Google Docs; or you can use an online mortgage calculator.

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

Click for today’s live mortgage rates (December 11, 2021).


Comments are closed.