Skip to main content
HL1Housing Living 101

How to Build a Home Loan Payment Calculator in Excel

By Tanika Patel
7 minute read

What a Home Loan Payment Formula Does

An Excel formula calculates your monthly mortgage payment based on three numbers: the loan amount, the interest rate, and how many months you have to pay it back. The formula is called PMT, and it lives in nearly every spreadsheet program. You enter your numbers once, and the sheet shows you what you owe each month—before you sign anything.

This is different from a payment schedule, which shows every single payment over the life of the loan. A formula gives you the one number you need to know right now: what comes out of your account each month. It does not include property taxes, insurance, or HOA fees—just the principal and interest the lender charges.

Key Takeaways

  • The PMT formula in Excel calculates your monthly payment using the interest rate (divided by 12 for monthly), the number of months, and the loan amount.
  • You must enter the interest rate as a decimal and divide it by 12 because mortgages are quoted as annual rates but paid monthly.
  • The formula returns a negative number by default; put a minus sign in front of it to show the payment as a positive amount you pay out.
  • Changing any one number—the rate, the term, or the loan amount—instantly recalculates your payment, so you can test different scenarios.
  • The PMT formula only covers principal and interest; it does not include taxes, insurance, or other costs that affect your true monthly housing expense.

The PMT Formula and How to Set It Up

The PMT formula syntax is: =PMT(rate, nper, pv). In plain terms: rate is your monthly interest rate, nper is the number of months you are paying, and pv is the present value—the loan amount you borrowed. Here is what each part means in a real mortgage.

If you have a $300,000 loan at 6.5% annual interest over 30 years, you set it up like this: the annual rate 6.5% becomes 0.065 divided by 12 (because you pay monthly), which is 0.00542. The 30-year term becomes 360 months (30 times 12). The loan amount is 300000. Your formula reads: =PMT(0.065/12, 360, -300000). The minus sign in front of the loan amount tells Excel to treat it as money you owe, not money you have.

Excel will return a number like -1896.20. The negative sign means money flowing out of your pocket. To display it as a positive number (the way your lender will show it), wrap the formula in a minus sign: =-PMT(0.065/12, 360, -300000). Now it shows 1896.20—your monthly payment in dollars.

Building a Scenario Comparison Sheet

The real power of a formula is testing "what if" questions without calling your lender. Set up a small table with labels for loan amount, annual rate, term in years, and monthly payment. Put the numbers you are considering in separate cells, then reference those cells in your PMT formula instead of typing the numbers directly.

For example, put your loan amount in cell B2, your rate in B3, and your term in B4. Your formula becomes: =-PMT(B3/12, B4*12, -B2). Now when you change the number in B3 from 6.5 to 7.0, the payment recalculates instantly. You can copy this formula across to test three different rates, or down to test three different loan amounts, and see all the payments side by side.

This is how you answer questions like "How much does an extra 0.5% in interest cost me per month?" or "What is my payment if I put down $50,000 instead of $40,000?" without waiting for a loan officer to run the numbers.

Why the Interest Rate Must Be Divided by 12

Mortgage rates are always quoted as annual percentages. Your lender says "6.5% per year." But you pay monthly, not yearly. Excel needs the monthly rate to calculate a monthly payment. Dividing the annual rate by 12 gives you the rate that applies to each month.

If you forget to divide and enter 0.065 instead of 0.065/12, your payment will be roughly 12 times too high. This is the most common mistake when building a mortgage formula. Double-check that your rate cell is divided by 12 before you trust the number.

Converting Years to Months in the Formula

Mortgage terms are usually stated in years: 15 years, 20 years, 30 years. The PMT formula needs months. A 30-year mortgage is 360 months. A 15-year mortgage is 180 months. If you put your term in a cell as years, multiply it by 12 in the formula.

For example, if cell B4 contains 30 (meaning 30 years), your formula uses B4*12 to convert it to 360 months. This way, if you change B4 to 20, the formula automatically recalculates using 240 months. You do not have to rewrite the formula every time you test a different term.

What the PMT Formula Does Not Include

A PMT formula shows only principal and interest—the money that goes to the lender to pay off the loan itself. It does not include property taxes, homeowners insurance, HOA fees, or mortgage insurance (PMI). These costs vary by location, the home price, and your down payment, so no single formula can account for them.

Your actual monthly housing payment is higher than what PMT shows. To get a true picture, add estimated taxes and insurance on top of the PMT result. Your lender will provide a Loan Estimate that includes all these costs; use that as your real monthly number for budgeting.

Testing Different Scenarios Quickly

Once your formula is built, you can test dozens of scenarios in minutes. Change the down payment (which changes the loan amount), adjust the rate up or down by half a percent, or switch from a 30-year to a 20-year term. Each change recalculates instantly.

This helps you answer real questions: "Can I afford this house if rates go up?" "How much does a larger down payment save me per month?" "Is a 15-year mortgage worth the higher payment?" You can print or screenshot different scenarios and compare them side by side, which is much faster than asking a lender to run each one.

Frequently Asked Questions

What if I want to include property taxes and insurance in my formula?

Add separate cells for estimated annual property tax and annual insurance, divide each by 12 to get the monthly amount, then add both to your PMT result. For example: =(-PMT(B3/12, B4*12, -B2)) + (B5/12) + (B6/12), where B5 is annual tax and B6 is annual insurance. Your lender's Loan Estimate will give you the tax and insurance estimates to use.

Why does PMT show a negative number?

Excel treats the loan amount as money you received (positive) and the payment as money you owe (negative). To flip it to a positive number that makes sense visually, put a minus sign in front of the entire formula: =-PMT(...). This is purely cosmetic; the actual payment amount is the same either way.

Can I use this formula to calculate a payment if I already have a monthly payment and want to find the loan amount?

Yes, but you would use the PV formula instead of PMT. PV finds the present value (loan amount) when you know the payment. The syntax is =PV(rate, nper, pmt). This is useful if you know you can afford $1,500 per month and want to know what loan amount that supports.

What happens if I change the interest rate in my formula?

The payment recalculates instantly. This is why a formula is so useful for testing: you can see exactly how much a 0.25% rate increase costs you per month without calling anyone. Just change the number in the rate cell and watch the payment update.

Do I need to include points or origination fees in the PMT formula?

No. Points and origination fees are one-time costs paid at closing, not part of your monthly payment. They reduce the cash you have at closing but do not change the PMT calculation. If you want to factor them into your total cost, add them separately and divide by the number of months to see the average monthly cost.