The Excel PMT function calculates the constant periodic payment required to pay off (or partially pay off) a loan or investment, with a constant interest rate, over a specified period.
The syntax of the function is :
Where the arguments are as follows:
|rate||-||The interest rate, per period|
|nper||-||The number of periods over which the loan or investment is to be paid|
|pv||-||The present value of the loan / investment|
An optional argument that specifies the future value of the loan / investment, at the end of nper paymentsIf omitted, [fv] takes on the default value of 0
An optional argument that defines whether the payment is made at the start or the end of the periodThe [type] argument can have the value 0 or 1, meaning:
0 - the payment is made at the end of the period
In each of the examples below, the spreadsheet on the left shows the format of the Pmt function, and the spreadsheet on the right shows the result.
The following spreadsheet shows the Excel Pmt function used to calculate the monthly payments on a loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.
Note that in this example :
In this example, the spreadsheet below shows the Excel Pmt function being used to calculate the quarterly payments on a loan of $10,000 that is to be reduced to $5,000 over a period of 2 years. Interest is charged at a rate of 3.5% per year and the payment is to be made at the beginning of each quarter.
Note that, in this example :
Further examples of the Excel Pmt function can be viewed on the Microsoft Office website.
If you get an error from the Excel Pmt function, this is likely to be one of the following:
|#NUM!||-||Occurs if the supplied value of nper is equal to 0|
|#VALUE!||-||Occurs if any of the supplied arguments are not recognised as numeric values|
Also, the following problem is encountered by some users:
The result from the Excel Pmt function is much higher or much lower than expected.
Many users, when calculating monthly or quarterly payments, forget to convert the interest rate or the number of periods to months or quarters.
Solve this problem by ensuring that the rate and the nper arguments are expressed in the correct units. i.e. :
|months||=||12 * years;||monthly rate||=||annual rate / 12|
|quarters||=||4 * years;||quarterly rate||=||annual rate / 4|