This site is used as a homework reference in:
Stoughton High School (Pat Schneider's economics class)



Figuring the monthly payment on a mortgageIf you came to this page first, you should use the calculator for how much home you can afford before you try to figure your monthly payment here.
Your monthly payment includes more than just the repayment on the loan! It also includes property taxes and insurance, and if your down payment was less than 20%, then it also includes private mortgage insurance. Many mortgage calculators don't include these amounts, which makes them kind of useless. My calculator (at right) gives you a more realistic picture of your real total monthly obligation. For the down payment,
enter the largest that you're able to afford. To show how much the interest rate and the down payment affect the monthly payment, here are some examples of monthly payments on a $180,000 home with a 30year mortgage:
Taxes, Insurance, and Maintenance

Insurance Deductible vs. Premium  
Deductible  Premium 
1% ($1333)  $1333 
3% ($4890)  $961 
5% ($8150)  $784 
While maintenance is a very real expense, it's not included in your monthly payment, so you'll need to prepared to pay for maintenance separately. Longterm maintenance often runs around 1% of the home value per year, so on a $175,000 home, figure $1750 per year (going up each year with inflation). If that sounds like a lot, consider that every 15 years or so you'll need to replace your roof, which will cost several thousand dollars. (You could get a metal roof which will last your lifetime, like I did, but metal roofs cost more up front.) And every several years you'll have to have a wood home repainted, which will run a few thousand dollars. Central heat/air systems don't last forever, either.
If you have decent savings, you don't have to budget for maintenance. But if buying the home is going to be a stretch for you and you're not good at saving, then try to put some money away each month in a separate account so you'll be able to pay for maintenance as needed. A good amount is the value of your home divided by 1200. (e.g., on a $180,000 home, that would be $150/mo.)
The rest of this page shows you how to calculate the mortgage payment manually with spreadsheet software (Excel, Numbers, etc.). If this doesn't interest you, feel free to skip to the Down Payments page.
Here's how to use a spreadsheet program to figure out the payments on a loan. Open up your trusty spreadsheet software and type the following into any cell:=PMT(A%/12,B,C) (If you're using OpenOffice, use semicolons instead of commas.)But instead of typing the letters A, B, and C, use these figures instead:
A = Enter the interest rate of the loan. Note that the formula divides it by 12 because you want the monthly interest rate, not the yearly interest rate.B = Enter the number of months you'll be making mortgage payments. That's 180 for a 15year loan, or 360 for a 30year loan.
C = Enter the amount of the loan. This is the price of the house, minus the down payment, plus closing costs (if you're rolling the closing costs into the loan).
Note that the result is a negative number. Don't worry about that. If it bothers you, put a minus sign between the = sign and "PMT".
Here's an example. Let's say our home costs $140,000. We're putting 5% down ($7,000), so we'll only need to borrow $133,000. But we're rolling the closing costs ($6,000) into the mortgage, which takes it back up to $139,000. Our interest rate is 8% and it's a 30year loan. So we've got:
=PMT(8%/12,360,139000) (If you're using OpenOffice, use semicolons instead of commas.)And our answer is $1020 a month. Don't forget that your mortgage payment also includes taxes and insurance. (See that section above.) Let's say that taxes are $2500/year and insurance is $1100/year. That's $3600/year together, or $300/month. So your total monthly mortgage payment is $1320 ($1020 from what we figured earlier, plus $300 for taxes and insurance.)
One more thing: If you put less than 20% down, you'll probably have to pay for Private Mortgage Insurance (PMI). PMI generally costs about 1/3700th to 1/1500th the price of the home. (On a $120,000 home, you'll pay $32 to $80/mo. for PMI).
Using this formula to pay off a loan early
You can use this formula to figure out how much you have to pay in order to pay your loan off early. For example, let's say you're five years into a 30year mortgage, and you want to pay the loan off in another 13 years instead of another 25. Just enter in the principal remaining on your loan (should be listed in your coupon book or on your mortgage statement), and use the number of months you want to pay it off in (in this case, 13 years x 12 months/year = 156 months).=PMT(8%/12,156,80000) (If you're using OpenOffice, use semicolons instead of commas.)
If you liked this site then you might like some of my other sites:
How to Find Cheap Airfare
How to Save Electricity
Slot machines demystified
Entire site ©19992023 Michael Bluejay Inc. • All information is "use at your own risk" Contact