PMT Function - Excel
Overview
The PMT function in Excel is a useful tool for calculating the periodic payment for a loan or an investment, assuming a constant interest rate and constant payments. This function is used to help determine the payment amount required to pay off a loan or the periodic contribution needed for an investment with regular, constant payments.
Example:
=PMT(0.05, 12, -100000)
Syntax:
=PMT(rate, nper, pv, [fv], [type])
rate: The interest rate for each period.
nper: The total number of payment periods.
pv: The present value or principal amount (the initial loan amount or investment).
[fv]: An optional argument representing the future value, or the desired loan balance after the last payment is made (defaults to 0 if omitted).
[type]: An optional argument indicating whether payments are due at the beginning or end of the period (0 or omitted for end-of-period payments, 1 for beginning-of-period payments).
Example
In this example, the PMT function takes in three inputs to give us the payment value output. First it takes in the rate, which is 5% in this example. Next it takes in the number of payment periods, which is 10 in this example. Finally it takes in the present value amount, which is -1000 in this example. The present value is negative because it’s an outgoing investment. The final output of this function is a payment value of “$129.50”.
Example: Optional [fv] input
In this example, we will use the optional “future value (fv)” input for the PMT function. All of the inputs are the same as the previous example except this time we are adding the fv input, which is -5000 in this example. The fv input is negative because it represents a final outgoing investment at the end of the term. The final output of this function is a payment value of “$527.03”.
Example: Optional [type] input
In this example, we will use the optional “type” input for the PMT function. The “type” input has two options, you can either enter 0 to indicate that a payment is due at the end of a period or a 1 to indicate that a payment is due at the beginning of a period. In this example I show the difference between the 0 type entry and the 1 type entry. When the payment is due at the beginning of a period (1 type entry), the future value tends to be higher. The reason for the future value being higher with the payment period at the beginning is because you have more time to earn interest on that money.
