PMT in Excel: Calculate Loan Payments Step by Step

Formulas & functions · Updated November 20, 2026

To calculate a fixed loan payment in Excel, use the PMT function: enter the interest rate per payment period, the total number of payments, and the loan amount. For example, with a $20,000 loan at 6% annual interest repaid monthly over five years, a properly set up PMT formula returns a payment of about $386.66 per month.

What PMT does in Excel

PMT calculates the regular payment needed to pay off a loan or reach a savings target when the interest rate and payment amount stay constant. It is useful for estimating payments on a fixed-rate loan, comparing repayment periods, or working out a regular contribution toward a future value. Excel’s PMT function is available in Microsoft 365 for Windows, and the function works in Excel for Mac and Excel for the web as well.

For a standard fully repaid loan, each payment covers interest for that period and pays down some principal. Early payments usually contain a larger interest share because the outstanding balance is higher; later payments pay down more principal. PMT calculates the same total payment each period, not the changing split between interest and principal.

The result is a calculation based on the inputs, not a lender’s quoted offer. It does not automatically include fees, insurance, taxes, variable-rate changes, or other charges. Check the lender’s actual terms before relying on an estimate.

PMT arguments and payment timing

PMT accepts a rate, number of periods, present value, and two optional arguments. In practical terms, those inputs are:

  • Rate: the interest rate for one payment period, not necessarily the quoted annual rate.
  • Nper: the total number of payment periods over the loan or savings term.
  • Pv: the present value, such as the amount borrowed today.
  • Fv: the desired balance after the final payment. It is normally zero for a loan paid off in full.
  • Type: when payments are due. Use 0, or omit this argument, for payments at the end of each period; use 1 for payments at the beginning.

For instance, monthly payments over five years mean 60 periods. A quoted annual rate of 6% is commonly converted to a monthly rate by dividing by 12, giving 0.5% per month. That conversion assumes the rate and compounding convention are suitable for the loan being modeled. A lender may use a different convention, so match its terms where possible.

Set up a monthly loan example

Put the inputs in a small worksheet so the calculation is easy to audit and adjust. In this example, enter the annual interest rate in B2, the term in years in B3, the amount borrowed in B4, and payments per year in B5:

  • B2: 6%
  • B3: 5
  • B4: 20000
  • B5: 12

With those values in B2:B5, and the annual rate in B2, years in B3, principal in B4, and payments per year in B5, enter this in B6:

=PMT(B2/B5,B3*B5,B4)

Excel returns approximately -$386.66. The negative sign is expected: Excel treats the $20,000 received as a positive cash flow and the payments made as negative cash flows. The formula divides the annual rate by 12 and multiplies five years by 12, so it calculates 60 monthly payments.

To display the result as a positive payment for a presentation or budget, with the same inputs in B2:B5, enter this in B6 instead:

=-PMT(B2/B5,B3*B5,B4)

This version returns approximately $386.66. Applying Currency or Accounting number formatting can make the amount easier to read; formatting changes how the result appears, not the underlying calculation. On Windows, use the Home tab’s Number group to choose a number format. The same function and sign convention apply in Mac and web versions, though ribbon layout can vary.

Understand the payment and total cost

The monthly payment is not the same as the total repaid. Multiply the payment by the number of periods to estimate all scheduled payments, then compare that amount with the amount borrowed to estimate interest under the assumptions in the sheet. With the input layout above and the negative PMT result in B6, enter this in B7 to calculate total payments:

=-B6*B3*B5

The result is approximately $23,199.40. With the loan amount in B4 and total payments in B7, enter this in B8 to estimate total interest:

=B7-B4

The result is approximately $3,199.40. These figures are estimates from the unrounded payment value in B6; the displayed payment may be rounded to cents, so multiplying the displayed $386.66 by 60 can differ by a few cents. Actual lender schedules may also differ because of fees, payment dates, rounding, or rate conventions.

Match the rate and number of periods

The rate and period count must use the same time unit. If payments are monthly, use a monthly rate and a count of monthly payments. For quarterly payments, use a quarterly rate and the number of quarters. Combining an annual rate with a monthly period count without converting the rate can produce a dramatically incorrect payment.

For a loan repaid annually over five years, the annual rate can be used directly and the number of periods is five. With an annual rate in B2, years in B3, and principal in B4, enter this formula in B6:

=PMT(B2,B3,B4)

If the interest rate is already expressed per month, do not divide it by 12 again. Likewise, if the quoted annual percentage rate includes fees or is defined using a particular compounding method, a simple division may not reproduce the lender’s exact payment calculation. Confirm whether the quoted rate is the appropriate rate for the payment schedule.

