How to Calculate Monthly Interest & Principal Payments

How to Calculate Monthly Interest & Principal Payments
••• Stockbyte/Stockbyte/Getty Images

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.


  • If you would prefer to do the calculation by hand, use the following equation: Payment = (((rate/12)/(((1+rate/12)^term)-1))+(rate/12))_principal In the equation, you would reflect a 5.25 percent rate as 0.0525, and you would use the mortgage's "term" in months. For the example in this article, the equation would appear as follows: $1104.41 = (((.0525/12)/(((1+.0525/12)^360)-1))+(.0525/12))_200000


  • The instructions in this article apply to the versions of Microsoft Excel, Google Spreadsheets and Apache OpenOffice available as of the time of publication. They may or may not apply to other spreadsheet programs or to other versions of these programs.