Blog
The Excel PMT formula calculates mortgage payments in one line: =PMT(0.07/12, 360, -300000) returns $1,996/month for a $300K loan at 7% over 30 years. For a quick number without opening Excel, the online mortgage calculator gives the same answer instantly.
| Part | What It Means | Example Value | Why |
|---|---|---|---|
| =PMT( | Function name | — | Calculates payment for a loan |
| rate/12 | Monthly interest rate | 0.07/12 = 0.00583 | Annual rate divided by 12 months |
| nper | Total number of payments | 30*12 = 360 | Years times 12 months |
| -pv | Loan amount (negative) | −300000 | Negative because you receive the money |
| ) | Close | — | — |
Full formula: =PMT(0.07/12, 360, -300000) → $1,996/month
Need a quick answer without opening a spreadsheet?
Open Mortgage Calculator| Loan Amount | Rate | Term | PMT Formula | Monthly Payment |
|---|---|---|---|---|
| $200,000 | 6.5% | 30 yr | =PMT(0.065/12,360,-200000) | $1,264 |
| $300,000 | 7.0% | 30 yr | =PMT(0.07/12,360,-300000) | $1,996 |
| $400,000 | 7.0% | 30 yr | =PMT(0.07/12,360,-400000) | $2,661 |
| $400,000 | 7.0% | 15 yr | =PMT(0.07/12,180,-400000) | $3,595 |
| $500,000 | 6.5% | 30 yr | =PMT(0.065/12,360,-500000) | $3,161 |
An amortization schedule shows exactly how much of each payment goes to principal vs interest. Here is how to build one:
A1: Payment # | B1: Payment | C1: Interest | D1: Principal | E1: Remaining Balance
Loan amount in G1: $300,000 | Annual rate in G2: 7% | Term in G3: 30 years
| Cell | Formula | What It Calculates |
|---|---|---|
| A2 | 1 | Payment number |
| B2 | =PMT($G$2/12,$G$3*12,-$G$1) | Fixed monthly payment ($1,996) |
| C2 | =$G$1*$G$2/12 | Interest on full balance ($1,750) |
| D2 | =B2-C2 | Principal portion ($246) |
| E2 | =$G$1-D2 | Remaining balance ($299,754) |
| Cell | Formula | Change from Row 2 |
|---|---|---|
| A3 | =A2+1 | Increment payment number |
| B3 | =B2 | Same payment every month |
| C3 | =E2*$G$2/12 | Interest on PREVIOUS remaining balance |
| D3 | =B3-C3 | Principal = payment minus interest |
| E3 | =E2-D3 | New balance = old balance minus principal |
Copy row 3 down to row 361 (payment 360). Watch how interest decreases and principal increases each month. In month 1, you pay $1,750 interest and $246 principal. By month 360, you pay $12 interest and $1,984 principal.
Add column F: "Extra Payment." Enter $200 in any month you want to pay extra. Change the balance formula:
E3 = E2 - D3 - F3
On a $300K loan at 7%, paying an extra $200/month saves ~$98,000 in interest and pays off 5.5 years early. The spreadsheet shows you exactly which month you become mortgage-free.
| Task | Excel | Online Calculator | Winner |
|---|---|---|---|
| Quick monthly payment | ~Open spreadsheet, type formula | ✓ Enter 3 numbers, get answer | Online |
| Full amortization schedule | ✓ 360-row breakdown | ✗ Most do not show this | Excel |
| Model extra payments | ✓ Add column, see exact payoff date | ~Some support this | Excel |
| Compare 5 scenarios at once | ✓ Multiple tabs or columns | ✗ One calculation at a time | Excel |
| Share with spouse/advisor | ✓ Send the spreadsheet file | ✓ Share the URL | Tie |
| Phone calculation | ✗ Mobile Excel is painful | ✓ Works perfectly on phone | Online |
For a quick number: online calculator. For full planning: build the spreadsheet above.
Open Mortgage Calculator