For months I have been trying to figure out how to calculate a good estimate for the monthly payments necessary to own a house. The basic costs would be mortgage, taxes and insurance. It is often difficult to get a good answer on how much the total cost will be.

With some help from my mortgage broker cousin, I whipped up an Excel file that has the formula calculated. I wish I knew how to use Excel more efficiently but here it is. For my own reference.

Assumptions:

• Monthly taxes + insurance is approximately 1.5% of purchase price of a home

• 30 year fixed rate mortgage

Method:

• use the PMT() function in Excel
• current interest rate is about 6% or 7% on a 30 year fixed mortgage
• the fill-ins I use for the formula are as follows:
  • 6%/12 to indicate 6% interest payed monthly (12 months)
  • 360 total monthly payments (30 years * 12 months/yr)
  • Principal amount = Home cost * (1 – %down payment)
  • take that value and subtract (home cost * 1.5%) for taxes + insurance.

• optionally subtract $X for HOA dues if you buy a condo.