How to Estimate Monthly Mortgage Payments

by Steve Lander
If you have a spreadsheet, don't bother sharpening a pencil.

Your mortgage payment is based on three different factors -- the amount you borrow, the length of time you borrow it for, and your interest rate. If you know these factors, you can estimate how much your mortgage payments will be. You can even figure out any escrow payments for taxes, insurance or other expenses. Making your own estimate lets you figure out what you can, and can't, afford before you even sit down with a loan broker or real estate agent.

Principal and Interest Payments

Open your spreadsheet program.

Enter the interest rate in cell A1. For example, if you think you can get a 4.75 percent mortgage, enter "4.75%" in the cell.

Enter the length of the loan in years into cell A2. For a 30-year loan, enter "30" and press "Enter."

Enter the amount you plan to borrow as a negative number in cell A3. If you plan to borrow $200,000, enter "-200000" and press "Enter."

Enter your spreadsheet's payment command in cell A4. On Excel or the Google Docs spreadsheet, type in "=PMT(A1/12,A2*12,A3)," but without the comma and quotes, then press "Enter." In Apache OpenOffice, the command syntax is "=PMT(A1/12;A2*12;A3;0;0)." The number that appears is your monthly principal and interest payment.

Tax and Insurance Escrows

Enter your annual property tax in cell A5, divided by 12. For example, if the property tax is $4,000, enter "=4000/12," so that the cell reflects the $333.33 per month tax payment.

Enter your annual property insurance in cell A6, also divided by 12. For example, if your homeowners' insurance will be $2,500, enter "=2500/12." The cell will show your monthly $208.33 payment

Enter any additional monthly expenses that you will pay through your mortgage escrow account in cell A7.

Type "=a4+a5+a6+a7" in cell A8. This will show you your total payment including principal, interest, taxes and insurance.

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

  • Brand X Pictures/Brand X Pictures/Getty Images
Cite this Article A tool to create a citation to reference this article Cite this Article