Excel PMT Function: Calculate Loan Payments

To calculate a fixed loan payment in Excel, divide the annual interest rate by the number of payments per year and use that periodic rate, the total number of payments, and the loan amount in PMT. With the annual rate in B2, monthly payment count in B3, and loan principal in B4, enter =PMT(B2/12,B3,B4). For a $20,000 loan at 6% annual interest over 48 months, the result is about -$469.70 per month; the negative sign represents money paid out.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. The PMT function is also available in Excel for Mac and Excel for the web; the basic formula works the same way.
The amount borrowed, the interest rate per payment period, and the total number of payments. For a typical fixed-rate loan, these figures come from the lender’s terms.
A blank worksheet or an existing workbook where you can enter the loan details and formula. Keep the rate, term, and payment frequency clear so you can check your assumptions.

Step-by-Step

Choose the payment schedule. Decide whether the payment is monthly, quarterly, or another frequency. PMT calculates one regular payment at a time, so the interest rate and number of periods must use the same frequency. For monthly payments, use a monthly rate and a count of months. A 4-year monthly loan has 48 periods, not four.
Set up labeled inputs. In a new worksheet, put Annual interest rate in A2 and enter 6% in B2. Put Loan term in years in A3 and enter 4 in B3. Put Payments per year in A4 and enter 12 in B4. Put Loan amount in A5 and enter 20000 in B5. Formatting B2 as a percentage and B5 as currency makes the values easier to read. Enter 6% as a percentage rather than the number 6; the distinction affects the calculation.
Convert the loan term into payment periods. PMT needs the total number of payments, not the number of years. With the term in years in B3 and payments per year in B4, the total periods can be calculated in B6. For the example, that gives 4 × 12 = 48. You can type 48 directly instead, but a calculation makes the schedule easier to update if the term or frequency changes.
Enter the PMT formula. With the annual interest rate in B2, years in B3, payments per year in B4, and loan amount in B5, enter =PMT(B2/B4,B3*B4,B5) in B7. Press Enter. Excel divides the annual rate into a rate per payment period and multiplies the term by the payment frequency. With the example inputs, B7 returns approximately -$469.70. The result assumes a fixed rate and payments made at the end of each period.
Make the payment display positive if that is clearer. Excel treats money you borrow as a cash inflow and payments as cash outflows, so the standard result is negative when the loan amount is positive. If you prefer a positive payment amount for a simple budget display, use a leading minus sign. With the same inputs in B2, B3, B4, and B5, enter =-PMT(B2/B4,B3*B4,B5) in B7. It returns about $469.70. This changes only how the result is displayed; it does not change the payment calculation.
Check the timing convention. By default, PMT assumes each payment is made at the end of the period, which is the usual assumption for standard loan calculations. The optional final argument, called type, can be 0 for end-of-period payments or 1 for beginning-of-period payments. For a lease or other arrangement where payments are due at the start of each period, include 1 as the fifth argument. Confirm the contract’s timing rather than changing this setting just to make a result look more favorable.
Format and label the answer. Select the result cell, then use Home > Number to choose Currency or Accounting and set the desired decimal places. Add a label such as Monthly payment, and make the payment frequency visible nearby. Formatting changes how the value looks, not the underlying result. If you copy the formula to another part of the sheet, check that the references still point to the intended input cells.
Review what the payment includes. PMT calculates a regular principal-and-interest payment based on the figures supplied. It does not automatically add lender fees, insurance, property taxes, or other charges. Compare the spreadsheet result with the lender’s payment schedule and the full terms before relying on it. For an actual loan, verify rates, compounding, fees, and payment timing with the lender or a qualified professional; this example is illustrative, not financial advice.

Common Mistakes

Using the annual rate as if it were monthly. If B2 contains 6%, typing it directly as PMT’s rate treats 6% as the rate for every payment period. For monthly payments, divide the annual rate by 12. For quarterly payments, divide by 4. If the value is entered as 6 rather than 6%, dividing by 12 is still wrong because Excel reads 6 as 600%; enter 6% or use a correctly converted decimal.

