To calculate a fixed periodic loan payment in Excel, use the PMT function, making sure the interest rate and number of periods use the same time unit. For example, if a loan amount is in B1, its annual interest rate is in B2, and its term in years is in B3, enter =PMT(B2/12,B3*12,B1) for monthly payments; Excel returns a negative amount because it treats the loan payment as cash flowing out.
What PMT calculates
PMT calculates the regular payment needed to pay off a loan or reach a savings target over a specified number of equal periods, assuming a constant interest rate and consistent payment timing. It is useful for estimating payments on a fixed-rate loan, comparing repayment schedules, or planning regular deposits toward a future value. The result is a payment amount, not a complete loan schedule: it does not show how much of each payment is principal versus interest.
The function is based on the time value of money. A dollar paid today and a dollar paid later are not equivalent when interest accrues. PMT finds a level payment that accounts for the starting balance, interest between payments, and the number of payments. It can calculate loan payments with a future balance of zero, or periodic deposits intended to build a specified future balance.
PMT is an estimate based on the inputs you supply. It does not automatically include origination charges, insurance, taxes, changing interest rates, or lender-specific rounding and payment rules. For a real borrowing decision, check the payment schedule and all applicable terms with the lender or a qualified professional. This calculation is educational, not financial, tax, investment, or legal advice.
PMT arguments and the rate-period rule
PMT takes arguments in this order: 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; pv is the present value, such as the amount borrowed; fv is the desired balance after the last payment; and type specifies when payments occur. The last two arguments can be omitted when the future balance is zero and payments occur at period-end.
The most important rule is that rate and nper must describe the same period. For monthly payments, divide an annual nominal rate by 12 and multiply the number of years by 12. For quarterly payments, convert the annual rate to a quarterly rate and count quarters. For annual payments, use the annual rate and number of years directly. Mixing a monthly rate with a term expressed in years can produce a plausible-looking but incorrect answer.
Use the rate that matches the loan agreement. Dividing an annual rate by 12 is appropriate for a nominal annual rate charged monthly, but it is not a universal conversion for every rate quoted as an effective annual yield. Lenders may define rates, compounding, and payment periods differently. If the contract specifies a monthly rate or an effective rate, follow the contract’s conventions rather than assuming the simple annual-rate conversion.
Set up a monthly payment calculation
In Excel for Microsoft 365 on Windows, put the loan inputs in separate cells, format the rate as a percentage, and enter the PMT formula in another cell. A clear layout makes it easier to audit and revise assumptions later. For instance, label A1 Loan amount, A2 Annual interest rate, and A3 Term in years; enter the corresponding values in B1:B3. Then calculate the payment in B4.
With the loan amount in B1, annual nominal rate in B2, and term in years in B3, enter this in B4: =PMT(B2/12,B3*12,B1). This assumes monthly payments, a zero balance after the final payment, and payments at the end of each month. It returns a negative result under Excel’s cash-flow convention. To show the payment as a positive borrower-facing amount, use the same cell layout and enter =-PMT(B2/12,B3*12,B1). The negative sign changes only how the result is displayed; it does not change the underlying calculation.
On Excel for Mac, PMT works the same way and the formula syntax is identical. Excel for the web also supports PMT with the same formula. The main difference is the interface, not the function. Excel formulas normally use commas between arguments in the United States English locale; some regional settings use semicolons, so Excel may adjust separators based on your system’s list-separator settings.
Worked example: a $25,000 loan
Suppose a borrower models a $25,000 loan at a 6% annual nominal interest rate, repaid monthly over five years. The monthly rate is 6% divided by 12, or 0.5%; the number of monthly payments is five times 12, or 60. Enter 25000 in B1, 6% in B2, and 5 in B3. With those values in B1:B3, enter =PMT(B2/12,B3*12,B1) in B4. Excel returns approximately -$483.32, meaning the borrower’s monthly payment is about $483.32.
The sign is negative because the borrowed amount is treated as money received and the payments as money paid out. To make a simple total-payment estimate, multiply the absolute monthly payment by 60. Using the displayed payment, $483.32 × 60 is about $28,999.20, or roughly $3,999.20 above the original principal. That estimate uses the rounded displayed payment; Excel’s underlying result has additional decimal places, so a total based on the unrounded result can differ by a few cents. Actual lender totals may also differ because of payment dates, fees, rounding, or contract terms.
PMT assumes all payments are equal. A real first payment may not be due exactly one month after funds are issued, and some contracts use daily interest or other conventions. Treat the example as a way to understand the function, not as a quote for a specific loan.
Why Excel shows a negative payment
Excel’s financial functions use cash-flow signs. Money received is conventionally positive, while money paid is negative. If the present value of a loan is entered as a positive amount, PMT returns a negative payment to balance that cash flow. This is not an error, and applying ABS to every result is not always the best fix: when comparing or combining cash flows, consistent signs carry useful meaning.
There are two common ways to make the payment easier to read. Place a minus sign before PMT in the formula, as in the earlier example, or enter the present value as a negative number so that the payment comes back positive. Choose one convention and document it. If you enter a negative loan principal and also negate the PMT result, you may reverse the sign twice and end up with a result that confuses readers.
Payment timing and the optional arguments
By default, PMT assumes payments happen at the end of each period, which is often the standard assumption for a loan. The optional type argument changes timing: 0 means end-of-period, and 1 means beginning-of-period. Beginning-of-period payments accrue interest for less time before each payment, so the required payment can differ. Use type 1 only when the actual arrangement calls for payments at the beginning of each period, such as some rent or lease schedules.
The optional fv argument is the balance or target value remaining after the final payment. For a fully amortized loan, that is usually zero, which Excel assumes when the argument is omitted. A nonzero future value can model a balloon balance or a savings goal, but it does not tell you whether such a loan structure is appropriate. Make sure the value and its cash-flow sign are consistent with the other inputs.
With principal in B1, annual nominal rate in B2, and term in years in B3, this formula models end-of-month payments and explicitly sets the future balance to zero: =PMT(B2/12,B3*12,B1,0,0). It gives the same result as omitting the final two arguments for this standard loan setup. To model beginning-of-month payments with the same inputs, use =PMT(B2/12,B3*12,B1,0,1). The difference is timing, not a different interest rate.
Common PMT errors and checks
When an answer seems wrong, check the inputs before changing the formula. Many mistakes come from mismatched periods or values entered in the wrong units rather than a problem with Excel’s calculation.
- Annual rate used as a monthly rate: If B2 contains 6%, using B2 directly with 60 periods means Excel applies 6% every month. Divide by 12 when the quoted nominal annual rate is charged monthly.
- Years used as the payment count: A five-year monthly loan has 60 periods, not five. Multiply the term by 12 for monthly payments.
- Percent entered as a whole number: Enter 6% or 0.06 for six percent, not 6. A value of 6 means 600% if Excel treats it as a decimal rate.
- Unexpected negative output: This is normally Excel’s cash-flow sign convention. Decide whether the spreadsheet should show the payment as an outflow or a positive amount for presentation.
- Payment does not match a lender quote: Check whether the quoted amount includes fees, escrow, insurance, or a different first-payment date. Confirm the interest-rate definition, compounding, and payment frequency.
- Cell references move when copying: If you copy a formula and want an input cell to remain fixed, use an absolute reference such as $B$2. Press F4 while editing a reference in Windows to cycle through reference styles; on Mac, the shortcut may depend on the keyboard and Excel settings.
Also check that the term is a number and that rate cells are numeric percentages rather than text. A formula can calculate without an error message while still using an incorrect assumption, so verify the implied number of periods and the units for every input.
PMT compared with a payment schedule
PMT is useful when you need one fixed payment amount. It does not provide a month-by-month breakdown. For that, build an amortization table with columns for period, opening balance, payment, interest, principal, and closing balance. Each period’s interest is based on the remaining balance and periodic rate; the rest of the payment reduces principal. A schedule makes it easier to see how the interest share generally falls and the principal share rises over time.
Excel also has IPMT and PPMT functions for calculating the interest and principal portions of a payment in a specified period. Those can help create a schedule, but the same rate, period-count, and sign rules apply. For a lender’s official payoff amount or a loan with variable rates, irregular payments, or fees, a home-built table may not reproduce the contract. Use the lender’s figures for contractual decisions.
Frequently Asked Questions
What is the PMT formula in Excel?
PMT calculates a fixed periodic payment using an interest rate per period, total number of periods, and present value. For a monthly loan, convert the annual rate to a monthly rate and the term in years to a number of months.
Why does PMT return a negative number?
Excel treats a loan advance and its repayments as cash flows in opposite directions. With a positive loan amount entered as present value, payments usually appear negative. Negate the function result if the spreadsheet should display a positive payment amount.
Should I divide the interest rate by 12?
Do so when the input is a nominal annual rate applied monthly and you are calculating monthly payments. Use the rate period stated by the loan terms; an effective annual rate or a contract with different compounding conventions may require a different conversion.
How do I calculate an annual payment?
Use an annual rate and a count of annual periods, rather than converting to months. For example, with a loan amount in B1, annual rate in B2, and term in years in B3, enter =PMT(B2,B3,B1). This assumes one payment per year at period-end.
Does PMT include taxes, fees, or insurance?
No. PMT uses the values and timing supplied to it; it does not automatically add fees, taxes, insurance, or other charges. Include any relevant costs separately when estimating a total outlay, and verify actual figures with the lender or a qualified professional.
Related
- Basic Project Timeline Template in Excel: A Simple Guide
- Basic Sum in Excel: How to Add Cells and Numbers
- Basic Excel Budget Template: Build a Monthly Budget
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP With Multiple Criteria: Excel FormulasIf your table has Product in column B, Region in C, Sales in D, and a combined lookup…
- Excel Power Query Table Filter: How to Apply and RefreshTo filter an Excel table with Power Query in Microsoft 365 for Windows, select a cell in the…
- Excel Filter Shortcut: Turn Filters On and Use ThemIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current…
- ISERROR in Excel: Check for Errors in Your FormulasIn Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such…
- Delete Left Text in Excel: Formulas and MethodsTo remove a set number of characters from the left of a cell in Excel, enter =REPLACE(A2,1,3,"") in…
- How to Find the Average Date in Excel With a FormulaIf your dates are in A2:A10, enter =AVERAGE(A2:A10) in an empty cell, then format that cell as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.