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 numbers into cells, PMT does the math for you.

Open a blank Excel spreadsheet. In the first column, label your inputs: put "Loan Amount" in cell A1, "Annual Interest Rate" in A2, "Loan Term (Years)" in A3, and "Monthly Payment" in A4. These labels help you remember what each number represents when you come back to the spreadsheet later.

In column B, you'll enter the actual numbers. For now, leave B1, B2, and B3 empty—you'll fill those in with your own mortgage details. In cell B4, you'll write the PMT formula that calculates the payment based on whatever numbers you put in those cells above.

Key Takeaways

  • The PMT formula in Excel is =PMT(rate, nper, pv), where rate is your monthly interest rate, nper is the total number of monthly payments, and pv is the loan amount as a negative number.
  • You must divide your annual interest rate by 12 to get the monthly rate, and multiply your loan term in years by 12 to get the total number of payments.
  • Excel returns the payment as a negative number by default; put a minus sign before the formula or multiply by -1 to show it as a positive amount.
  • Once your formula is set up, you can change the loan amount, interest rate, or term and see the new payment calculate instantly.

Enter the PMT formula with the correct syntax

Click on cell B4 and type the formula. The basic structure is =PMT(rate, nper, pv). However, you need to adjust your numbers so Excel understands them correctly. The rate must be your monthly interest rate (not annual), nper must be the total number of monthly payments (not years), and pv must be the loan amount as a negative number.

If your annual interest rate is in cell B2, your loan term in years is in B3, and your loan amount is in B1, the formula looks like this:

=PMT(B2/12, B3*12, -B1)

Break this down: B2/12 converts your annual rate to a monthly rate. B3*12 converts years into months. The minus sign before B1 tells Excel that you're borrowing money (a negative cash flow from your perspective). Type this formula exactly as shown, then press Enter.

Input your mortgage numbers and read the result

Now enter your actual numbers. Click on cell B1 and type your loan amount—for example, 300000 for a $300,000 mortgage. Press Tab to move to B2 and enter your annual interest rate as a decimal. If your rate is 6.5%, type 0.065. Press Tab again and enter your loan term in years—typically 15 or 30.

As soon as you enter the last number, the formula in B4 calculates automatically. The result will appear as a negative number, like -1799.37. This is Excel's way of showing money going out. To display it as a positive number (which is more intuitive for a payment you'll make), go back to cell B4 and edit the formula to add a minus sign at the start: =-PMT(B2/12, B3*12, -B1). Press Enter, and it will now show 1799.37.

This number is your principal and interest payment only—it does not include property taxes, insurance, or HOA fees if you have them.

Add columns to break down principal, interest, and total cost

Once you have your basic payment, you can expand the spreadsheet to show how much of each payment goes toward interest versus principal. This helps you understand how your loan works over time.

In cell A6, type "Total Interest Paid". In B6, enter the formula =B4*B3*12-B1. This multiplies your monthly payment by the total number of months, then subtracts the original loan amount. The result is the total interest you'll pay over the life of the loan.

In cell A7, type "Total Amount Paid". In B7, enter =B4*B3*12. This shows the sum of all payments you'll make. The difference between B7 and B1 is your total interest cost.

These summary rows give you a quick view of the real cost of borrowing. A 30-year mortgage at 6.5% on $300,000, for example, costs significantly more in total payments than the original loan amount because of accumulated interest.

Create an amortization table to see payment breakdown by month

An amortization table shows exactly how much principal and interest you pay each month. Start in row 9. In cell A9, type "Month". In B9, type "Payment". In C9, type "Principal". In D9, type "Interest". In E9, type "Balance".

In A10, type 1. In B10, reference your monthly payment: =B4. In D10, calculate the interest for month one: =E9*B2/12 (where E9 is your starting balance, which should be your loan amount). In C10, calculate principal: =B10-D10. In E10, calculate the remaining balance: =E9-C10. For the first row, E9 should be your loan amount (B1).

Once row 10 is complete, select cells A10 through E10 and copy them down for as many rows as you have months. For a 30-year loan, that's 360 rows. Excel will adjust the cell references automatically as it copies down. You'll see the interest portion shrink and the principal portion grow with each payment, and the balance decrease until it reaches zero at the end.

Adjust the formula to test different scenarios

The real power of an Excel calculator is that you can change any number and instantly see the impact. Try entering different interest rates in B2 to see how a rate change affects your payment. Change B3 to see the difference between a 15-year and 30-year loan. Adjust B1 to compare different home prices.

For example, if you change your interest rate from 0.065 to 0.07, the payment recalculates immediately. If you change your term from 30 years to 15 years, you'll see the monthly payment increase but the total interest paid decrease significantly. These comparisons help you understand the trade-offs between different loan options without having to rebuild the spreadsheet each time.

You can also add a row for down payment and calculate the loan amount as the home price minus down payment. This lets you see how a larger down payment reduces both your monthly payment and total interest cost.

Common setup mistakes and how to fix them

The most frequent error is forgetting to divide the annual interest rate by 12. If you enter 0.065 instead of 0.065/12, your payment will be wildly too high. The second common mistake is entering the loan term in months instead of years, or forgetting to multiply years by 12 in the formula. Both produce incorrect results.

Another mistake is entering the loan amount as a positive number when the PMT function expects it as negative. If your formula is =PMT(B2/12, B3*12, B1) without the minus sign before B1, Excel will return an error or a nonsensical result. Always use -B1 or put a minus sign at the start of the entire formula.

If your payment shows as a very small number or zero, check that your interest rate is entered as a decimal (0.065, not 6.5). If it shows as a negative number and you want it positive, add a minus sign before PMT in the formula. If the amortization table's balance doesn't reach zero at the end, you may have a rounding error in how you set up the principal and interest columns—recalculate to ensure each row's principal plus interest equals the payment.

Frequently Asked Questions

What if my interest rate changes or is variable?

The PMT formula calculates a fixed payment based on a single interest rate. If your rate is variable, you can use the formula to see what your payment would be at different rates, but the actual payment will change when your rate adjusts. Create separate rows with different rates to compare scenarios, or consult your loan documents for how your specific variable rate adjusts.

Should I include property taxes and insurance in the PMT formula?

No. The PMT function calculates principal and interest only. Property taxes, homeowners insurance, and PMI (if applicable) are separate costs. Add them as additional line items below your mortgage payment to see your total monthly housing cost, but they don't go into the PMT formula itself.

Can I use this spreadsheet to compare different loan offers?

Yes. Create separate sections for each loan offer, each with its own PMT formula using different rates and terms. Line them up side by side so you can see the monthly payment and total interest cost for each option at a glance. This makes it easy to spot which offer costs less over time.

What does it mean if the payment shows as a negative number?

Excel treats borrowed money as negative cash flow (money going out). The negative sign is correct; it just means you're paying it. To display it as positive, put a minus sign before the PMT function in your formula, like =-PMT(B2/12, B3*12, -B1), or multiply the result by -1.

Can I use this to calculate a car loan or personal loan payment?

Yes. The PMT formula works for any fixed-rate loan. Enter the loan amount, annual interest rate, and term in years, and it calculates the monthly payment. The only difference is the numbers you plug in—a car loan might be $25,000 at 5% for 5 years, while a mortgage is much larger and longer.