Menu
EXCEL PMT FUNCTION
Excel PMT Function Introduction
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.
Description of Excel PMT Function
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.
Key Points
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 NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
