our everyday life

How to Calculate Housing Payments

by Steve Lander

To calculate your housing payments, you'll need to know what your house, its mortgage, its taxes and its property insurance will cost. You can estimate these values, or get quotes and data from the appropriate parties. Once you know those, you can put them together and calculate your monthly principal, interest, tax and insurance payment. Sometimes referred to as a PITI payment, this is your main cost of owning a home.

Find a house that you like and settle on a purchase price.

Have your lender or mortgage broker calculate the interest rate and closing costs that you will pay for your loan.

Add your closing costs to your property's purchase price, then subtract your down payment, to find your loan. For instance, if you plan to buy a $165,000 and put $12,000 down but have $4,000 in closing costs, you'd have a total cost of $169,000 and a loan of $157,000. Your closing costs are an extra expense, so you'll need to have additional money. For example, if you're taking out a loan with a 3 percent down payment and your closing costs are an additional 2 percent, you'll actually need to have 5 percent of the purchase price.

Open a spreadsheet with a blank document. Enter your loan's interest rate as a percentage in cell A1, its length in months in cell A2 and the balance as a negative number in cell A3. For instance, if you were going to take out a $157,000 30-year mortgage at 4.75 percent, you would enter "4.75%" in cell A1, "360" in cell A2," and "-157000" in cell A3. In all three cases, leave out the quotation marks.

Enter the following command in cell A4, without the quotation marks and closing period: "=PMT(A1/12,A2,A3)." This format will work in Excel and Google Spreadsheets, and will also work in OpenOffice if you replace the commas with semicolons. Once you enter the command, the cell will display the monthly payment. In the case of the $157,000 30-year loan at 4.75 percent, the payment is $818.99.

Find your monthly mortgage insurance payment by entering "=a3*.0135/-12" in cell A5. It will come up with $176.63 or so. If your mortgage insurance's rate isn't 1.35 percent per year, you can enter a different rate as a decimal. For instance, for a 0.75 percent premium, you would enter "=a3*.0075/-12" but leave out the quotation marks.

Enter "=2000/12" in cell A6, replacing the 2000 with your annual property insurance premium. If you don't want to call an insurance agent to find a premium or get an online quote, your state's department of insurance may post the results of their comparative shopping surveys which you can use to estimate your insurance. For the most accurate estimate, though, call an agent or insurer.

Enter "=3000/12" in cell A7, replacing the 3000 with your annual property taxes. If the price you will pay is going to be the same as the property's current assessed value, which you can get from the real estate agent, you might be able to use the existing taxes. If not, your real estate agent or the county assessor can help you estimate what your new taxes will be.

Enter "=a4+a5+a6+a7" in cell A8. This will add up your principal and interest payment, your private mortgage insurance payment, your property insurance payment and your property tax payment. If your loan has a $818.99 PI payment, $176.63 private mortgage insurance payment, $166.67 homewowners insurance payment and $250 tax payment, it adds up to $1412.28 after rounding.

Tip

  • Your lender is allowed to collect a reserve equal to two months' insurance and property tax payments, so the tax and insurance part of your payment could be a little bit higher than the estimate here.

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

  • Jupiterimages/Comstock/Getty Images