EXCEL PMT FUNCTION

This function calculates a loan’s monthly payment based on a fixed payment and a fixed interest rate. For example, if you’re buying a car for $20000 and have a 24 month loan with 7% interest, you can figure out your monthly payment and how much principal and interest you’ll be paying each month.

Syntax:

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

Parameter list:

  • rate – the loan’s interest rate, which you must divide by 12 if paying monthly.
  • pmt – the loan’s total number of payments (12 months of 36 months).
  • pv stands for present value, or the current total worth of all loan payments.
  • fv – [optional] the future value, or the cash balance you’d like after the last payment. 0 is the default value (zero).
  • When payments are due, type – [optional]. 0 indicates the end of the era. 1 indicates the start of the era. The default value is 0.

Step By Step Guide of PMT Function

We’re estimating how much we’ll pay in principle and interest on a car loan over the course of 48 months in this example file. The car is worth $100,000, and we’re looking for an 8% loan with a 48-month term. =PMT(B2/12,B3,B1) is the formula for this.
We’ll start with the interest rate, which we’ll divide by 12 to obtain the monthly interest rate because we’re paying monthly.
Second, we’re going to enter the month number, which is 48.
We’ve inserted a negative sign to indicate the money has been deducted from our bank in the third parameter, which is the car value or principle amount or loan taken.
After you’ve entered the formula, you’ll obtain the monthly payment for this loan for the next 48 months.
Now multiply the Period by the Monthly payments to obtain the total loan amount paid, which is $11,718.20, and remove this from the Principal to get the interest paid, which is $1,718.20.

PMT’s payment includes principle and interest but excludes taxes, fees, hidden charges, reserve payments, and other costs.
Make sure the first and second parameters you provide are accurate. For example, if you’re calculating a monthly payment for a loan with a 10% interest rate over four years, the rate will be 10%/12 and the nper will be 4*12. If you make annual payments on the same loan, the rate will be 10% and the nper will be 4.
If it’s quarterly, the rate will be 10% /4, and the nper (four years multiplied by four quarters) will be 16.
The amount computed in Excel2007 and Excel2010 may change somewhat since the algorithms used in both are different, and Excel 2010 computation is more exact.

Related Functions

HLOOKUP Function

HLOOKUP Function

Explore Now

VLOOKUP Function

VLOOKUP Function

Explore Now

INDEX Function

INDEX Function

Explore Now

MATCH Function

MATCH Function

Explore Now

DATE Function

DATE Function

Explore Now

IF Function

IF Function

Explore Now

OR Function

OR Function

Explore Now

AND Function

AND Function

Explore Now

RANK Function

RANK Function

Explore Now

SUMIF Function

SUMIF Function

Explore Now

XIRR Function

XIRR Function

Explore Now

WORKDAYS Function

WORKDAYS Function

Explore Now