Set up your spreadsheet with the PMT function
Excel has a built-in function called PMT that calculates your monthly mortgage payment in one formula. The function takes three pieces of information: your interest rate, the number of payments you'll make, and the loan amount. Once you enter these correctly, Excel does the math.
The formula looks like this: =PMT(rate, nper, pv). The rate is your monthly interest rate (annual rate divided by 12), nper is the total number of monthly payments, and pv is the loan amount as a negative number. Excel requires the loan amount to be negative because it treats money you borrow as a negative cash flow.
Here's a concrete example. Say you're borrowing $300,000 at 6.5% annual interest over 30 years. Your monthly rate is 0.065 divided by 12, which equals 0.00542. Your total payments are 30 times 12, which equals 360. Your formula becomes =PMT(0.00542, 360, -300000). Excel returns approximately $1,896, which is your monthly principal and interest payment.
Key Takeaways
- The PMT function calculates monthly payment using three inputs: monthly interest rate, total number of payments, and loan amount as a negative number.
- Your monthly interest rate is your annual rate divided by 12, and your total payments equal the loan term in years multiplied by 12.
- The PMT result includes only principal and interest, not property taxes, insurance, or HOA fees.
- You can build a reusable calculator by putting your loan details in separate cells and referencing those cells in your PMT formula.
- A second formula using IPMT and PPMT functions lets you see how much of each payment goes toward interest versus principal.
Create a reusable calculator template
Rather than typing the formula fresh each time, set up a template where you enter your loan details once and the payment calculates automatically. This approach lets you test different scenarios without rewriting formulas.
In column A, create labels: "Loan Amount", "Annual Interest Rate", "Loan Term (Years)", "Monthly Payment". In column B, enter the actual numbers. For a $300,000 loan at 6.5% over 30 years, your cells look like this:
| Cell | Label | Value |
| B1 | Loan Amount | 300000 |
| B2 | Annual Interest Rate | 0.065 |
| B3 | Loan Term (Years) | 30 |
| B4 | Monthly Payment | =PMT(B2/12, B3*12, -B1) |
Now you can change any number in B1, B2, or B3 and the payment in B4 updates when ready. This makes it straightforward to compare a 15-year mortgage against a 30-year one, or see how a rate change affects your payment.
Understand what the PMT result does and does not include
The number Excel returns is your principal and interest payment only. It does not include property taxes, homeowners insurance, mortgage insurance, or HOA fees. Your actual monthly payment to the lender will be higher if any of these explore to your loan.
If you want to see your full monthly housing cost, add a row below your PMT result. Label it "Property Tax (Monthly)", "Insurance (Monthly)", and "Other Fees (Monthly)", then sum all four amounts. This gives you the true amount you'll owe each month.
For example, if your PMT result is $1,896, your property tax is $250 per month, and your insurance is $150 per month, your total housing payment is $2,296. The PMT function cannot calculate these other costs because they vary by location and lender.
Break down each payment into principal and interest
As you pay down your mortgage, each monthly payment splits between interest (which goes to the lender) and principal (which reduces what you owe). Early in the loan, most of your payment is interest. Later, most is principal. Excel can show you this split for any payment using the IPMT and PPMT functions.
IPMT calculates the interest portion of a single payment. PPMT calculates the principal portion. Both functions need the same inputs as PMT, plus one more: the payment number you want to examine.
Using your $300,000 loan at 6.5% over 30 years, your first payment breaks down like this: =IPMT(0.00542, 1, 360, -300000) returns about $1,625 in interest, and =PPMT(0.00542, 1, 360, -300000) returns about $271 in principal. By payment 360 (the last one), the split flips: almost all principal, almost no interest.
Build an amortization schedule to track the loan over time
An amortization schedule is a month-by-month table showing your payment, how much goes to interest, how much goes to principal, and your remaining balance. Excel can generate this automatically once you set it up.
Create columns for Payment Number, Payment Amount, Interest Paid, Principal Paid, and Remaining Balance. In the first row, enter 1 for the payment number, your PMT result for the payment amount, and use IPMT and PPMT to calculate interest and principal. For the remaining balance, subtract the principal paid from your original loan amount.
In the second row, enter 2 for the payment number, the same PMT amount, and use IPMT and PPMT again but with the payment number set to 2. For the remaining balance, subtract the principal paid from the previous row's remaining balance. Then copy this row down for all 360 payments. Excel will adjust the payment number and remaining balance automatically, giving you a complete picture of how your loan pays down over 30 years.
Test different scenarios to compare loan options
Once your calculator is built, you can quickly see how different choices affect your payment. Change the loan amount to see what a larger or smaller down payment means. Change the interest rate to compare what you'd owe at different rates. Change the loan term to see the difference between a 15-year and a 30-year mortgage.
For instance, keeping everything else the same, a $300,000 loan at 6.5% over 15 years costs about $2,596 per month, while the same loan over 30 years costs about $1,896. The 15-year payment is higher, but you pay far less total interest over the life of the loan. Your calculator lets you see both numbers side by side.
You can also test rate scenarios. If rates drop from 6.5% to 6%, your 30-year payment on $300,000 falls to about $1,799. If rates rise to 7%, it climbs to about $1,996. This helps you understand what refinancing might save you, or what a rate increase would cost.
Frequently Asked Questions
Why does Excel want the loan amount as a negative number?
Excel's PMT function treats borrowed money as a negative cash flow (money going out) and your payments as positive cash flows (money coming in). This is standard accounting convention. If you forget the negative sign, Excel returns a negative payment, which you can straightforward ignore the minus sign on.
Can I use PMT for loans other than mortgages?
Yes. PMT works for any loan: car loans, personal loans, student loans. You just need the loan amount, annual interest rate, and the number of months you'll be paying. The formula is identical.
What if my interest rate changes during the loan?
PMT assumes a fixed rate for the entire loan term. If you have an adjustable-rate mortgage, PMT can only calculate your payment for the current rate period. When your rate adjusts, you'll need to recalculate using the new rate and the remaining balance as your new loan amount.
How do I account for property taxes and insurance in my calculator?
Add separate rows below your PMT result for each cost. Enter the monthly amount for property tax, insurance, and any other fees, then sum them all together. This gives you your total monthly housing payment, though only the PMT portion is calculated by Excel.
Can I use this calculator on Google Sheets or other spreadsheet programs?
Yes. The PMT, IPMT, and PPMT functions work the same way in Google Sheets, LibreOffice Calc, and most other spreadsheet software. The syntax is identical, so you can copy your formula directly between programs.