Function Problems

Calculate a Loan Payment With the Excel PMT Formula

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

Calculate a periodic loan payment using the PMT function, with options for a remaining balance and payment timing.

Calculate a Loan Payment With the Excel PMT Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user knows the loan amount, interest rate, payment frequency, and number of payments and wants to calculate the periodic payment.
Observed behavior
The desired result is a periodic payment based on a rate that matches the payment period.
Before you start

Gather the loan amount, interest rate, payment frequency, number of payments, and any required remaining balance. Check the lender's compounding rules before converting an annual rate to a periodic rate.

Solution 1Recommended

Use Excel's PMT function to calculate the loan payment

Use this method to calculate a regular payment with an optional remaining balance or beginning-of-period payment.

PMT uses the syntax PMT(rate, nper, pv, [fv], [type]). The rate and number of payments must use matching periods. A negative present value and positive future value produce a positive payment in these examples.

1
Enter the loan details

In an Excel worksheet, enter the positive loan amount in B1, the periodic interest rate in B2, and the total number of payments in B3. For a nominal annual rate with monthly payments, dividing the annual rate by 12 is often appropriate, but follow the lender's compounding rules.

2
Calculate a standard payment

Select an empty cell, enter =PMT(B2,B3,-B1), and press Enter. This assumes a zero final balance and payments at the end of each period.

3
Include a remaining balance if needed

Enter the positive remaining balance in B4, then use =PMT(B2,B3,-B1,B4) in the payment cell. To calculate payments made at the beginning of each period, use =PMT(B2,B3,-B1,B4,1) instead.

4
Set the payment precision

Select the result cell and use Home > Number to display two decimal places. If the actual payment must be rounded, use =ROUND(PMT(B2,B3,-B1,B4),2) instead.

Use Excel's PMT function to calculate the loan payment
Rounding can affect the final balance: Formatting changes only the displayed value. Rounding the actual payment can cause an amortization schedule to finish with a balance different from zero or the intended remaining balance.
Calculate loan payments in WPS

Calculate loan payments with PMT in WPS Spreadsheets

Enter your loan details in WPS Spreadsheets and use the PMT function to calculate a periodic payment, including an optional remaining balance.

  1. 1. Open a spreadsheet: Open WPS Spreadsheets and create a blank workbook or open an existing Excel file.
  2. 2. Enter the loan details: Enter the positive loan amount in B1, the periodic interest rate in B2, and the total number of payments in B3.
  3. 3. Calculate the payment: Select an empty cell, enter =PMT(B2,B3,-B1), and press Enter. For a remaining balance, enter it as a positive value in B4 and use =PMT(B2,B3,-B1,B4).
  4. 4. Format the result: Select the payment cell and use the number formatting controls on the Home tab to display two decimal places.
Calculate periodic loan payments with the PMT functionOpen and edit Microsoft Excel XLSX filesDisplay payment results with two decimal places
QA img-9

Frequently Asked Questions

Why does my PMT formula return a negative payment?

PMT uses opposite signs for cash inflows and outflows. If B1 contains a positive loan amount, use =PMT(B2,B3,-B1) to return a positive payment.

How do I convert an annual rate for monthly payments?

For a nominal annual rate, dividing by 12 is often appropriate. Enter the resulting monthly rate in B2, but first check the lender's compounding rules because other conversions may apply.

How do I include a balloon balance in the payment calculation?

Enter the positive remaining balance in B4 and use =PMT(B2,B3,-B1,B4). This calculates the periodic payment needed to leave that balance after the specified payments.

How do I calculate payments due at the start of each period?

Set PMT's fifth argument to 1. With the remaining balance in B4, use =PMT(B2,B3,-B1,B4,1). Enter 0 in B4 if the intended final balance is zero.

Does displaying two decimal places round the payment used in formulas?

No. Home > Number formatting changes the display, not the underlying value. Use ROUND around PMT only when the actual payment needs to be rounded.