How to Use the PMT Function in Excel for Payments

Use Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a constant interest rate and regular payment intervals. For a $20,000 loan at 6% annual interest over five years, with the annual rate in B2, years in B3, and loan amount in B4, enter =PMT(B2/12,B3*12,B4) in a blank cell. The result is about -$386.66 per month; the minus sign represents money paid out. Before using the result, make sure the interest rate and number of periods match the payment schedule.

What You’ll Need

Excel for Microsoft 365 on Windows, or access to Excel for Mac or Excel for the web. PMT works in these versions, though ribbon layouts can differ.
The amount borrowed or invested, the interest rate for each payment period, and the total number of payment periods.
A clear payment frequency, such as monthly, quarterly, or annual. The rate and period count must use that same frequency.
Optional details if they apply: a future balance after the final payment, or whether payments happen at the beginning rather than the end of each period.

Step-by-Step

Decide what payment you want to calculate. PMT returns one equal payment for every period, assuming the rate does not change and the payment schedule remains regular. It can model loan repayments or regular contributions toward a target balance. It does not automatically include fees, taxes, insurance, or changing interest rates. For a real loan, check the lender’s stated terms before relying on a spreadsheet estimate.
Set the payment frequency. Choose whether payments are monthly, quarterly, or annual. Convert both the interest rate and the term to that frequency. For monthly payments on a loan with an annual rate of 6% and a five-year term, divide the annual rate by 12 and multiply five years by 12. This gives a monthly rate of 0.5% and 60 payments. Do not divide the rate by 12 if it is already stated as a monthly rate.
Enter the inputs in cells. For a simple monthly loan example, put the annual interest rate, 6%, in B2; the term in years, 5, in B3; and the amount borrowed, 20000, in B4. Add labels in A2:A4, such as Annual rate, Term in years, and Loan amount, so the model is easy to check later. Enter rates as percentages or decimals consistently; Excel understands 6% as 0.06.
Enter PMT in a blank cell. With the annual interest rate in B2, the loan term in years in B3, and the positive loan amount in B4, enter =PMT(B2/12,B3*12,B4) in B5. Press Enter. Excel returns approximately -386.66, meaning a payment of about $386.66 leaves your account each month. The negative sign is normal: the borrowed amount is an inflow, while repayments are outflows. If you want to display the payment as a positive number, use a negative present value instead: with the same inputs in B2:B4, enter =PMT(B2/12,B3*12,-B4) in B5. This returns approximately 386.66. Choose one sign convention and keep it consistent in related calculations.
Understand the arguments. PMT uses the rate per period, total number of periods, and present value (usually the starting loan balance). It also accepts an optional future value and an optional timing setting. If you leave out the future value, Excel treats it as zero, which means the balance is paid off at the end. If you leave out timing, Excel assumes payments are due at the end of each period. For a loan with an amount due at the end, those defaults are usually appropriate.
Check the result against the total paid. With the annual rate in B2, years in B3, principal in B4, and the monthly payment returned in B5, enter =-B5*B3*12 in B6 to show total repayments as a positive amount. The result is about $23,199.36. To estimate the interest paid over the full term, with the same cell layout, enter =B6-B4 in B7; this returns about $3,199.36. These totals assume the payment is rounded only for display, not in the underlying formula. The lender’s actual schedule may differ if it rounds each payment or applies fees.

Common Mistakes

Mixing annual and monthly inputs. Supplying an annual rate with a monthly period count makes PMT treat that annual figure as the rate for every month. That can make the calculated payment far too high. Convert the rate and term to the payment frequency before calculating.

Using the wrong rate basis. A quoted annual percentage rate and an effective annual yield may not mean the same thing for compounding. Dividing an annual rate by 12 is a common estimate for monthly calculations, but the lender’s stated periodic rate or compounding rules should take priority. Do not silently convert a rate if the agreement defines it differently.

Misreading the negative result. PMT follows cash-flow signs. A positive loan amount generally produces a negative payment because one is money received and the other is money paid. The sign does not mean the calculation failed.

Forgetting extras or changing terms. The function models equal payments at a fixed rate. It does not account for an origination fee, a balloon payment unless you specify a future value, variable rates, or extra payments that shorten the loan. Treat its answer as a payment calculation based on the inputs, not a complete loan quote.

Tips

Keep inputs in separate cells rather than typing every number directly into the function. If the rate, principal, or term changes, the result will update automatically, and another person can inspect the assumptions. Add clear labels and units, such as annual rate, years, and monthly payment.

Format the result as currency using Home > Number Format in Excel for Microsoft 365 on Windows. This changes how the value is displayed, not the calculation’s precision. Keep the underlying result unrounded unless you specifically need to model a payment rounded to cents each period; rounding every period can affect the final balance.

For a payment due at the beginning of each period, the optional timing argument is 1; for a payment due at the end, it is 0. With the annual rate in B2, years in B3, and principal in B4, enter =PMT(B2/12,B3*12,B4,0,1) in B5 to calculate beginning-of-month payments. For the example loan, this is about -$384.73 per month. It is lower in magnitude than the end-of-month payment because each payment is made earlier. Use this setting only when the arrangement actually calls for payments at the beginning.

The PMT function and the formulas shown work in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. Cell entry and calculation are the same; menu placement and formatting controls may look different across versions. If a workbook uses a different regional setting, Excel may require semicolons instead of commas between arguments.

Troubleshooting

The payment looks much too large or small: Check that the rate is converted to the payment period and that the term is entered as a number of payments. A five-year monthly loan needs 60 periods, not 5. Also confirm that 6% is stored as 6% or 0.06, not as 6 when intended to mean six percent.

The formula shows an error: Check that every cell reference exists and that the rate, period count, and present value are numeric. A blank or text value where a number is expected can cause an error or an unexpected result. In some regional settings, replace commas in the formula with the list separator used by your Excel installation, often a semicolon.

The answer is negative when you expected a positive amount: This is usually the cash-flow sign convention, not an error. Either interpret the negative result as a payment leaving your account, or enter the loan amount as a negative value if you want PMT to display the payment as positive. Avoid changing signs inconsistently across a model.

The lender’s payment does not match: Compare the interest rate basis, exact payment dates, rounding method, fees, insurance, and any final or balloon balance. PMT assumes equal intervals and a constant rate, while real agreements can use specific day-count rules or other charges. Ask the lender for its amortization schedule if you need to reconcile the figures.

Frequently Asked Questions

What does PMT calculate?
It calculates the regular payment needed for a loan or investment based on a constant rate and equal intervals. Depending on the cash-flow signs you provide, the result may be positive or negative.

What is the difference between rate and nper?
Rate is the interest rate for one payment period; nper is the total number of those periods. For monthly payments, use a monthly rate and count the total months.

Does PMT include interest?
Yes. The payment it calculates reflects the interest rate and repayment period, as well as the principal and any specified future value. It does not automatically add lender fees, insurance, or taxes.

Can PMT calculate a mortgage payment?
It can estimate the fixed principal-and-interest payment when the rate and term are fixed. It will not include property taxes, homeowners insurance, mortgage insurance, or other charges unless you calculate and add them separately.

What does the type argument do?
It sets when payments are due: 0 means at the end of each period, and 1 means at the beginning. If omitted, Excel uses 0.

Can PMT handle a changing interest rate?
Not as one fixed-rate calculation. If the rate changes, calculate each part of the schedule using the applicable rate, or use a payment schedule that reflects the lender’s adjustment rules.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.