How to Calculate Mortgage Payments

One of the big questions to ask when buying a house is how much will my mortgage payments be? When looking to refinance or make a purchase of a new property, it is important to be able to calculate mortgage payments based on the current rate environment. When refinancing a home, both the current payment and the proposed payment need to be determined so that an accurate comparison can be made. When purchasing a new home, calculating a payment is important to determine whether the purchaser can afford the payment. Calculating mortgage payments is a simple process with a basic spreadsheet program, but more difficult without one.

The Variables of a Mortgage Payment Calculation

Three variables required to calculate a mortgage payment are the interest rate, loan amount, and amortization term. The first two variables are obvious, but the loan amortization term can be a bit more confusing.

The loan amortization term refers to the period of time required to pay the loan in full. The typical 30-year mortgage has a 30-year term, but also a 30-year amortization term. At the end of 30 years, the mortgage is paid in full based on the payment schedule. It is possible for the term of the mortgage to be shorter than the amortization term. A balloon mortgage occurs when a payment larger than the normal scheduled payment is required to pay the loan in full at maturity.

Calculating a Mortgage Payment with a Spreadsheet Program

All modern spreadsheet programs contain a payment function. Microsoft Works requires the principal amount, the rate, and the term. Microsoft Excel requires the rate, the number of periods, and the present value. These are all the same variables that are being required. It is important to note that the rate will need to be adjusted based on the period of time being measured. For example, on a 30-year mortgage where payments are made monthly, you will have to divide the interest rate by 12 to determine the monthly rate. A similar adjustment needs to be made to the term or number of periods. On a 30-year mortgage, payments are made monthly, so the number of periods is equal to 360.

Assuming a 30-year, fully-amortizing mortgage, a 6.00% rate, and a $200,000 loan amount, the monthly payment would be $1,199.10. The 6.00% rate needs to be divided by 12 in the function, resulting in a monthly rate of 0.50%. The number of periods or term is 360 months.

Calculating a Mortgage Payment with a Basic Calculator

Calculating a mortgage payment without a spreadsheet or financial calculator requires a bit more math. The formula is as follows:

Payment = Principal x [rate x (1 + rate) ^n / (1 + rate) ^n -1]

The “^” indicates an exponent, and “n” represents the number of periods or amortization term. Starting with the fraction within the brackets, a 6.00% annual rate needs to be converted to a monthly rate, which would be 0.50%. The numerator is determined by multiplying 0.50% times 1 plus the 0.50% rate to the power of 360, which is 0.0301. For the denominator, 1 plus the 0.50% rate raised to the power of 360 minus the whole number 1 is 5.0226. Dividing 0.0301 by 5.0226 is 0.005996. Multiplying this result times the principal amount of $200,000 from the example above equals $1,199.10. This is the same result achieved in the spreadsheet example above.

A basic spreadsheet program is the easier route to take. To perform the calculations using the formula above requires a calculator that can handle exponents.

Other Considerations

Many mortgage lenders also require that taxes and insurance be escrowed. This does not affect the monthly principal and interest payment, but it would affect the total monthly payment to the mortgage company. Many times, a mortgage company will require 13 months of tax and insurance payments to provide a buffer against rising taxes and insurance costs. This would be calculated by adding the annual tax payment required plus the annual property insurance payment, dividing by 12 and then multiplying by 13. A mortgage representative should be able to indicate what amounts will be required on an ongoing basis.