Q & A: Figuring the financing for a first home

Q. We are starting to look for a first home, and I can't quite get the loan and credit formula to work out. I use Excel quite a bit in my job and am not sure what I am missing. Can you please take a look?

A. You know, I haven't built a mortgage calculation into a spreadsheet in a couple of years, and I had the darndest time getting the answer.

Excel uses the function PMT (rate, nper, pv) to calculate mortgage payments, where rate is interest, nper is the term, or length of the loan, and pv is the amount of the loan. A wizard lays this all out pretty well when you go to the header and select Insert/Function/Financial/PMT.

Here's the trick: You can't mix apples and oranges, or in this case, annualized interest with monthly payments. Convert yearly interest to monthly interest by dividing the interest rate by 12. This still isn't quite the way the banks do it, but it's close enough for estimating. And always double check your results with an online calculator.