Calculate a Loan Payment With the Excel PMT Formula
Question details
Calculate a periodic loan payment using the PMT function, with options for a remaining balance and payment timing.

- 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.
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.
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.
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.
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.
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.
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.

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. Open a spreadsheet: Open WPS Spreadsheets and create a blank workbook or open an existing Excel file.
- 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. 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. Format the result: Select the payment cell and use the number formatting controls on the Home tab to display two decimal places.

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.




