Mortgage Calculator Spreadsheet
Work out the full monthly mortgage payment with tax, insurance, PMI and HOA, compare three loan options and see the cash you need to close.
What's inside
One workbook, in Excel + Google Sheets. A mortgage calculator for when you are buying a home and want the payment you will really make, not just principal and interest:
- Start here: four steps and what the colors mean.
- Dashboard: the full monthly payment, cash you need at closing, total interest and the month PMI ends, with a chart of the payment parts for three loan options and a chart of the balance year by year.
- Calculator: home price, down payment as a percentage or an amount, rate, a 10 to 30-year term, property tax, insurance, PMI, HOA dues and closing costs. You get principal and interest, each escrow part, the total monthly payment and the cash to close.
- Compare: three loans side by side, for example 10% or 20% down, or 30 years against 15.
- Affordability: your income and other debts give the front-end and back-end debt-to-income ratios against the common 28% and 36% guidelines.
- Schedule: all 360 payments, with PMI dropping off when the balance reaches 78% of the price.
- Sample check: a sample home with the answers worked out separately. Every row shows ✓ when the formulas are right.
The sample, so you know what to expect
A $425,000 home with 10% down at 6.75% for 30 years: $2,480.89 principal and interest, $3,179.85 a month with tax, insurance and PMI, and $55,250 cash to close. PMI lasts 112 months. With 20% down the payment is $2,744.82 and there is no PMI.
Who it's for
A good fit if you
- Are house hunting and want a mortgage calculator in Excel or a mortgage payment calculator in Google Sheets that you can keep and change.
- Want to see how the down payment, rate and term change the monthly payment and the cash you need.
- Like to check a lender's numbers yourself.
Not for you if you
- Already have a loan and want to plan extra payments. Use the Loan Amortization Schedule.
- Need an adjustable-rate, FHA or VA loan modeled in detail. The file assumes a fixed rate and a conventional-style PMI estimate.
How it works
- Open the file. It comes filled with the sample home above.
- Check the sample. Open Sample check and see every row show ✓.
- Type in your own. Change the price, down payment, rate, tax and insurance on Calculator, then set up three options on Compare.
Questions
Does it work in Excel and Google Sheets?
Yes. You get an Excel file (.xlsx) and a Google Sheets version. Both use the standard PMT function, so the payment is the same in either.
Does the monthly payment include taxes and insurance?
Yes. It adds property tax, homeowners insurance, PMI and HOA dues to principal and interest, which is the PITI payment most lenders collect.
How is PMI worked out?
As a yearly percentage of the loan that you set, charged only when you put down less than 20%. It stops at the first payment where the balance is at or below 78% of the original price. It is an estimate; your lender quotes the real cost.
Can it tell me how much house I can afford?
It shows your debt-to-income ratios against the 28% and 36% rules of thumb and the highest housing payment within both. That is a guide, not a loan approval.
Can I add extra payments?
No. This file is for choosing a loan. To plan extra payments on a loan you have, use the Loan Amortization Schedule.
Can I get a refund?
Yes. If the template does not work for you, email [email protected] within 30 days of buying and you get your money back. See the refund policy.
Related templates
Disclaimer. For general information only. Not tax, legal or financial advice. Check the rules for your country or ask a qualified adviser. Read the full disclaimer.