What a mortgage calculation actually tells you
A mortgage calculation shows you what your monthly payment will be based on three numbers: the loan amount, the interest rate, and the number of years you have to pay it back. The formula banks use is the same one you can use with a calculator, a spreadsheet, or a free online tool. Knowing how to do this yourself means you can test different loan amounts and interest rates before you talk to a lender, and you can spot errors on documents later.
The monthly payment covers principal (the money you borrowed) and interest (what the lender charges you for lending it). Property taxes, homeowners insurance, and mortgage insurance are usually added on top of this base payment, but the core calculation is just those three inputs.
Key Takeaways
- The standard mortgage formula uses loan amount, annual interest rate, and loan term in years to produce a monthly payment.
- You can calculate by hand using the formula, but a spreadsheet or online calculator is faster and less error-prone.
- The monthly payment covers only principal and interest; taxes, insurance, and mortgage insurance are separate line items.
- Testing different loan amounts and rates before you explore to a lender shows you what you can actually afford.
- Your actual monthly bill will be higher than the calculated payment because of taxes and insurance added by your lender.
The mortgage payment formula and what each part means
The formula is: M = P × [r(1 + r)^n] / [(1 + r)^n − 1]
Here is what each letter stands for:
- M = your monthly payment
- P = the principal (the amount you borrowed)
- r = the monthly interest rate (your annual rate divided by 12)
- n = the total number of monthly payments (years × 12)
Example: You borrow $300,000 at 6.5% annual interest over 30 years. Your monthly interest rate is 0.065 ÷ 12 = 0.00542. Your total number of payments is 30 × 12 = 360. Plugging these into the formula gives you a monthly payment of about $1,896 before taxes and insurance.
The formula works because it accounts for the fact that as you pay down the principal, the interest you owe each month gets smaller. Early payments are mostly interest; later payments are mostly principal.
Using a spreadsheet to calculate your payment
Most people use a spreadsheet rather than doing the math by hand. In Microsoft Excel or Google Sheets, the function is PMT. The syntax is: =PMT(rate, nper, pv)
Here is how to set it up:
- In cell A1, type your annual interest rate as a decimal (for 6.5%, type 0.065).
- In cell A2, type the number of years (for a 30-year mortgage, type 30).
- In cell A3, type the loan amount (for $300,000, type 300000).
- In cell B1, type: =PMT(A1/12, A2*12, -A3)
- Press Enter. The result is your monthly payment.
The negative sign in front of A3 tells the spreadsheet to treat the loan as money you owe, so the result comes out as a positive number. If you leave out the negative sign, the payment will show as negative, which is just a display issue — the number itself is correct.
The advantage of a spreadsheet is that you can change any of the three numbers and see the new payment when ready. This makes it straightforward to compare a 15-year loan against a 30-year loan, or to see how a 0.5% rate difference affects your monthly bill.
Using an online calculator
Free mortgage calculators are available from Bankrate, NerdWallet, and many bank websites. You enter the loan amount, interest rate, and loan term, and the calculator returns your monthly payment in seconds.
Most online calculators also let you add property taxes, insurance, and mortgage insurance (PMI) to see your full monthly bill. Some calculators show you an amortization schedule, which is a month-by-month breakdown of how much of each payment goes to principal versus interest.
The trade-off is that you are relying on someone else's tool, so if you want to understand the math or verify the result, a spreadsheet gives you more control. For a quick estimate, though, an online calculator is the fastest route.
What happens to your payment over time
Your principal and interest payment stays the same every month for the life of the loan (this is called a fixed-rate mortgage). However, your property taxes and insurance may change year to year, so your total monthly bill can go up or down even though the mortgage payment itself does not.
If you have an adjustable-rate mortgage (ARM), your interest rate can change after an initial fixed period, which means your payment will change too. The calculation method is the same, but you would recalculate using the new rate and the remaining loan term whenever the rate adjusts.
Some people make extra payments toward principal to pay off the loan faster. The calculation does not change, but paying extra reduces the number of months you owe interest, which saves you money over the life of the loan.
Why your actual monthly bill is higher than the calculated payment
The mortgage payment formula gives you only principal and interest. Your lender will add other costs on top:
- Property taxes — paid to your county or municipality, varies by location and home value
- Homeowners insurance — required by the lender, varies by insurer and home condition
- Mortgage insurance (PMI) — required if you put down less than 20%, typically 0.5% to 1% of the loan amount per year
- HOA fees — if your home is in a planned community, paid to the homeowners association
Your lender collects these costs along with your mortgage payment and puts them into an escrow account, then pays the bills on your behalf. This is why your actual bill is often 25% to 40% higher than the principal-and-interest number alone.
When you test different loan amounts and rates, use an online calculator that includes these costs so you see the real number you will owe each month.
Common mistakes when calculating a mortgage payment
The most common error is forgetting to convert the annual interest rate to a monthly rate. If your rate is 6.5% and you plug 6.5 into the formula instead of 0.065 ÷ 12, your payment will be wildly wrong. Always divide the annual rate by 12 first.
Another mistake is using the wrong number of payments. A 30-year loan is 360 monthly payments, not 30. A 15-year loan is 180 payments. Multiply the years by 12 to get the right number.
A third mistake is forgetting that the calculated payment is only principal and interest. If you use this number to decide what you can afford, you will be short when taxes, insurance, and PMI are added. Always budget for the full monthly bill, not just the mortgage payment.
Frequently Asked Questions
What is the difference between a 15-year and 30-year mortgage payment?
A 15-year mortgage has a higher monthly payment but you pay much less interest overall. For a $300,000 loan at 6.5%, the 30-year payment is about $1,896 per month and the 15-year payment is about $2,896 per month. Over the life of the loan, you pay roughly $380,000 in interest on the 30-year loan but only $120,000 on the 15-year loan.
How does a lower interest rate change the monthly payment?
A lower rate reduces both your monthly payment and the total interest you pay. On a $300,000 loan over 30 years, a rate of 5.5% gives a payment of about $1,703, while 6.5% gives about $1,896. That $193 difference per month adds up to over $69,000 over 30 years.
Can I calculate what loan amount I can afford?
Yes. Work backward from the monthly payment you can afford. If you can pay $2,000 per month and your rate is 6.5% over 30 years, you can borrow about $317,000. Use an online calculator and adjust the loan amount until the payment matches your budget. Remember to add taxes, insurance, and PMI to see your true monthly bill.
What if I want to pay off my mortgage early?
The calculation does not change, but paying extra toward principal shortens the loan term and saves you interest. If you pay an extra $200 per month on a $300,000 loan at 6.5%, you will pay it off in about 21 years instead of 30, saving roughly $150,000 in interest.
Do I need to recalculate if my interest rate is adjustable?
Yes. When your rate adjusts, recalculate using the new rate and the remaining balance and term. Your payment will change, and your lender will send you a notice showing the new amount before it takes effect.