Home > Formulas > Lookup > PMT Excel PMT Function for Loan Payment The Microsoft Excel PMT function calculates the payment for a loan that has constant payments and a constant interest rate. Also see the Loan Payment Schedule Template page

How Could You Use PMT?

The PMT function returns a payment amount, so you can use it to:

• Calculate the monthly payment due on a personal loan
• Calculate the payment due for a Canadian mortgage loan, with interest compounded bi-annually

PMT Syntax

The PMT function has the following arguments in its syntax. There are three required arguments, and 2 optional arguments:

• PMT(rate, nper, pv, [fv], [type])
  • Rate is the interest rate for the loan.
  • Nper is the total number of payments for the loan.
  • Pv argument is the present value; also known as the principal.
  • Fv argument is optional. It is the future value, or the balance that you want to have left after the last payment. If fv is omitted, the fv is assumed to be zero.
  • Type is optional. If omitted, it is assumed to be zero, and payments are due at the end of the period. Use 1 in this argument if payments are due at the beginning of the period.

PMT Traps

• The payment calculated by PMT includes principal and interest but does not include taxes, or other fees that might be associated with the loan.
• Canadian mortgage payments have the interest compounded bi-annually, even if the payments are made monthly. The Rate argument must be adjusted to account for this

Example 1: Calculate Payment on Personal Loan

To 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:

• Amount of loan is $10,000
• The annual interest rate is 5%
• The loan is for a 4 year term, with 48 monthly payments

In cell C6, the Excel PMT formula 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):

• =PMT(C2/12,C3,C4)

With the above formula, the monthly loan payment, -230.29, is calculated as a negative value, because you are paying that amount out of your bank account.

If you would prefer to see the result as a positive value, you can use a minus sign before the PMT function:

• =-PMT(C2/12,C3,C4)

Total Amount Paid

If 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.

• =C6*C3

This formula multiplies:

• monthly payment in cell C6
• by number of payments in cell C3

Example 2: Calculate Payment on Canadian Mortgage

For 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:

• The mortgage loan amount is $100,000
• The interest rate is 5% annually, compounded semi-annually
• The loan is for a 20 year term, with 240 monthly payments

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

• (Rate /2 +1) is the semi-annual interest as a proportion of the annual rate. In this example, the rate is 5/2 = 2.5% each 6 months. So at the end of 6 months you owe 1.025 of what you owed at the beginning.
• Payments are monthly, and there are 6 months in a half year, so the proportional rate is raised to the power of 1/6. In this example, the monthly rate is 1.025 ^(1/6)=1.00412391547
• The 1, that was added for the rate calculation, is subtracted

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 Calculator

In 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 Calculations

In 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.

• =-PMT(LoanRate/12,LoanMths,LoanAmt)

Payment Date Table

The 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 Formulas

Here are the formulas used in row 7 of the payment date table:

• Pay Date: =IF(G7="","", EDATE(LoanStart,G7-1))
• Outstanding: =IF(G7="","",LoanAmt-SUM(E$6:E6))
• Mth Pmt: =IF(G7="","",LoanPmt)
• Interest: =IF(G7="","",-IPMT(LoanRate/12,G7,LoanMths,LoanAmt))
• Principal: =IF(G7="","",C7-D7)
• Total Princ Paid: =IF(G7="","",SUM(E$6:E7))
• Pmt Num: =IF(MAX(G$6:G6)<LoanMths,SUM(G6,1),"")

Last Payment Highlighting

In the table, the latest payment row is highlighted, based on a Conditional Formatting rule

• =$A7=INDEX($A$7:$A$54,MATCH(TODAY(),$A$7:$A$54,1))

In the Conditional Formatting rule:

• MATCH looks for the current date (TODAY function) in the payment date list
• If the current date is not found, MATCH returns the location of the latest date before the current date
• Then, the INDEX function returns the date from that location in the list of payment dates
• If the date in the current row matches that date, the row is highlighted with light orange

Get the PMT Function Files

• Examples 1 & 2: To see the formulas used in examples 1 and 2, get the PMT function sample workbook. The file is zipped, and is in xlsx file format.

• Example 3: To see the enhanced loan calculator, get the Excel Loan Payment Calculator sample workbook. The file is in xlsx format, and zipped. The calculator uses the IFERROR function, which was introduced in Excel 2007.
• Example 4: To see the Payment Date Calculations Table, get the Excel Monthly Payment Dates sample workbook. The file is in xlsx format, and zipped.

Excel Functions Tutorials

Payment Calculation Table

Loan Payment Schedule Template

Data Table Examples

Cost Calculator, Annual

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Function

INDIRECT Function

Excel Function Video Tutorials