Excel Tip: Estimating Mortgage Payments

With mortgage rates still hovering around historic lows, you might be wondering whether you can afford to purchase a new home or if you would benefit from refinancing your home mortgage.  Here is a quick way to use Excel to calculate what your new monthly payment would be.  Type this formula into any cell in an Excel spreadsheet to calculate the monthly payment (principal + interest) on a fully-amortizing mortgage loan.

=PMT(interest/12, term, balance)

  • interest = the annual interest rate, entered as a decimal
  • term = the loan term in months
  • balance = the loan balance, entered as negative

For example, for a 110-month loan at 6.5% with a balance of $405,000, you would enter:

=PMT(0.065/12, 110, -405000)

If you determine that your monthly savings from refinancing would be enough to justify the transaction costs, the next step is to determine if you qualify.  For most people that purchased their homes in the last decade, the biggest hurdle to refinancing is not having enough equity.  Fortunately, even people with little or no equity in their homes may still have a chance to refinance, particularly if their existing mortgage is being held by Fannie Mae or Freddie Mac.

If you would like some assistance in determining whether you might benefit from refinancing, or if you would like the name of a trustworthy mortgage professional who can help you explore your options, then your friends at Ghirardo CPA are ready to assist you.