PMT Function in Excel and Google Sheets
Purpose
Calculate the fixed periodic payment required to fully repay a loan at a constant interest rate
Return value
A negative number representing the payment per period (negative because it is a cash outflow)
PMT is the financial function I use to answer the question every loan or financing scenario starts with: what is the monthly payment? Given a loan amount, an interest rate, and a repayment period, PMT calculates the fixed payment required to clear the balance to zero. It is the engine behind every mortgage calculator, car finance tool, and equipment lease comparison I build in a spreadsheet. The result comes out as a negative number because financial functions follow cash flow sign convention -- money leaving your account is negative -- which trips people up the first time. Either enter the loan amount as negative in the formula to get a positive result, or wrap PMT in ABS. The more important thing is to set up the rate and nper arguments correctly for monthly payments: divide the annual rate by 12 and multiply the years by 12. Get that right and PMT is a straightforward and extremely useful function. The monthly loan payment formula and compound interest formula together cover the two core financial calculations that come up in almost every business and personal finance spreadsheet.
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 on an annual rate, divide by 12: 5% annual = 0.05/12 per month. |
| nper | Required | Total number of payment periods. For monthly payments over 3 years: 3*12 = 36. |
| pv | Required | The present value -- the loan amount today. Enter as positive and PMT returns negative, or enter as negative and PMT returns a positive payment amount. |
| fv | Optional | Future value -- the balance remaining after the last payment. Defaults to 0, meaning the loan is fully repaid. |
| type | Optional | 0 (default) for payments at end of period, 1 for payments at beginning of period. |
How it works
PMT requires three key arguments: the interest rate per period, the total number of periods, and the present value (loan amount). The most common mistake is mismatching the rate and nper periods -- an annual rate of 5% with 36 monthly payments must become 0.05/12 and 36, not 0.05 and 3. Dividing the annual rate by 12 converts it to a monthly rate that matches monthly payment periods. The result is negative under standard cash flow convention. To get a positive display value, enter pv as negative: =PMT(0.05/12,36,-10000) returns a positive 277.99. Alternatively wrap in ABS: =ABS(PMT(0.05/12,36,10000)). The fv argument handles balloon payments -- leave it at 0 for standard loans that fully repay. PMT is the total payment; IPMT and PPMT split it into interest and principal portions for building a full amortisation schedule. The monthly loan payment formula post covers this function applied to real-world loan scenarios.
Examples
1
Calculate the monthly payment on a £10,000 loan at 5% annual interest repaid over 3 years.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan Amount | Annual Rate | Years | Monthly Payment |
| 2 | 10000 | 5% | 3 | =PMT(B2/12,C2*12,A2) |
| 3 | 10000 | 0.05 | 3 | -299.71 |
Row 2: -299.71
— The monthly payment is £299.71 (shown as negative because it is a cash outflow). Over 36 months the total paid is 299.71 × 36 = £10,789.56, meaning £789.56 in total interest.
The rate argument is B2/12 to convert the annual 5% rate to a monthly rate of 0.4167%. The nper is C2*12 to convert 3 years to 36 monthly periods. Getting this division and multiplication right is the most critical step in PMT.
2
Compare monthly payments across three different loan terms to show how repayment period affects the monthly amount.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan | Rate | Years | Monthly Payment |
| 2 | 10000 | 5% | 1 | =ABS(PMT($B$2/12,C2*12,$A$2)) |
| 3 | 10000 | 5% | 3 | 299.71 |
| 4 | 10000 | 5% | 5 | 188.71 |
| 5 | 10000 | 5% | 10 | 106.07 |
Row 2: 856.07
— A 1-year term requires £856.07 per month. Extending to 5 years drops the monthly payment to £188.71 but significantly increases total interest paid.
ABS wraps PMT to display positive values. The loan amount and rate are locked with $ signs so they do not shift when copying the formula down. Only the years column changes per row.
3
Calculate the monthly payment for a mortgage -- £250,000 at 4.5% annual interest over 25 years.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Mortgage | Rate | Years | Monthly Payment | Total Paid | Total Interest |
| 2 | 250000 | 4.5% | 25 | =ABS(PMT(B2/12,C2*12,A2)) | 1388.87*300 | Total-250000 |
| 3 | 250000 | 0.045 | 25 | 1388.87 | 416661.00 | 166661.00 |
Row 2: 1388.87
— The monthly mortgage payment is £1,388.87. Over 300 months (25 years) the total amount paid is £416,661 -- meaning £166,661 in interest on a £250,000 loan.
Total interest = (monthly payment × nper) − loan amount. Showing this alongside the monthly payment makes PMT much more informative for any financial decision -- the monthly payment is affordable but the total interest cost tells the fuller story.
Common use cases
1. Calculate the monthly repayment on a personal loan or car finance deal
=ABS(PMT(rate/12,years*12,loan_amount))
2. Compare monthly payments across different loan terms or interest rates in a scenario table
=ABS(PMT($B$2/12,C2*12,$A$2))
3. Calculate the monthly savings required to reach a target amount using the fv argument
=ABS(PMT(rate/12,months,0,-target))
4. Build a full loan amortisation schedule using PMT for total payment and IPMT and PPMT for the breakdown
=PMT($B$1/12,$C$1*12,$A$1)
5. Calculate lease payments where payments are made at the start of each period using type=1
=ABS(PMT(rate/12,months,value,0,1))
Common errors
Result is much higher or lower than expected
The rate was not converted to match the payment period -- using the annual rate directly with monthly periods (or vice versa). An annual rate of 5% with 36 monthly periods must be 0.05/12, not 0.05.
Fix: Always divide the annual rate by the number of payments per year: monthly = rate/12, quarterly = rate/4, annual = rate/1.
Result is negative when you expected positive
PMT returns negative by convention -- payments are cash outflows.
Fix: Wrap in ABS: =ABS(PMT(rate,nper,pv)) or enter pv as a negative value: =PMT(rate,nper,-pv) to get a positive result.
#NUM! error
The combination of rate, nper, and pv is mathematically impossible -- for example a zero or negative interest rate with certain inputs.
Fix: Check all three arguments have valid values. A zero rate is handled by =pv/nper (simple division). Negative rates are unusual and may indicate a sign error in the inputs.
Tips and variations
Always convert the annual rate to match the payment frequency
The most common PMT error is using the annual rate with monthly periods. Divide by 12 for monthly, 4 for quarterly, 1 for annual -- and multiply nper by the same factor. The rate and nper must always describe the same time period.
=ABS(PMT(0.05/12,3*12,10000))
Enter pv as negative to get a positive payment amount
The cleanest way to avoid ABS is to enter the loan amount as negative: =PMT(rate,nper,-loan_amount). This follows the cash flow convention that the loan is money received (positive inflow) being repaid (negative outflow).
=PMT(B2/12,C2*12,-A2)
Show total interest alongside the monthly payment
Multiply the monthly payment by nper and subtract the original loan to get total interest paid: =(ABS(PMT(rate,nper,pv))*nper)-pv. This one number often changes how people think about a financing decision.
=(ABS(PMT(B2/12,C2*12,A2))*C2*12)-A2
Excel vs Google Sheets
Excel vs Google Sheets
PMT works identically in Excel 2003 and newer and every version of Google Sheets. The cash flow sign convention, argument behaviour, and period-matching requirement are the same in both applications. Formulas copy between them without changes.
Related reading
Frequently asked questions
PMT calculates the fixed periodic payment required to fully repay a loan at a constant interest rate over a set number of periods. It returns the payment amount for each period -- monthly, quarterly, or annual depending on how you set up the arguments. The result is a negative number because it represents money going out.
PMT follows the cash flow sign convention used by all Excel financial functions: money received is positive, money paid out is negative. A loan payment is money leaving your account so it is returned as negative. To display it as a positive number, either enter the pv (present value) as a negative in the formula, or wrap PMT in ABS: =ABS(PMT(rate,nper,pv)).
Divide the annual rate by 12 for the rate argument and multiply the years by 12 for the nper argument: =PMT(annual_rate/12, years*12, loan_amount). For a 5% annual rate over 3 years: =PMT(0.05/12, 3*12, 10000). Always match the rate period to the payment period -- monthly payments need a monthly rate.
PMT gives the total payment per period (principal plus interest combined). IPMT gives only the interest portion of a specific period's payment. PPMT gives only the principal portion. Use PMT when you need the total payment amount. Use IPMT and PPMT when building a full amortisation schedule that breaks each payment into its components.
Yes. PMT works for any situation requiring equal periodic payments -- loans, leases, annuities, and savings plans. For an annuity or lease with payments at the beginning of each period rather than the end, set the type argument to 1: =PMT(rate, nper, pv, 0, 1). The default type of 0 assumes payments at the end of each period.