Use FV and Type when the defaults do not fit

PMT assumes a zero balance at the end and payments at period-end when its optional arguments are omitted. A loan with a balloon balance, or a savings plan with a specific target, may require a nonzero future value. A positive future value represents an amount remaining or desired at the end, interpreted using Excel’s cash-flow sign convention. Be deliberate about the signs of present and future values so that money received and money paid are represented consistently.

The Type argument changes when each payment happens. For an ordinary loan with payments due at the end of each month, leave Type out or set it to 0. For payments due at the beginning of each month, set it to 1. Beginning-of-period payments generally reduce the amount of interest accruing after each payment, so the result can differ. Use the timing specified by the agreement rather than choosing a setting only because it produces a preferred number.

With the annual rate in B2, years in B3, principal in B4, and payments per year in B5, a beginning-of-month version of the earlier loan calculation is:

=PMT(B2/B5,B3*B5,B4,0,1)

Common PMT errors and checks

Most unexpected results come from mismatched inputs or cash-flow signs rather than a problem with Excel. Before changing the formula, check these common issues:

  • Annual rate used for monthly payments: Convert the annual rate to a monthly rate when the terms support that conversion.
  • Wrong period count: Five years of monthly payments is 60 periods, not 5. For a term in years, multiply by payments per year.
  • Unexpected negative answer: A negative payment is normal when the borrowed amount is entered as a positive present value. Negate PMT if a positive display is more convenient.
  • Text in an input cell: Ensure the rate, term, and principal cells contain numeric values. A rate typed as text or a blank referenced cell can lead to errors or misleading results.
  • Zero or invalid periods: Nper must represent a valid positive number of periods, and the rate must be suitable for the calculation.
  • Difference from a lender quote: Check fees, rounding, the first payment date, variable rates, and the lender’s interest calculation method.

Excel uses commas between function arguments in the formulas shown here. Some regional settings use semicolons as separators, so Excel may expect that local syntax when entering a formula. This is a regional settings difference, not a different PMT calculation.

PMT compared with IPMT and PPMT

PMT gives the total fixed payment. If you want to examine how that payment is divided, Excel also provides IPMT for the interest portion of a particular period and PPMT for the principal portion. For a standard fixed-rate loan, the interest portion generally declines over time while the principal portion rises, and together they make up the scheduled payment.

Those functions need the period number as well as the rate, total periods, and present value, so they are useful when building an amortization table rather than when you only need the regular payment. They do not make a loan’s fees or rate changes disappear; their output still depends on the assumptions entered. For a simple payment estimate, PMT is usually the clearest starting point.

When PMT is not enough

PMT is designed for regular payments at consistent intervals, a constant rate, and a consistent payment amount. It is not a full loan schedule and does not model irregular payment dates, extra principal payments, payment holidays, or a changing interest rate. If any of those apply, a single PMT result may be a rough comparison rather than a reliable schedule.

For a more detailed estimate, build a period-by-period table that tracks the opening balance, interest charged, payment, principal reduction, and closing balance. Use the contract’s timing and interest rules, and check the resulting schedule against the lender’s disclosures. For decisions involving a real loan or other financial obligations, verify terms with the lender or a qualified professional; this spreadsheet example is educational, not financial advice.

Frequently Asked Questions

Why does PMT in Excel show a negative number?
Excel uses cash-flow signs. If the loan amount is entered as money received, the payments are money going out and appear negative. Negate the PMT result if you want the displayed payment to be positive.

Should I use the APR or monthly interest rate in PMT?
Use a rate that matches the payment period and the loan’s stated calculation method. For a simple monthly estimate from a nominal annual rate, dividing by 12 is common, but lender conventions and fees can make the result differ from the quoted payment.

What does the Type argument mean in PMT?
Type specifies when payments occur. Use 0 or omit it for payments at the end of each period; use 1 for payments at the beginning. Follow the timing in the loan or savings arrangement.

Does PMT include taxes and fees?
No. PMT calculates a payment from the rate, period count, present value, future value, and timing you supply. Add relevant charges separately if you are making a broader estimate, and confirm actual amounts with the lender.

Can PMT calculate a savings contribution?
Yes. The function can calculate a regular contribution toward a future value when the rate, contribution intervals, and target are consistent. Use signs that reflect contributions as cash paid out and the target balance as cash received, and remember that an assumed return is not guaranteed.

Related

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.