In this three-part series, you’ll first learn the basics of calculating a mortgage payment. In part two, he’ll see how he can use his spreadsheet to compare options and make plans as he buys a home. In the final part, you’ll see how to evaluate several mortgage offers side by side to choose the one that’s right for you.

The fundamentals (principle and interest):

Excel’s PMT function calculates the bulk of your mortgage payment, principal, and interest payment on your loan. If you are not familiar with using functions in Excel, you need to enter:

=PMT(Rate, Nper, PV, FV, Type)

Where:

Rate is your interest rate. To calculate your monthly payments you need to enter this as your interest rate divided by 12, like this: =0.0375/12.

Nper is the number of payments. To be consistent, you should enter this as the monthly payment number or 360 if you are planning a 30-year fixed-rate loan.

PV is the amount you plan to finance. It is simply the purchase price minus the down payment. From your loan, make this a negative number.

FV is the future value, which is equal to zero.

Type specifies whether the payment is at the beginning or end of each period. If you don’t enter anything, the PAYMENT function defaults to the end of each period, which is fine.

If you are trying to figure out what the payment will be on a $250,000 house with a $25,000 down payment using a 3.75% 30-year fixed loan, your Excel input would be:

= PAYMENT(0.0375/12,360,-200000)

Press enter and you will see that your monthly principal and interest payment will be $1,042.01.

All other costs

While principal and interest are the most important parts of your monthly payment, there’s more. Now you need to add everything else, including mortgage insurance, hazard insurance, and property taxes.

Mortgage Insurance: If you’re putting down less than 20% down (as in the example), you’ll need to add this to your costs. Because FHA mortgage insurance has an initial and annual component, you’ll need to add it in two steps.

Step 1: Multiply your financing amount by 0.0175 (1.75%), the current initial FHA mortgage insurance premium for all loans that need mortgage insurance. Add that amount to your financing amount and redo your principal and interest calculation. Using the example above, the calculations would look like this:

= $225,000*0.0175=$3,937.5; $225,000+$3,937.5= $228,937.5; =PAYMENT(0.0375/12,360,-228937.5)

That gives you a new principal and interest payment of $1,060.25 per month.

Step 2: Multiply the amount financed (including UFMIP) by the annual FHA mortgage insurance premium for you and divide by 12 to get the monthly cost. Your annual MIP will range from 0.45% to 1.55% depending on the amount financed, the term of your loan, and the value of your loan. You can check the FHA website to see what rate applies to your specific case. In this example, the annual MIP would be 1.35% of the financed amount.

= $228,937.5 * 0.0135 = $3,090.66/year or $257.55/month

Hazard Insurance: In 2012, the national average cost of homeowners insurance was $897.91 per year, or $74.83 per month, according to homeinsurance.com. You can use this figure until you can get a quote from an insurer. The cost of homeowners policies varies considerably depending on the location, type and size of the home, and your coverage options.

Taxes: You can find property tax estimates for the homes you’re considering on most major real estate sites like Zillow, Redfin, or Trulia. Or if you want the most exact number, current tax information is available online at most county appraisal offices. Just be sure to use a monthly figure. To finish the example, assume taxes of $2,400 per year or $200 per month.

putting it all together

Now all you have to do is put it all together. You should design your excel sheet to have P&I + MMI + HI + Tax with all figures calculated monthly. In the example, that would be:

$1,060.25 + $257.55 + $74.83 + $200 = $1,592.63 / month

gaining knowledge

The real power of doing these calculations in Excel is that you can run through all the scenarios and possibilities quickly and have the results side by side for comparison. While that’s the focus of the next two parts of this series, you may have already seen how this helps. If you found that mortgage insurance added $276 per month, or about 20% of the total payment, you probably did.