How to Figure Mortgage Interest Rates & Payments

by Steve Lander
Spreadsheets quickly solve for mortgage rates or payments.

The basic business points of most mortgages can be summed up in four numbers. The mortgage's interest rate determines how much you pay for the money you're borrowing, the term specifies how long the loan lasts, the present value is the amount of money that you're borrowing, and the payment is what you have to pay every month. If you know three of those variables, you can solve for the fourth. You can do it on a financial calculator, but any spreadsheet can also do the mortgage math for you. Solving these statistics by hand requires elementary calculus, so, for most people, a spreadsheet is the best way to go.

Open a blank spreadsheet in your chosen spreadsheet program.

Enter the interest rate for your mortgage in cell A1 as a percentage. For instance, if you're looking at a loan with a 5.25-percent interest rate, enter "5.25%" without the quotation marks. If you want to solve for the interest rate, leave this cell blank.

Enter the loan's term in months in cell A2, right below the interest rate or the blank cell. For a 30-year loan, enter "360" without the quotation marks. If you don't know the term in months, you can enter "=X*12" without the quotation marks, with X as the number of years on your mortgage.

Enter the mortgage's monthly payment in cell A3. For instance, if you have a 30-year $195,000 mortgage and know that the payment is $1,076.80, enter "1076.80" without the quotation marks. If you don't know the payment amount, leave this cell blank.

Enter the loan amount as a negative number in cell A5. If you're taking out a $195,000 mortgage, enter "-195000" without the quotation marks.

Solve for the payment by entering the following formula, without the quotation marks, in cell A6 "=PMT(A1/12,A2,A4)" into Microsoft Excel or the Google Docs spreadsheet. If you're using OpenOffice, enter "=PMT(A1/12;A2;A4)" without the quotation marks.

Enter this command, without the quotation marks or period, into cell A6 on Excel or Google Docs to find the yearly interest rate: "=RATE(A2,A3,A4)*12." The format is the same for OpenOffice, but with semicolons instead of commas -- "=RATE(A2;A3;A4)*12." (ref 5)

About the Author

Steve Lander has been a writer since 1996, with experience in the fields of financial services, real estate and technology. His work has appeared in trade publications such as the "Minnesota Real Estate Journal" and "Minnesota Multi-Housing Association Advocate." Lander holds a Bachelor of Arts in political science from Columbia University.

Photo Credits

  • Stockbyte/Stockbyte/Getty Images