Excel’s PMT function calculates a loan’s regular payment when you know its interest rate, number of payments, and principal.
A small worked example shows how to set up the inputs correctly and why the result can change when payment timing or rate units are wrong.
Excel loan payment formula
Use the PMT function to calculate a fixed payment for a loan with a constant interest rate and equal payment periods.
Its syntax is:
=PMT(rate, nper, pv, [fv], [type])
rateis the interest rate for one payment period.nperis the total number of payment periods.pvis the present value, or amount borrowed.fvis the desired balance after the final payment; it defaults to 0.typeindicates when payments are due: 0 or omitted for the end of a period, and 1 for the beginning.
For an ordinary fixed-rate loan paid monthly, divide the annual interest rate by 12 and multiply the number of years by 12.
For example, if the annual rate is 6%, the term is 5 years, and the principal is $20,000, enter:
=PMT(6%/12, 5*12, 20000)
The result is approximately -$386.66 per month.
Excel returns a negative amount because the payment is a cash outflow, while the borrowed principal is a cash inflow.
To display the payment as a positive number, enter the principal as negative instead: =PMT(6%/12, 5*12, -20000).
Build the formula with worksheet cells
Cell references make the calculation easier to update and audit.
Enter these inputs in a worksheet:
B2: annual interest rate, such as6%B3: loan term in years, such as5B4: loan amount, such as20000
Use this formula to return a positive monthly payment:
=PMT(B2/12, B3*12, -B4)
Format the result cell as Currency or Accounting.
If you want a negative cash-flow result, use =PMT(B2/12, B3*12, B4) instead.
Keep the sign convention consistent if you later combine the payment with other cash flows.
Match the rate and number of periods
The rate and nper arguments must use the same period.
For monthly payments, use a monthly rate and a count of monthly payments.
For quarterly payments, use the quarterly rate and number of quarters; for annual payments, use the annual rate and number of years.
If the annual rate is in B2, monthly payments are typically modeled with B2/12 and years*12.
For weekly payments, the corresponding setup is often B2/52 and years*52.
Check the loan agreement for the rate convention and payment schedule: lenders may use daily accrual, nonstandard periods, variable rates, or other terms that a simple PMT calculation does not represent exactly.
Do not enter an annual percentage rate directly as the monthly rate.
For example, =PMT(6%, 60, -20000) treats 6% as the rate for every month, not the annual rate.
That produces a very different result from =PMT(6%/12, 60, -20000).
Include a final balance or payments at the beginning
Most fully amortizing loans are expected to reach a zero balance after the final payment, so the default fv of 0 is appropriate.
If the loan has a balloon payment or another planned balance at the end, include that amount as fv.
For example, =PMT(6%/12, 60, -20000, 5000) calculates a payment while leaving a $5,000 future balance, using the positive-principal sign convention shown here.
Most loan payments are due at the end of each period.
That is the default, type 0.
If payments are due at the beginning, use type 1.
For example, =PMT(6%/12, 60, -20000, 0, 1) models payments at the start of each month.
Beginning-of-period payments reduce the amount of interest accumulated between payments, so the calculated regular payment is generally lower than for otherwise identical end-of-period payments.
Common Excel PMT errors and edge cases
- Unexpected negative result: This is normal when the principal is entered as a positive cash inflow.
Negate the principal if you want a positive payment amount. - Payment seems far too large: Check that an annual rate was converted to a per-period rate and that the term uses the correct number of periods.
- Loan balance does not match the lender: PMT assumes a constant rate and equal payments.
Fees, insurance, variable rates, irregular first periods, rounding, and lender-specific accrual methods can change the actual schedule. - Zero interest: PMT can calculate equal payments at a zero rate, provided the period count is valid.
The payment is principal divided across the periods. - Invalid arguments:
npermust be greater than zero.
A nonnumeric cell or an incorrect reference can also cause an error. - Too many decimal places: Format the result as currency for display.
Avoid rounding the payment inside the formula unless the loan terms require it, since early rounding can affect the calculated total.
Excel and Google Sheets compatibility
PMT is available in current desktop and web versions of Microsoft Excel, including Microsoft 365, and in Google Sheets.
The function syntax and core rate, period, principal, future-value, and timing arguments are compatible across both spreadsheet applications.
Regional settings may use semicolons instead of commas between arguments, so a localized spreadsheet may require =PMT(B2/12; B3*12; -B4).
PMT gives the recurring payment, not a full repayment schedule.
To see how much of each payment goes toward principal and interest, use spreadsheet functions such as IPMT and PPMT, or create an amortization table that tracks the opening balance, interest, principal paid, and closing balance for each period.