How to Calculate Mortagage Payments

by Christopher Raines
Computer spreadsheets can aid your search for lower mortgage payments.

Your monthly mortgage payment significantly impacts your budget. A lower monthly payment means you can spend more to pay down other debt, maintain your car and home, buy food and clothes and meet emergencies and unexpected needs. The ability to calculate your mortgage helps you decide the best possible loan terms for minimizing your mortgage payment and whether to refinance an existing loan.

Using a Spreadsheet

Create cells for the loan amount, annual interest rate and loan term in years. (Ref. 1.) By way of illustration, the loan principal can occupy A2; put the annual interest rate B2 and the number of years of the loan in C2.

Enter the payment function in a cell. Start with "=PMT." (Ref. 1, 2.) The formula is formatted as "=PMT(Rate, Nper, PV, FV, type)." "Rate" represents the monthly interest rate, "Nper" is for number of periods, "PV" stands for present value and "FV" is the future value. Disregard the future value and type arguments.

Divide the annual rate by 12 months for the "Rate" component. For example, if you assigned the annual rate to cell "B2," type the argument as B2/12.

Set up the argument for Nper, or number of periods, as the cell for the number of years multiplied by 12. For example, if C2 is the loan term, depict the number of periods as C2*12. The spreadsheet will treat a 30-year loan as 360 months, a 15-year loan as 180 months and a 10-year loan as 120 months.

Use the cell for the loan amount, such as A2, as the present value. With the example cells in step 1, the formula in its final form is "=PMT(B2/12,C2*12,-A2)"; the loan amount, or present value, is negative so that your spreadsheet will present the payment as a positive result.

Using Payment Table

Go to "How to Buy a Home With a Low Down Payment" from the Federal Citizen Information Center" ( Scroll down to the "Payment Table."

Get the monthly payment for each $1,000 of your loan. Find the payment for your interest rate and length of your loan -- 15 years, 20 years or 30 years. For example, on a 30-year loan with an eight percent annual interest rate, your payment per $1,000 is $7.34.

Divide your loan principal by 1,000 and multiply the result by the monthly payment per $1,000 from the payment table. For a $75,000 loan, multiply 75 (75,000/1,000) by 7.34 to arrive at a monthly mortgage payment of $550.50.

About the Author

Christopher Raines enjoys sharing his knowledge of business, financial matters and the law. He earned his business administration and law degrees from the University of North Carolina at Chapel Hill. As a lawyer since August 1996, Raines has handled cases involving business, consumer and other areas of the law.

Photo Credits

  • Stockbyte/Stockbyte/Getty Images