Guide Excel

How to Calculate a Loan Payment in Excel

The PMT function in Excel allows you to quickly calculate the periodic payment for a loan based on constant payments and a constant interest rate.

Étape par étape

1
Prepare your data
Enter your loan amount, annual interest rate, and total number of payment periods into separate cells.
2
Select the target cell
Click on the cell where you want the calculated payment to appear.
3
Enter the PMT formula
Type =PMT(rate/12, periods, -loan_amount) and press Enter to calculate the monthly payment.

=PMT(rate, nper, pv, [fv], [type])

Divide the annual interest rate by 12 if you are calculating monthly payments.
Use a negative sign before the loan amount to display the result as a positive number.

Why is my PMT result negative?

Excel treats loan payments as cash outflows, which is why the result is negative by default.