In Excel, the PMT function syntax is =PMT(rate, nper, pv, fv, type). The first three arguments are required; fv and type are optional and default to 0, so a basic loan formula can be as short as =PMT(rate, nper, pv).
What each PMT argument means
PMT calculates a fixed payment for a loan or annuity using a constant interest rate and a fixed number of payment periods. Its arguments describe the interest rate for one period, the total number of periods, and the present value. Enter each value in a consistent unit: a monthly rate must go with a number of monthly periods, for example.
- rate is the interest rate per payment period, not automatically the annual rate. For monthly payments, divide an annual rate by 12 if it is quoted as a nominal annual rate.
- nper is the total number of payment periods. A five-year loan paid monthly has 60 periods.
- pv is the present value: the amount borrowed or the current value of an annuity. Its sign affects whether Excel returns a positive or negative payment.
- fv is the balance you want remaining after the final payment. If omitted, Excel assumes zero, as with a loan paid off in full.
- type indicates when payments are due. Use 0, or omit it, for payments at the end of each period; use 1 for payments at the beginning.
In the syntax, the argument names are placeholders, not literal text to type unless your workbook has defined names with those names. In a worksheet formula, replace them with numbers, cell references, or valid named ranges. The square-bracket notation sometimes used in documentation simply means an argument is optional; do not type square brackets into the formula.
How PMT calculates a payment
PMT spreads a present value across repeated periods while accounting for interest charged during each period. For a standard fully amortizing loan, each regular payment covers that period’s interest and reduces the amount still owed. Early payments usually contain a larger interest share; later payments usually reduce more principal. The payment remains level when the rate and payment schedule stay fixed.
The function assumes regular, equal intervals and a constant rate. It does not create an amortization table, determine the dates on which payments are due, or account for a rate that changes over time. It returns the scheduled periodic payment based on the inputs you supply. A lender’s quoted payment may differ if the real agreement includes fees, insurance, taxes, irregular dates, or other charges.
Worked example: a monthly loan
Suppose a borrower takes out $20,000 at a nominal annual rate of 6%, makes monthly payments, and repays the balance over five years. Put the annual rate 6% in B2, the term in years (5) in B3, and the loan amount (20000) in B4. The monthly rate is the annual rate divided by 12, and the total number of payments is the term in years multiplied by 12. With those inputs, enter:
=PMT(B2/12,B3*12,B4)
Excel returns approximately -$386.66 per month. The negative sign follows Excel’s cash-flow convention: a positive loan amount received is balanced by payments going out. The formula uses the default future value of zero and the default end-of-period timing.
If you prefer to display the borrower’s payment as a positive amount, make the present value negative instead. With the same values still in B2:B4, use:
=PMT(B2/12,B3*12,-B4)
This returns approximately $386.66. Either sign choice can be useful; the important point is to use a consistent cash-flow perspective rather than changing signs just to make one result look right. At the unrounded payment, 60 payments total about $23,199.40, or roughly $3,199.40 above the original principal. Those totals are illustrative and exclude any fees or other costs.
Payment timing: type 0 or type 1
The type argument changes when each payment occurs. Most conventional loan payments are made at the end of a period, so type 0 is the usual setting. An annuity due, such as a payment collected at the start of each period, uses type 1. Because a payment made at the beginning starts reducing the balance sooner, the payment amount for an otherwise identical calculation is generally different.
To compare the beginning-of-month version of the example, keep the annual rate in B2, years in B3, and positive loan principal in B4. Enter:
=PMT(B2/12,B3*12,-B4,0,1)
Excel returns approximately $384.73 per month for these inputs. The fourth argument explicitly sets the desired ending balance to zero, and the fifth sets payment timing to the beginning of the period. If your payment timing is not clear from a quote or contract, check the actual agreement rather than assuming that type 1 applies.
Match the interest rate and number of periods
A frequent source of incorrect answers is mixing annual and monthly units. If payments are monthly, both rate and nper must be monthly: use an appropriate monthly rate and the number of months. For quarterly payments, use a quarterly rate and the number of quarters. For annual payments, use an annual rate and the number of years.
For a nominal annual rate and regular monthly periods, dividing by 12 is a common setup. However, financial products may define rates, compounding, and payment schedules differently. A rate quoted as an effective annual rate is not necessarily converted to a monthly rate in the same way as a nominal annual rate. Use the rate convention in the loan or annuity terms; PMT does not infer it from a percentage format or convert it for you.
Likewise, do not enter the term in years as nper when there are monthly payments. A five-year monthly schedule has 60 payment periods, not 5. If the contract calls for an uneven first period, skipped payments, or changing payment dates, a single PMT calculation may not represent the actual schedule.
Signs, total paid, and what the result excludes
Excel’s financial functions treat money received and money paid as opposite cash flows. If pv is positive, PMT will normally return a negative payment. If pv is negative, the payment will normally be positive. This is not an error; it is a sign convention. Decide whether the formula represents the borrower’s or lender’s perspective, then keep the signs consistent across related calculations.
PMT calculates a periodic payment, not the total cost of borrowing. You can multiply the absolute payment by the number of periods to estimate total scheduled payments, then compare that amount with the principal to estimate interest under the formula’s assumptions. The estimate does not include origination charges, taxes, insurance, late charges, or other costs unless they are explicitly incorporated into the inputs. It is not financial, tax, or legal advice; confirm the real payment schedule and total cost with the lender or a qualified professional.
When a loan has a residual balance or a balloon payment, the ending balance is not zero, so the optional fv argument may matter. Be careful with its sign: it represents a future cash-flow amount, and Excel’s sign convention still applies. Confirm how the balance is described in the agreement and check the result against the lender’s schedule.
Common PMT errors and how to check them
- The payment is far too large or small: Check whether the annual rate was divided by the number of periods and whether
nperis expressed in periods rather than years. - The result is negative: This is usually normal when the present value is positive. Reverse the sign of
pvif a positive payment display better suits your model. - The final balance does not match expectations: Check whether
fvshould be zero or a residual amount, and whether its sign matches the cash-flow perspective. - The result differs slightly from a lender’s quote: The lender may use different rounding, compounding, payment dates, fees, or rate terms. Compare the assumptions, not just the displayed payment.
- Excel displays an error or unexpected value: Check that each referenced cell contains a numeric value, that no required argument is missing, and that your workbook uses the expected list separator. Regional settings can require semicolons instead of commas when entering formulas.
To verify a calculation, write down the rate per payment period, the exact number of payments, the principal, the ending balance, and whether each payment is due at the start or end of a period. These checks catch most setup mistakes before you rely on the returned amount.
PMT compared with related Excel functions
PMT is useful when you want the same payment throughout a fixed-rate schedule. For more detail, Excel’s IPMT function calculates the interest portion for a specified period, while PPMT calculates the principal portion for that period. Together, those functions can help build an amortization schedule, but you must supply the period number and matching rate and term inputs.
Other financial functions answer different questions. NPER estimates the number of periods needed for a payment, rate, and present value; RATE estimates the rate per period under specified cash-flow assumptions; and PV calculates present value. They are alternatives when the unknown is not the regular payment. None of these functions automatically accounts for a contract’s fees or special payment rules.
Frequently Asked Questions
What is the simplest PMT formula in Excel?
Use =PMT(rate,nper,pv) when the rate, number of periods, and present value are known and you want the standard end-of-period payment with a zero ending balance. Replace the placeholders with cell references or values.
Does PMT calculate monthly payments automatically?
No. PMT uses the rate and period count you provide. For a monthly schedule, supply a monthly rate and the total number of months; it does not infer payment frequency from the annual rate.
Why does PMT return a negative number?
Excel uses opposite signs for money coming in and money going out. A positive present value, such as loan proceeds received, generally produces a negative payment. Reverse the present-value sign if you want the payment shown as positive.
What is the difference between type 0 and type 1?
Type 0 means payments occur at the end of each period and is the default. Type 1 means payments occur at the beginning of each period. Choose the one that matches the actual schedule.
Can PMT calculate a loan with a balloon payment?
Yes, the optional fv argument can represent a nonzero balance remaining after the last regular payment. Apply the correct cash-flow sign convention and verify the result against the loan’s stated terms.
Related
- Group Rows in Smartsheet: Sheets, Reports, and Views
- Numbers Text to Columns: Split Your Data Correctly
- Hayward Salt Cell Split Nut: Fit, Failure, and Replacement
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Unique Records in Excel: Formulas and PivotTablesTo count distinct values in a column in Microsoft Excel for Microsoft 365, put this formula in an…
- Excel Time Converter: Formulas, Formats, and TipsTo convert decimal hours in Excel into a time value, divide by 24; to convert an Excel time…
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
- LEFT Excel String Formula: Extract Text from the LeftIn Excel, use the LEFT function to return a chosen number of characters from the beginning of a…
- Excel Formula for Variance Between Two CellsTo calculate the difference between two cells in Excel, subtract one from the other: with the earlier or…
- Excel Formula to Password Protect a Sheet? What WorksNo Excel formula can password-protect a worksheet: formulas calculate values, but they cannot set sheet permissions or require…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.