Mixing years and months. A monthly loan needs a period count in months. Pairing a monthly rate with a term of four periods calculates a four-month loan, not a four-year loan. Conversely, dividing the rate by 12 while entering 4 as nper gives a mismatched calculation. Convert both inputs consistently.

Being surprised by a negative answer. The minus sign follows Excel’s cash-flow convention; it is not an error. If you need a positive amount for presentation, use the negative of PMT, or enter the loan amount as a negative value and keep track of the sign convention consistently. Avoid changing signs arbitrarily across inputs because that can make the result confusing.

Assuming the result is the total cost of borrowing. The payment amount alone does not show how much interest you will pay over the whole loan, and it excludes charges not included in the principal or rate. A PMT estimate is not the same as a lender’s complete disclosure or payoff figure.

Tips

Keep inputs in separate, labeled cells and refer to those cells in the formula. That is easier to audit than embedding numbers in the formula, and it lets you change one assumption without rebuilding the calculation.
Use the actual payment frequency. For biweekly payments, do not automatically assume that every month has exactly two payments; use the lender’s defined schedule and periodic rate. Payment calendars and interest conventions can differ from a simple monthly calculation.
Use Excel’s Formula Bar to inspect a selected formula, especially if a result seems unexpectedly large or small. Check that the percentage cell is genuinely formatted and stored as a percentage and that the period count matches the schedule.
Consider testing a second term or rate in separate input cells to see how the payment changes. Keep the original assumptions intact, and label each scenario so you do not mistake an estimate for the lender’s quoted payment.

Troubleshooting

The payment is much too high or too low: Check whether the rate was entered as 6%, 0.06, or 6, and whether the formula divides by the correct number of payments per year. Then confirm that the term is expressed as a total number of periods. A common mismatch is using 48 months with an annual rate that has not been converted to a monthly rate.

Excel shows #NUM!: Check that the number of payment periods is greater than zero and that the rate and other inputs form a valid calculation. A zero or negative period count is not a usable loan term. Also verify that the interest rate is not an unintended extreme value caused by entering a whole-number percent.

Excel shows #VALUE!: One or more referenced cells may contain text instead of a numeric value, such as a typed currency symbol or a note in the amount cell. Enter numeric values and apply Currency or Percentage formatting rather than typing symbols into the cell. Check for spaces or text copied in with the inputs.

The displayed answer is rounded or looks different from the lender’s quote: Increase the displayed decimal places to inspect the full calculated value, and verify whether the quoted payment includes fees, insurance, taxes, a different first-payment date, or a different rate convention. Formatting cannot correct different assumptions. Ask the lender to confirm the exact figures and schedule.

Frequently Asked Questions

What does PMT calculate?
PMT returns the regular payment for a loan or other arrangement with constant payments and a constant interest rate. It can also be used for savings calculations when the cash-flow signs and inputs are set appropriately, but it does not model changing rates or irregular payment amounts.

Why does PMT return a negative number?
Excel represents money received and money paid in opposite directions. If the loan principal is entered as a positive amount received, the regular payments are returned as a negative outflow. Use the negative of the function result if a positive display is more convenient.

Can PMT calculate a payment when the interest rate is zero?
Yes. With a zero rate and no additional future balance, a loan is divided evenly across the periods. For example, a $12,000 amount over 12 monthly payments is $1,000 per month. Make sure the period count and signs still match your setup.

What does the type argument do?
It sets when each payment occurs. Type 0, which is the default when omitted, means payments occur at the end of each period. Type 1 means payments occur at the beginning. Use the option that matches the agreement or schedule being modeled.

Does PMT include fees, taxes, or insurance?
No, not unless those costs are incorporated into the amount being financed or modeled separately. PMT uses the values supplied; it cannot identify extra costs from a lender’s terms on its own. Check the loan documents and ask the lender what its quoted payment includes.

Can I use the PMT function in Excel for Mac or Excel for the web?
Yes. PMT is available in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web, and the formula syntax shown here is the same. Ribbon layout can vary slightly by platform, but you can enter the formula in a worksheet cell in each version.

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.