With the loan amount in B2, annual interest rate in B3, and term in years in B4, enter =PMT(B3/12,B4*12,B2) to calculate a monthly payment. For a $20,000 loan at 6% over five years, Excel returns about -$386.66; the minus sign indicates money paid out.
What the PMT function calculates
PMT calculates the regular payment needed to pay off a loan or reach a savings goal when the interest rate and payment amount stay constant. It is useful for estimating fixed monthly loan payments, but it does not tell you how much of each payment goes toward interest versus principal. That breakdown changes over the life of most loans.
The function uses a standard time-value-of-money model. Each payment is assumed to occur at a regular interval, and interest is applied at a consistent rate for each interval. The payment amount is level: Excel calculates the amount that makes the present value of the payments equal to the amount borrowed, subject to the rate, term, and other inputs.
PMT can also model regular contributions or withdrawals in other situations, such as saving toward a target. For a loan estimate, however, treat the result as a calculation from the inputs—not a lender quote. Actual loan payments can differ because of fees, insurance, taxes, rate changes, or lender-specific calculation rules.
PMT syntax and arguments
The function syntax is PMT(rate,nper,pv,[fv],[type]). The first three arguments are required; the last two are optional. The comma-separated arguments in the syntax describe the order Excel expects, not values that should be typed literally.
- rate: The interest rate for one payment period. For monthly payments, use a monthly rate, not an annual rate.
- nper: The total number of payment periods. A five-year loan with monthly payments has 60 periods.
- pv: The present value, usually the amount borrowed. This is entered as a positive amount in the example below.
- fv: The future value after the last payment. It is commonly 0 for a loan that will be fully paid off.
- type: The payment timing: 0 or omitted means payments are due at the end of each period; 1 means they are due at the beginning.
All the time-based inputs must match. If the payment period is monthly, use a monthly rate and a number of months. Using an annual rate with a count of monthly payments mixes periods and produces a misleading result.
Worked example: a $20,000 loan
Suppose a borrower takes a $20,000 fixed-rate loan at a nominal annual rate of 6%, makes monthly payments, and repays the balance over five years. Put 20000 in B2, 6% in B3, and 5 in B4. The annual rate is divided by 12 to get the monthly rate, and the number of years is multiplied by 12 to get the number of payments.
With B2 as the loan amount, B3 as the annual rate, and B4 as the term in years, use =PMT(B3/12,B4*12,B2). Excel returns approximately -$386.66 per month. The negative result follows Excel’s cash-flow sign convention: the loan proceeds are money received, while the payments are money leaving the borrower’s account.
In absolute terms, the regular payment is about $386.66. Multiplying the unrounded monthly result by 60 gives total payments of approximately $23,199.40, so the modeled interest is approximately $3,199.40 above the $20,000 principal. These totals are approximate because displaying a payment rounded to cents does not mean Excel uses that rounded amount in its internal calculation. A lender may also round each actual payment or adjust the final one.
To enter the example in Excel for Microsoft 365 on Windows, type the three inputs into the cells, select the result cell, and enter the formula. To show a positive payment for presentation, format the result as currency and account for the negative sign, or calculate the displayed amount as a positive value in a separate formula. Keep the underlying sign convention consistent if you later use other financial functions.
How to choose the rate and payment periods
The rate argument must represent the rate for each payment interval. If a lender quotes a nominal annual rate and payments are monthly, dividing that rate by 12 is the usual PMT setup for a fixed-rate estimate. For example, an annual rate stored as 6% is entered as 6%, not as 6. The percent formatting represents 0.06 internally.
That conversion is not automatically correct for every rate measure. A quoted effective annual rate already reflects compounding over the year, so simply dividing it by 12 does not produce its equivalent monthly rate. Also, a loan’s advertised APR may include fees or use a disclosure convention that is not identical to the periodic interest rate used in a payment calculation. Check the lender’s stated rate basis and payment schedule before treating an estimate as exact.
Match nper to the number of payments, rather than the number of years alone. A four-year loan paid every two weeks has a different number of periods from a four-year loan paid monthly. For an irregular schedule, a changing interest rate, or payments that vary, PMT’s constant-period assumptions may not fit the agreement.
Payment timing and the optional arguments
Most standard loan payments are due at the end of a period, so type is usually omitted or set to 0. A payment due at the beginning of each period is modeled with type 1. Because the payment is made earlier, less time elapses before it is received, and the level payment needed for the same present value is generally smaller than with end-of-period payments.
With the same inputs in B2:B4—loan amount, annual rate, and term in years—the following formula sets payments at the beginning of each month: =PMT(B3/12,B4*12,B2,0,1). For the example loan, the result is approximately -$384.74 per month. Use this only when the actual payment schedule calls for beginning-of-period payments; changing type simply to get a lower estimate would misrepresent the timing.
The optional fv argument is useful when the balance is not expected to reach zero. For example, some financing arrangements have a residual or balloon amount due at the end. Setting a future value changes the payment calculation, but the appropriate value and sign depend on the cash-flow perspective and contract. Read the agreement carefully rather than assuming a balloon payment is included in a standard loan estimate.
Understanding negative results and cash-flow signs
A negative PMT result is not an error. Excel represents cash received and cash paid with opposite signs. If pv is entered as a positive loan amount received, the payment is returned as a negative amount paid. Many people leave that result intact in a financial model because it makes the direction of cash flow explicit.
If a worksheet is intended only to display the payment as a positive number, one option is to negate the result. With the same B2:B4 inputs and the loan amount entered as a positive value, use =-PMT(B3/12,B4*12,B2) to display approximately $386.66. Another approach is to enter the present value as a negative amount, which makes PMT return a positive payment. Avoid changing signs inconsistently across related calculations, since that can make totals and cash-flow comparisons confusing.
What PMT includes—and what it leaves out
PMT calculates a level payment from the rate, number of periods, present value, future value, and timing. It does not automatically add a loan origination fee, property tax, homeowners insurance, private mortgage insurance, maintenance, or other expenses. If those charges are part of a real monthly budget, evaluate them separately using the figures and terms that apply to the situation.
It also does not produce an amortization schedule. An amortization schedule shows each period’s opening balance, interest portion, principal portion, and remaining balance. Early payments on a typical amortizing loan include more interest than later payments because interest is calculated on the outstanding balance. PMT gives the same total payment each period under its assumptions, not the changing split within it.
For an illustration of total payments, with B2 holding principal, B3 the annual rate, and B4 the term in years, use =-PMT(B3/12,B4*12,B2)*B4*12. For the example, this is approximately $23,199.40. It is a simple total of the calculated payments, not a complete accounting of fees or other ownership costs.
Common PMT errors and how to fix them
If the result is far too large or too small, first check whether the rate and term use the same period. A formula using a 6% annual rate directly with 60 monthly periods treats 6% as the rate for every month, not for the year. For a nominal annual rate and monthly schedule, divide the annual rate by 12 and use the count of monthly payments.
- Wrong number of periods: Convert years to payment periods. Five years of monthly payments means 60 periods, not 5.
- Unexpected negative payment: Check the signs of the loan amount and payment. Opposite signs are normal for cash received and cash paid.
- Payment does not match a quote: Check whether the quote includes fees, uses a different rate basis, or has a different due date or term.
- Wrong timing: Confirm whether payments are due at the end or beginning of each period. The default is end of period.
- Input error: Make sure rate, period count, and present value cells contain numbers rather than text, and confirm that the cell references point to the intended inputs.
For an ordinary fixed-rate loan, a zero future value is often appropriate because the loan is intended to be fully repaid. If the payment is still inconsistent with the agreement after checking these assumptions, use the lender’s payment schedule and stated calculation terms as the reference rather than forcing PMT to match an incomplete quote.
PMT compared with related Excel functions
PMT gives the total periodic payment. Excel’s PPMT function calculates the principal portion for a particular period, while IPMT calculates the interest portion for a particular period. Those functions can help build an amortization schedule, but they require a period number as well as the rate, term, and present value. The principal and interest portions for a period together make up the payment under the modeled assumptions.
For a quick affordability estimate, PMT is more direct than manually constructing a payment calculation. For a variable-rate loan or irregular payments, a single PMT result may not represent the real schedule. A row-by-row model based on the actual dates, rates, and payment rules may be more suitable, but the lender’s documentation remains the source for contractual amounts.
Frequently Asked Questions
What is the PMT formula in Excel for a monthly loan?
Use the monthly interest rate, total number of monthly payments, and loan amount as the first three arguments. For example, with an annual rate of 6%, a five-year term, and a $20,000 loan, PMT returns about -$386.66 per month when the loan amount is entered as positive.
Why does Excel PMT show a negative number?
Excel uses opposite signs for money received and money paid. A positive present value represents money received from the loan, so the payment is shown as a negative cash flow. You can reverse the sign for display, provided you keep the rest of the cash-flow model consistent.
Should I divide the interest rate by 12?
For a nominal annual rate used with monthly payments, dividing by 12 is the usual setup. Do not assume this conversion fits an effective annual rate or every APR quote; confirm how the rate is defined and how the lender calculates payments.
What does the type argument do in PMT?
Type sets when each payment occurs. Use 0 or omit it for payments at the end of each period, which is the common loan assumption. Use 1 for payments at the beginning of each period.
Does PMT include taxes and insurance?
No. PMT calculates a payment from the financial inputs supplied; it does not automatically include taxes, insurance, fees, or other charges. Add relevant costs separately when making a broader estimate, and confirm actual terms with the lender or a qualified professional. This example is educational, not financial, tax, investment, or legal advice.
Related
- Excel Dependent Dropdowns: How to Create Linked Lists
- Excel Forecast Sheet: Does It Use AI and How It Works
- Dock Audit Format in Excel: Build a Practical Checklist
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- NPV Formula Examples in Excel: Cash Flows ExplainedIn Excel, calculate net present value with =NPV(rate, future_cash_flows)+initial_cash_flow: enter the discount rate and future cash flows in…
- Excel Formula for Age From DOB: Years, Months, and DaysTo calculate age in completed years from a date of birth in cell A2, use =DATEDIF(A2,TODAY(),"Y"). It compares…
- How to Copy Formulas in Excel by Dragging the Fill HandleTo copy a formula in Excel by dragging, select the cell with the formula, point to the small…
- JavaScript String startsWith() Function: How It WorksYes. JavaScript provides the string method startsWith() to check whether a string begins with a specified sequence of…
- Pandas read_excel vs read_csv: Choose the Right ReaderUse pandas.read_csv() for delimited text files such as CSV, and pandas.read_excel() for Excel workbooks such as XLSX. The…
- How to Calculate Numbers in Excel With Simple FormulasTo calculate numbers in Excel, start a cell entry with an equals sign and use cell references or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.