Excel’s PMT function calculates the regular payment for a loan with a constant interest rate and equal payments. For a monthly loan, if the annual rate is in B2, the term in years is in B3, the amount borrowed is in B4, and payments per year are in B5, enter =PMT(B2/B5,B3*B5,B4); for a $20,000 loan at 6% over five years with monthly payments, the result is about -$386.66, with the minus sign showing money paid out.
What PMT calculates
PMT stands for payment. It solves for the fixed amount that, paid at regular intervals, repays a present loan balance over a specified number of periods at a specified interest rate. It is useful for estimating principal-and-interest payments on an amortizing loan, such as a standard fixed-rate car loan or mortgage, provided the rate and payment schedule stay constant.
The function does not determine whether a loan is affordable or whether a lender will offer those terms. It also does not automatically include taxes, insurance, lender fees, or other charges. Treat the result as a calculation based on the inputs, then compare it with the payment schedule and disclosures from the lender. For real borrowing decisions, check the actual terms with the lender or a qualified professional; this example is illustrative, not financial, tax, investment, or legal advice.
PMT syntax and its inputs
PMT has five arguments: rate, nper, pv, optional fv, and optional type. Rate is the interest rate for one payment period, nper is the total number of payment periods, and pv is the present value of the loan. The optional fv is the balance you want remaining after the final payment; use zero when the loan should be fully paid off. Type indicates when payments fall: 0 means at the end of each period, while 1 means at the beginning. If omitted, both optional arguments default to zero.
With the annual rate in B2, years in B3, borrowed amount in B4, payments per year in B5, a desired ending balance in B6, and payment type in B7, the full argument pattern is =PMT(B2/B5,B3*B5,B4,B6,B7). For the usual end-of-month payment and a fully repaid loan, B6 and B7 would both contain 0. The optional inputs are useful for special arrangements, but most basic loan estimates need only rate, nper, and pv.
PMT follows a cash-flow sign convention: amounts you pay out and amounts you receive have opposite signs. A loan amount entered as a positive present value therefore produces a negative payment. That is not an error; it indicates an outgoing payment. If a report needs a positive payment figure, you can negate the function result or enter the borrowed amount as a negative value, while keeping the signs consistent.
Worked monthly loan example
Suppose a borrower takes out $20,000 at a fixed annual rate of 6%, makes monthly payments, and repays the loan over five years. Use the annual rate as 6%, not 6, and convert both the rate and term to monthly units. With the values in B2:B5 as described above—6%, 5, $20,000, and 12—enter =PMT(B2/B5,B3*B5,B4). Excel returns approximately -$386.66, so the scheduled payment is about $386.66 per month.
There are 60 payments because five years multiplied by 12 payments per year equals 60. The total of the displayed rounded payment multiplied by 60 is $23,199.60; the total based on Excel’s unrounded payment is slightly different because Excel retains more precision than the two decimals shown. The amount above the $20,000 principal is roughly $3,199 in interest over the full term, assuming the rate and schedule remain unchanged and ignoring additional charges.
If a worksheet should show the payment as a positive number, with the same inputs in B2:B5, use =-PMT(B2/B5,B3*B5,B4). It returns approximately $386.66. This is only a display/sign choice: it does not change the cash flows or the underlying loan math. Format the result as Currency or Accounting if you want a dollar sign and two decimal places.
Match the interest rate to the payment period
The rate and number of periods must use the same time unit. For monthly payments, supply a monthly rate and the number of months; for quarterly payments, supply a quarterly rate and the number of quarters. A frequent mistake is entering a 6% annual rate directly as the rate argument while also asking for 60 monthly payments. That treats 6% as the rate every month and produces a wildly inflated payment.
For the common case where a lender quotes a nominal annual rate and calculates monthly interest as one-twelfth of that rate, divide the annual rate by 12. If payments occur quarterly, divide by four and multiply the term in years by four. Do not assume every rate quotation uses that convention: use the periodic rate or calculation method specified in the loan documents. If the quoted figure is an effective annual rate and you need an equivalent monthly rate, the conversion is different.
For an effective annual rate in B2 and the number of periods per year in B5, an equivalent rate per period is =(1+B2)^(1/B5)-1. For example, an effective annual rate of 6% converted to 12 equal periods is about 0.4868% per month, rather than exactly 0.5%. Use this conversion only when it matches how the quoted rate is defined; a lender’s contractual periodic rate takes priority for reproducing its payment.
Payment timing: end or beginning of period
Most loan installments are due at the end of a payment period, so type is normally 0 or left blank. A payment due at the end of January, for example, follows a period during which interest has accrued. Type 1 models payments at the beginning of each period, a timing more commonly associated with certain rent or annuity arrangements than ordinary loan installments. Because beginning-of-period payments reduce the balance sooner, they generally produce a different payment amount than end-of-period payments for otherwise identical inputs.
With annual rate in B2, years in B3, principal in B4, and 12 payments per year in B5, this formula sets type to 1 and the ending balance to zero: =PMT(B2/B5,B3*B5,B4,0,1). Do not choose type 1 simply to make a payment estimate look smaller or larger. Use the timing stated in the contract, and note that the timing convention can affect the result even when the rate, principal, and term have not changed.
What the PMT result includes—and leaves out
PMT calculates a level payment that accounts for the principal and interest represented by its inputs. It does not add property taxes, homeowners insurance, mortgage insurance, association dues, maintenance, or lender charges. A lender’s quoted monthly housing payment may combine several of these items, so it can be higher than the PMT result. Likewise, a loan with an origination fee or financed add-on may have a different amount financed from the advertised purchase price.
For a simple fixed-rate loan with no extra charges, multiplying the periodic payment by the number of periods gives the total of scheduled payments. Subtracting the principal gives an estimate of total interest. With a positive payment in B8, principal in B4, years in B3, and payments per year in B5, the total scheduled payments are =B8*B3*B5, or about $23,199 for the example when calculated from the unrounded payment. With total payments in B9 and principal in B4, estimated interest is =B9-B4, about $3,199. These totals rely on the same assumptions as PMT and should not be read as a lender payoff quote.
Check a payment against an amortization schedule
PMT gives the same scheduled payment for each period, but the split between interest and principal changes over time. Early payments on a typical amortizing loan contain more interest because the balance is larger. As principal is repaid, the interest portion generally falls and the principal portion rises. The payment can remain level even though its components change.
To inspect that split, Excel includes IPMT for the interest portion of a payment and PPMT for its principal portion. These functions use a period number along with the rate, number of periods, and present value. A schedule can also be built with a running balance: start with the loan principal, calculate each period’s interest from the opening balance and periodic rate, then subtract the principal repaid. Check rounding carefully; if each period’s payment or interest is rounded to cents inside the calculation, a small residual balance can remain at the end. A lender may adjust the final installment to clear it.
For a quick reasonableness check, compare the PMT estimate with the lender’s stated payment using the same principal, periodic rate, number of payments, and payment timing. If the figures differ, look for financed fees, a different day-count or rate convention, a balloon balance, a variable rate, or a first payment date that creates a nonstandard initial period. PMT assumes regular, equally spaced periods and a constant rate; it is not designed to reproduce every real-world loan schedule automatically.
Common PMT errors and how to fix them
- Using the annual rate as a monthly rate: Divide a nominal annual rate by the number of payments per year when that matches the lender’s convention.
- Using years as the number of monthly periods: Multiply years by 12 for monthly payments, or use the matching count for another schedule.
- Getting a negative answer: This is PMT’s cash-flow sign convention when pv is positive. Negate the result for a positive display if useful.
- Expecting taxes or fees in the result: PMT only uses the values provided. Add separate cost estimates if you need a broader budget, and label them clearly.
- Using PMT for a changing-rate loan: A single PMT result assumes a constant rate and equal periods. Recalculate separate scenarios if the rate changes, and follow the loan terms for the actual payment.
- Confusing a payment estimate with a payoff amount: A payoff quote can include accrued interest, fees, or other adjustments. Request the current figure from the lender.
Excel for Microsoft 365 on Windows uses commas between formula arguments in the examples here. Excel for Mac and Excel for the web support PMT as well, though regional settings can require semicolons instead of commas. If Excel rejects a formula despite correct cell references, check the list separator configured for your region. The function itself and the meaning of its arguments are the same.
Frequently Asked Questions
Does PMT include interest? Yes. For a constant rate and regular payments, PMT calculates the level payment needed to cover interest and repay the specified balance over the term. It does not include costs that you have not entered, such as taxes, insurance, or fees.
Why does Excel show a negative loan payment? Excel uses signs to distinguish cash received from cash paid. If the loan amount is entered as positive money received, the repayment is returned as negative money paid out. Use a leading minus sign before PMT when you need a positive display.
How do I calculate a monthly payment from an annual interest rate? Convert the rate to the monthly rate used by the loan—often the nominal annual rate divided by 12—and set the number of periods to years multiplied by 12. Confirm the lender’s rate convention before relying on the estimate.
Can PMT calculate a variable-rate loan payment? Not as one fixed payment for the whole term. PMT assumes a constant rate. You can calculate a scenario for each rate period, but a changing-rate loan’s actual payment depends on its contract and future rate adjustments.
Can PMT show how much of each payment is principal? PMT returns the total level payment, not its period-by-period split. Use Excel’s IPMT and PPMT functions or an amortization schedule to examine interest and principal separately.
Related
- Excel Very Slow Loading Data? Causes and Fixes
- How to Calculate VaR 99 in Excel
- Excel Vertical Text: How to Rotate Text in Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Minus Days: Formulas and ExamplesTo subtract calendar days from a date in Excel, subtract a number from the date cell: if the…
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
- Excel SUBSTITUTE Shortcut: Function and Replace KeysExcel has no dedicated keyboard shortcut for the SUBSTITUTE function. To replace text in cells with a formula,…
- VLOOKUP Excel Using Two Spreadsheets: Steps and TipsYes. VLOOKUP can find a value in one Excel workbook and return related information from another; the workbooks…
- XLOOKUP Syntax Examples for ExcelFor an exact lookup, with the value to find in E2, lookup keys in A2:A100, and results in…
- Sort Excel by Highlighted Rows: Step-by-StepYes. In Excel for Microsoft 365 on Windows, select the entire table, open Data > Sort, set Sort…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.