Monthly Loan Payment Formula in Excel and Google Sheets
Purpose
I built my first mortgage calculator in Excel before I knew anything about financial functions, and it was a mess…
I built my first mortgage calculator in Excel before I knew anything about financial functions, and it was a mess of manual cell arithmetic. When I discovered PMT I deleted the whole thing and replaced it with one formula. PMT calculates the fixed payment needed to fully repay a loan over a specified period at a fixed interest rate. Plug in the interest rate, the number of payments, and the loan amount and it tells you exactly what you need to pay each month. It handles mortgages, car loans, personal loans — anything with regular fixed payments. The only thing that trips people up is the rate argument: you must give it the rate per payment period, not the annual rate. A 6% annual rate with monthly payments is 6%/12 per month. Get that right and the rest is straightforward.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=PMT(rate, nper, pv, [fv], [type])
Arguments
| Argument | Required | Description |
|---|---|---|
| rate | Required | The interest rate per payment period. For monthly payments divide the annual rate by 12. Example: a 6% annual rate becomes 6%/12 or 0.5% per month. Can be entered as 6%/12 or 0.06/12 — both work. |
| nper | Required | Total number of payment periods. For monthly payments multiply years by 12. A 30-year mortgage has 30*12 = 360 monthly payments. |
| pv | Required | Present value — the loan amount today. Enter as a positive number for a loan you receive. The convention is that money received is positive. |
| fv | Optional | Future value — the balance remaining after the last payment. For a fully repaid loan this is 0 (the default). Use a non-zero fv for balloon loans or savings goals. |
| type | Optional | 0 for payments at the end of each period (default and most common for loans). 1 for payments at the beginning of each period (some leases and annuities). |
How it works
PMT works by solving the standard loan amortisation formula. Every payment you make covers two things — a portion of the interest owed and a portion of the principal balance. Early in the loan, most of each payment goes to interest because the balance is high. As the balance decreases over time, less of each payment goes to interest and more goes to principal. PMT calculates the fixed payment amount that keeps this balance reaching exactly zero at the end of the final payment. The rate argument must match the payment frequency — monthly payments need the monthly rate which is the annual rate divided by 12. The nper argument is the total number of payments — 30 years of monthly payments is 360. The pv argument is the present value — the loan amount today. The result is negative by convention because it represents money going out. Use -PMT() or ABS(PMT()) to display it as a positive payment amount.
Examples
1
Calculate the monthly payment for a £200,000 mortgage at 4.5% annual interest over 25 years.
| A | B | C | |
|---|---|---|---|
| 1 | Loan Parameter | Value | Monthly Payment |
| 2 | Loan Amount | 200000 | =-PMT(B3/12,B4*12,B2) |
| 3 | Annual Rate | 4.50% | |
| 4 | Term (years) | 25 | |
| 5 | Monthly Rate | 0.375% | |
| 6 | Total Payments | 300 | |
| 7 | Total Paid | 1108.53 x 300 | |
| 8 | Total Interest | 132559 | |
| 9 | |||
| 10 | |||
| 11 |
Row 2: 1108.53
— A £200,000 mortgage at 4.5% over 25 years requires a monthly payment of £1,108.53.
The minus sign in front of PMT converts the negative cash flow result to a positive display number. Without it the formula returns -1108.53 which is correct mathematically but confusing to read.
2
Build a loan comparison showing monthly payments for different rates and terms.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Scenario | Annual Rate | Term (Years) | Monthly Payment |
| 2 | Conservative | 3.50% | 30 | =-PMT(B2/12,C2*12,200000) |
| 3 | Standard | 4.50% | 25 | 1108.53 |
| 4 | Higher rate | 5.50% | 25 | 1227.71 |
| 5 | Shorter term | 4.50% | 15 | 1529.99 |
| 6 | Longer term | 4.50% | 30 | 1013.37 |
| 7 | Best case | 3.00% | 30 | 843.21 |
| 8 | Worst case | 6.50% | 20 | 1491.15 |
| 9 | Investment property | 5.00% | 25 | 1169.18 |
| 10 | Refinance option | 4.00% | 20 | 1211.96 |
| 11 | First home buyer | 4.25% | 30 | 983.88 |
Row 2: 898.09
— At 3.5% over 30 years a £200,000 loan costs £898.09 per month. Compare to £1,108.53 at 4.5% over 25 years — the lower rate and longer term reduce the monthly payment by £210.
This table shows the real trade-off between rate and term. A shorter term at the same rate costs more per month but saves significantly on total interest.
Common use cases
1. Compare monthly payments for the same loan at different interest rates to see the impact of rate changes
=-PMT(C2/12,D2*12,B2)
2. Calculate how much car loan you can afford given a maximum monthly payment budget
=PV(B2/12,C2*12,-D2)
3. Build a loan comparison table showing monthly payment, total paid, and total interest for different terms
=-PMT(rate/12,years*12,loan)
4. Model a business equipment loan to determine if the monthly payment fits the projected cash flow
=-PMT(6%/12,48,25000)
5. Calculate the payment for an interest-only period by setting nper to the interest-only months and adding a balloon payment in fv
=-PMT(rate/12,60,200000,0)
Common errors
Result is negative
PMT returns a negative result by design — it represents a payment (cash outflow). This is not an error.
Fix: Put a minus sign before PMT: =-PMT(rate,nper,pv) to display the payment as a positive number. Or use ABS(PMT(rate,nper,pv)).
#NUM! error
The rate or nper argument contains a non-numeric value, or the calculation results in an impossible payment (such as a zero interest rate with a zero loan amount).
Fix: Check that rate and nper are numbers. Verify that the loan amount (pv) is a positive number and the rate is expressed as a decimal or percentage.
Monthly payment seems too high or too low
The annual rate was used as the rate argument without dividing by 12, or the term in years was used without multiplying by 12. This is by far the most common PMT mistake.
Fix: Always divide the annual rate by 12 for monthly payments: rate/12. Always multiply years by 12 for monthly periods: years*12. Double-check both arguments before trusting the result.
Tips and variations
Build a sensitivity table to see how rate changes affect monthly payments
Create a table with different interest rates in a row and different loan terms in a column. Use PMT with the rate and term as cell references and you can see all combinations at a glance. This is useful when comparing mortgage products or evaluating refinancing.
=-PMT(C$1/12,$B2*12,loan_amount)
Calculate total interest paid by multiplying payment by periods minus the principal
PMT tells you the monthly payment. To find total interest paid multiply the monthly payment by the number of payments and subtract the loan amount. This shows the full cost of the loan over its term.
=ABS(PMT(rate,nper,pv))*nper - pv
Use named cells to make the formula readable
Store loan amount, annual rate, and term in named cells called LoanAmount, AnnualRate, and TermYears. Then write =-PMT(AnnualRate/12, TermYears*12, LoanAmount). Anyone reading the formula immediately understands it without needing to trace cell references.
=-PMT(AnnualRate/12, TermYears*12, LoanAmount)
Excel vs Google Sheets
Excel vs Google Sheets
PMT works identically in Excel and Google Sheets with the same arguments and behaviour. The cash flow sign convention — payments are negative — is the same in both applications. Formulas copy between the two applications without any changes.
Related reading
Frequently asked questions
PMT calculates the fixed periodic payment for a loan or mortgage given an interest rate, number of payment periods, and loan amount. It assumes a fixed interest rate and equal payments each period. The result is the amount you pay each month (or each period) to fully repay the loan by the end of the term.
PMT returns a negative number because it represents cash flowing out of your account — a payment you make. Excel uses the cash flow sign convention where money received is positive and money paid is negative. Wrap PMT in ABS() or add a minus sign before it to display the payment as a positive number.
Divide the annual rate by 12 for the rate argument. If the annual rate is 6%, the monthly rate is 6%/12 = 0.5%. Also multiply the number of years by 12 for the nper argument. A 5-year loan is 60 monthly payments. Example: =PMT(6%/12, 5*12, 100000).
Yes — adjust both arguments to match the payment frequency. For weekly payments divide the annual rate by 52 and multiply the years by 52. For bi-weekly divide by 26 and multiply by 26. The logic is always: rate per period and number of periods matching the same period length.
Multiply the PMT result by the number of payments, then subtract the original loan amount: =(ABS(PMT(rate,nper,pv))*nper)-pv. This gives total interest paid. Alternatively use the CUMIPMT function which calculates cumulative interest between any two payment periods.