Show
How Could You Use PMT?The PMT function returns a payment amount, so you can use it to:
PMT SyntaxThe PMT function has the following arguments in its syntax. There are three required arguments, and 2 optional arguments:
PMT Traps
Example 1: Calculate Payment on Personal LoanTo see the steps for calculating a simple loan payment with the PMT function, watch this short video. The written instructions are below the video. With the PMT function, you can return a payment amount, based on loan information. In this example:
In cell C6, the PMT function calculates the monthly payment, based on the annual rate, which is divided by 12 to get the monthly rate, the number of payments (periods) and the loan amount (present value):
The payment, -230.29, is calculated as a negative amount, because you are paying that amount out of your bank account. If you would prefer to see the result as a positive number, you can use a minus sign before the PMT function:
Total Amount PaidIf you would like to see the total amount that will be repaid, over the duration of the loan, use the following formula in cell C8.
This formula multiplies:
Example 2: Calculate Payment on Canadian MortgageFor Canadian mortgage loans, the interest is compounded semi-annually, rather than monthly, even if the payments are monthly. To calculate the payments, you need a different rate calculation, instead of the simple Rate/12. Note: Visit your bank's website, or check with your banker, to confirm how your bank will calculate the payments. In this example:
In cell C6, the PMT function calculates the monthly payment, based on the annual rate, the number of payments (periods) and the loan amount (present value): =PMT((C2/2+1)^(1/6)-1,C3,C4) Instead of simply dividing the rate by 12, the rate calculation is: (Rate/2+1)^(1/6)-1
The payment, -657.13, is calculated as a negative amount, because you are paying that amount out of your bank account. If you would prefer to see the result as a positive number, you can use a minus sign before the PMT function: =-PMT((C2/2+1)^(1/6)-1,C3,C4) Example 3: Enhanced Loan CalculatorIn the previous examples, you had to enter the total number of payments due, after calculating that number -- number of years in the loan term, times the number of payments per year. To make things easier, this Excel loan payment calculator lets you select the payment frequency from a drop down list of options. In the sample file, the Lists sheet has a lookup table of frequencies and number of payments per year, for each frequency. Based on the frequency that you select, a number of payments per year is calculated in cell E5, using a VLOOKUP formula. =IFERROR(VLOOKUP(C5,FreqLU,2,0),"") The payment amount is calculated with the PMT function: =IFERROR(PMT(C7/E5,E6,-C4),"") In this workbook, there is a minus sign before the present value variable, so the monthly payment is shown as a positive number. You can omit the minus sign, to show the payment as a negative number. Example 4: Payment Date CalculationsIn this final example, the PMT function is used at the top of the worksheet, to calculate the monthly payment amount. Here is the formula in cell E2, which is named LoanPmt.
Payment Date TableThe first payment date is also entered at the top of the sheet, in cell A2, and a payment table calculates all the payment days, plus the interest and principal amounts each month. NOTE: There are 48 rows in the table, and you can add more rows if needed. The formulas should fill in automatically. Table FormulasHere are the formulas used in row 7 of the payment date table:
Last Payment HighlightingIn the table, the latest payment row is highlighted, based on a Conditional Formatting rule
In the Conditional Formatting rule:
Get the PMT Function Files
Excel Functions TutorialsPayment Calculation Table Loan Payment Schedule Template Cost Calculator, Annual SUM Function VLOOKUP Function INDEX function and MATCH Function Count Function INDIRECT Function Excel Function Video Tutorials What does PV stand for in the PMT function?Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal. Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
What is a PV argument?The PV function uses the following arguments: rate (required argument) – The interest rate per compounding period. A loan with a 12% annual interest rate and monthly required payments would have a monthly interest rate of 12%/12 or 1%. Therefore, the rate would be 1%.
What does PV stand for in Excel?PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate.
What does PMT stand for in finance?Payment (PMT)
This is the payment per period. To calculate a payment the number of periods (N), interest rate per period (i%) and present value (PV) are used.
|