PMT is short for “payment”, and is a function used to calculate the payment for a loan based on constant payments and interest rates.
The PMT function consists of:
- Rate – interest rate
- Nper – number of payments for the loan
- Pv – present value
While the first three are required for the formula, the next two are optional:
- Fv – future value (desired cash balance after the last payment is made)
- Type – indicates when the payments are due; 0 indicates the end of the period, while 1 indicates the beginning
So, the formula for a loan payment will look like this:
=PMT(Rate,Nper,Pv, Fv, Type)
Note that the last two are optional.
Note: For calculating monthly payments, divide the interest rate by 12. For loans that will take a number of years to pay, multiply the Nper by the number of years (e.g. for a 3-year payment, multiply Nper by 3).
Example
Enter the following data in Excel:
Where:
- Rate = 8%
- Nper is 10
- Pv = $10,000
To find the monthly payment for a loan with the given values, type the following in cell A5 to get your result:
=PMT(A2/12,A3,A4)
Or, if you want to find the same as above, but given that payments are due at the beginning of the period, then use this formula instead:
=PMT(A2/12,A3,A4,,1)
