For a $20,000 loan at 6% annual interest over 36 monthly payments, enter the principal in B1, annual rate in B2, and number of payments in B3, then use =PMT(B2/12,B3,-B1); the result is about $608.44 per month. PMT calculates a regular payment for a loan or savings plan when the interest rate and payment amount stay constant.
A Worked PMT Example
Start with a small worksheet. Put these values in the indicated cells:
- B1: 20000, the amount borrowed today
- B2: 6%, the annual interest rate
- B3: 36, the total number of monthly payments
With those inputs in B1:B3, enter the PMT formula in B4: =PMT(B2/12,B3,-B1). Excel returns approximately $608.44. Format B1 and B4 as Currency if you want the amounts displayed with a dollar sign and two decimal places. The formula uses the underlying values, not the way they are formatted on screen.
This example assumes a fixed annual rate of 6%, divided into 12 monthly periods, with one payment due at the end of each month. It is an illustration of the function, not a lender quote. Real loan payments can also reflect fees, insurance, rate changes, different day-count conventions, or other contract terms.
What PMT Does and What Its Arguments Mean
PMT is a financial function that returns the payment for a loan or investment based on periodic, constant payments and a constant interest rate. Its arguments are the rate per payment period, the total number of payment periods, and the present value. You can also provide an optional future value and an optional payment-timing setting.
- Rate: the interest rate for one period, not automatically the annual rate. For monthly payments and a nominal annual rate, the example divides the annual rate by 12.
- Nper: the total number of periods. Three years of monthly payments means 3 times 12, or 36 periods.
- Pv: the present value, such as the loan amount received today. Excel uses cash-flow signs, so the borrowed amount is negative in the example to make the payment appear positive.
- Fv: an optional balance remaining after the final payment. If omitted, Excel assumes zero.
- Type: an optional timing choice. Zero or omission means payments at period-end; 1 means payments at period-beginning.
For a standard fully repaid loan, leaving future value out is usually appropriate because the goal is to bring the balance to zero. If a loan is structured with a balloon balance or a savings target has a desired ending value, the future-value argument may be relevant.
Build the Worksheet and Check Total Cost
Label the cells so the model is understandable later: A1 could say Principal, A2 Annual rate, A3 Number of monthly payments, and A4 Monthly payment. Put the inputs in B1:B3 and the PMT result in B4. Referencing input cells makes it easier to compare different rates or terms: change B2 or B3 and Excel recalculates the payment. Keep the rate and period units consistent; monthly payments need a monthly rate and a count of months.
To estimate the total of all payments, with B4 holding the unrounded PMT result and B3 holding the 36-payment term, enter =B4*B3 in another cell. It returns about $21,903.86. That total includes repayment of the original $20,000 principal as well as interest.
To estimate the interest paid over the full term, with B1 holding the $20,000 principal, B3 holding the number of payments, and B4 holding the payment, enter =B4*B3-B1. The result is about $1,903.86. These totals assume all payments are made as modeled and do not include separate charges. If you round every payment manually to cents or the lender adjusts the final payment, the actual total can differ slightly.
Why the Result Is Positive or Negative
Excel treats money received and money paid as opposite cash flows. In the example, the borrower receives $20,000, represented as a negative present value, and pays money out over time, so PMT returns a positive payment. If you enter the principal as positive instead, PMT normally returns a negative number. That is not an error; it is Excel preserving the direction of the cash flows.
Choose signs based on how you want to read the result. For a consumer-friendly payment estimate, a negative loan principal is a convenient way to get a positive payment. For a financial model that tracks all cash inflows and outflows, keeping the signs aligned with the model’s convention may be more useful. Avoid changing signs just to make a result look right without checking what each value represents.
Payments at the Beginning Versus the End
Most ordinary loan payments are due at the end of a payment period, which is PMT’s default. Some arrangements, such as rent or certain annuity payments, are due at the beginning. In that case, the payment timing changes the present value because each payment arrives one period earlier.
Using the same layout—principal in B1, annual rate in B2, and 36 monthly periods in B3—enter =PMT(B2/12,B3,-B1,0,1) to set the optional future value to zero and payment type to 1. The result is approximately $605.41 per period. It is lower than the end-of-period payment because the payments are made earlier. Confirm the actual due-date convention before choosing a timing setting; changing this argument does not account for fees or other loan terms.
Assumptions Behind a PMT Estimate
PMT is useful precisely because it simplifies a stream of payments, but that simplicity means its assumptions matter. The function models equal payments, equal-length periods, and a constant rate. A variable-rate loan, irregular payment dates, skipped payments, or a changing payment amount will not be represented accurately by one PMT calculation.
It also does not automatically add origination charges, taxes, insurance, service fees, or penalties. A quoted annual percentage rate may incorporate costs differently from the nominal interest rate used in a payment calculation. Check the lender’s stated rate, compounding convention, payment frequency, and fee treatment before comparing a spreadsheet estimate with an official payment schedule. For a real borrowing decision, check the contract with the lender or consult a qualified professional; this example is educational, not financial advice.
PMT Versus an Amortization Schedule
PMT answers a focused question: what is the constant payment under these assumptions? It does not show how much of each payment reduces principal, how much goes to interest, or the balance remaining after a particular month. An amortization schedule is more informative when you need those details.
A schedule typically lists each period, the opening balance, interest for that period, principal repaid, payment, and closing balance. For a fixed-rate loan, interest in a period is generally based on the current balance and that period’s rate, while principal is the payment less interest. The balance then falls over time. A lender’s schedule may still differ from a simple spreadsheet because of rounding rules, fees, payment dates, and contract-specific calculations.
Use PMT for a quick payment estimate or to compare terms under consistent assumptions. Use a detailed schedule when you want to inspect the changing balance, model extra repayments, or understand the composition of each payment. If payments are irregular or the rate changes, a schedule that reflects those changes is more suitable than a single constant-payment result.
Common PMT Errors and How to Fix Them
- Annual rate entered as a monthly rate: if payments are monthly, convert the annual rate to a monthly rate as in the example. Entering 6% as the rate for each month describes a very different, much more expensive loan.
- Years used instead of payment count: with monthly payments over three years, use 36 periods, not 3. For quarterly payments, use the number of quarters.
- Unexpected negative payment: check the signs of the present value and other cash flows. Opposite signs for money received and money paid usually make the output easier to interpret.
- Payment seems too small or too large: verify the period rate, total periods, and whether the payment is at the beginning or end of a period. Also check whether a final balance was intended.
- Result differs from a lender’s figure: compare the inputs and assumptions, including fees, exact rate, payment dates, and rounding. A displayed two-decimal payment may conceal additional precision in the cell.
In Excel for Microsoft 365 on Windows, type the formula into a cell and press Enter. The PMT function is also available in Excel for Mac and Excel for the web, though ribbon layout can vary. In some regional settings Excel uses semicolons rather than commas between arguments, and a local currency or decimal format can change how values appear. If Excel rejects an otherwise valid formula, check the list separator used by your regional settings.
Frequently Asked Questions
What does PMT stand for in Excel?
PMT refers to payment. The function calculates a constant periodic payment for a loan or investment using a constant interest rate and a specified number of periods.
How do I calculate a monthly loan payment in Excel?
Enter the annual rate, loan amount, and number of monthly payments in cells, convert the annual rate to a monthly rate, and use PMT. The example above estimates $608.44 for $20,000 at 6% over 36 months, before fees.
Why does PMT show a negative number?
Excel uses positive and negative signs to represent cash-flow direction. If the loan amount is entered as a positive amount received, the payment may appear negative. Reverse the present-value sign if you want the payment displayed as positive, while keeping the cash-flow meaning clear.
Does PMT include interest?
Yes. The payment is calculated using the interest rate and principal; it represents the combined periodic payment, not principal alone. PMT does not automatically include separate charges such as insurance or fees.
Can PMT calculate a payment when the interest rate is zero?
Yes. A zero rate is valid: the payment is the amount being financed divided across the specified number of periods, with the sign determined by Excel’s cash-flow convention. Make sure the period count and payment timing still match the situation.
Can PMT give the exact payment on my loan?
It can match a loan only when the spreadsheet inputs and assumptions match the contract. Confirm the rate convention, payment schedule, fees, and rounding with the lender, especially for variable rates or irregular payments.
Related
- Daily Employee Schedule Template Excel: Setup & Tips
- Daily Schedule Excel Template: Plan Your Day in Excel
- Daily Cash Flow Template Excel: How to Build and Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel ROW Function Examples for Numbering Rows and MoreExcel’s ROW function returns the worksheet row number for a reference, or the row containing the formula if…
- XLOOKUP in Excel: Practical Lookup Examples, Tips, and UsesExamples of XLOOKUP in Excel include finding a customer name from an ID, returning the latest matching price,…
- Round Excel Cell Values: Formulas and Display OptionsTo round a number in an Excel cell and change the value used in calculations, use the ROUND…
- Excel Weighted Average Using SUMPRODUCT: Formula and ExamplesTo calculate a weighted average in Excel, multiply each value by its weight, add those products, then divide…
- String length function in JS: .length explainedThe string length function in JavaScript is the .length property, used as myString.length. It returns the number of…
- Count Days in Excel: Formulas for Dates and WorkdaysTo count calendar days between two dates in Excel, put the start date in A2 and the end…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.