How to Calculate Monthly Interest & Principal Payments

by Steve Lander
Spreadsheets are the easiest way to calculate payments.

Calculating your mortgage's monthly interest and principal payments is an arduous task using pencil and paper. You will have to solve a relatively complicated equation involving an exponent and fractions. For this reason, many financial professionals use either financial calculators or spreadsheets to find loan payments. Doing it this way is quick and simple and allows you to change variables on the fly to model how different loan terms might affect your payment. Since spreadsheets are freely available online, you can do it yourself at no cost.

Using a Spreadsheet

Open a blank spreadsheet in your chosen program. If it doesn't open up to a blank one, select "New" from the "File" menu.

Enter your proposed interest rate in cell A1 as a percentage. For instance, if you're considering a 5.25 percent loan, enter "5.25%" without the quotation marks and press "Enter."

Enter your loan's term in months in cell A2 and press the "Enter" key. If you'd rather enter the term in years, enter the term as follows, albeit without the quotation marks: "=12*30" and substitute the "30" for the number of years of your loan.

Enter your loan's initial balance as a negative number in cell A3 and press "Enter." For a $200,000 loan, you would enter "-200000" and press Enter, remembering to omit the quotation marks.

Enter the following command into cell A4, exactly as it appears here, other than the quotation marks and the period at the end: "=PMT(B1/12,B2,B3)." If you're using OpenOffice Calc instead of Excel or Google Spreadsheet, replace the commas with semi-colons as follows: "=PMT(B1/12;B2;B3)." Once you press "Enter," the field will display the monthly payment for the loan with the terms you entered in the three cells above. If you change any of those terms, the payment automatically adjust.

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