PMT Function
The PMT function calculates the payment for a loan on regular payments and a constant interest rate over a period. The PMT function is often used to calculate the repayment of a mortgage with a fixed interest rate.
Syntax
PMT(rate, nper, pv, [fv], [type])
Arguments
|
Arguments
|
Description
|
|
Rate
|
Required. This is the interest rate per period.
|
|
Nper
|
Required. This is the total number of payment periods in an annuity i.e. the term.
|
|
Pv
|
Required. This is the present value of a principal or a series of future payments.
|
|
Fv
|
Optional. This is the future value of an investment based on an assumed rate of growth.
If you omit fv, it is assumed to be 0 (zero), i.e. the future value of a loan is 0.
|
|
Type
|
Optional. This argument is 0 or 1 and indicates when payments are due.
0 (or omitted) = at the end of the period.
1 = at the beginning of the period.
|
Some points to take into consideration when using annuity functions:
- The payment returned by PMT is for the principal and interest. It does not include taxes, reserve payments, or other fees they may be associated with loans.
- You always need to express the rate
argument in the same units as the nper
argument. For example, say you have monthly payments on a three-year loan at 5% annual interest. If you use 5%/12 for rate
, you must use 3*12 for nper
. If the payments on the same loan are being made annually, then you would use 5% for rate and 3 for nper.
Tip
: To calculate the total amount paid over the duration of the loan, simply multiply the value returned by PMT by the number of payments (nper).
Example
In the example below, we calculate the PMT for two loans:
- A $10,000 loan over 12 payments at 8.0 percent interest.
- A $10,000 loan over 60 payments at 4.9 percent interest.
Formula explanation
=PMT(A2/12,B2,C2)
The rate argument is a reference to cell A2 divided by 12, to represent the interest rate in monthly terms as nper (cell B2) is also specified in monthly terms. The pv argument takes in C2, which is the present value of the loan $10,000.
Answer: ($869.88)
=PMT(A3/12,B3,C3)
This formula is also for a loan of $10,000, however, the nper is 60 and the rate is 4.9 percent.
Answer: ($188.25)
