How to use PMT function in Excel with formula examples

What is the PMT function in Excel?

The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount.

"PMT" stands for "payment", hence the function's name.

For example, if you are applying for a two-year car loan with an annual interest rate of 7% and the loan amount of $30,000, a PMT formula can tell you what your monthly payments will be.

For the PMT function to work correctly in your worksheets, please keep in mind these facts:

• To be in line with the general cash flow model, the payment amount is output as a negative number because it's a cash outflow.
• The value returned by the PMT function includes principal and interest but does not include any fees, taxes, or reserve payments that may be associated with a loan.
• A PMT formula in Excel can compute a loan payment for different payment frequencies such as weekly, monthly, quarterly, or annually. This example shows how to do it correctly.

The PMT function is available in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

Excel PMT function - syntax and basic uses

The PMT function has the following arguments:

Where:

• Rate (required) - the constant interest rate per period. Can be supplied as percentage or decimal number.

  For example, if you make annual payments on a loan at an annual interest rate of 10 percent, use 10% or 0.1 for rate. If you make monthly payments on the same loan, then use 10%/12 or 0.00833 for rate.

• Nper (required) - the number of payments for the loan, i.e. the total number of periods over which the loan should be paid.

  For example, if you make annual payments on a 5-year loan, supply 5 for nper. If you make monthly payments on the same loan, then multiply the number of years by 12, and use 5*12 or 60 for nper.

• Pv (required) - the present value, i.e. the total amount that all future payments are worth now. In case of a loan, it's simply the original amount borrowed.
• Fv (optional) - the future value, or the cash balance you wish to have after the last payment is made. If omitted, the future value of the loan is assumed to be zero (0).
• Type (optional) - specifies when the payments are due:
  • 0 or omitted - payments are due at the end of each period.
  • 1 - payments are due at the beginning of each period.

For example, if you borrow $100,000 for 5 years with an annual interest rate of 7%, the following formula will calculate the annual payment:

=PMT(7%, 5, 100000)

To find the monthly payment for the same loan, use this formula:

=PMT(7%/12, 5*12, 100000)

Or, you can enter the known components of a loan in separate cells and reference those cells in your PMT formula. With the interest rate in B1, no. of years in B2, and loan amount in B3, the formula is as simple as this:

=PMT(B1, B2, B3)

Please remember that the payment is returned as a negative number because this amount will be debited (subtracted) from your bank account.

By default, Excel display the result in the Currency format, rounded to 2 decimal places, highlighted in red and enclosed in parenthesis, as shown in the left part of the image below. The image on the right shows the same result in the General format.

If you'd like to have the payment as a positive number, put a minus sign before either the entire PMT formula or the pv argument (loan amount):

=-PMT(B1, B2, B3)

or

=PMT(B1, B2, -B3)

How to use PMT function in Excel - formula examples

Below you will find a few more examples of an Excel PMT formula that show how to calculate different periodic payments for a car loan, home loan, mortgage loan, and the like.

How to make a PMT calculator in Excel

Before you go ahead and borrow money, it stands to reason to compare different loan conditions to find out the options that suit you most. For this, let's create our own Excel loan payment calculator.

1. To begin with, enter the loan amount, interest rate and loan term in separate cells (B3, B4, B5, respectively).
2. To be able to choose different periods and specify when the payments are due, create drop-down lists with the following predefined options (B6 and B7):
   
3. Set up the lookup tables for Periods (E2:F6) and Payments are Due (E8:F9) like show in the screenshot below. It is important that text labels in the lookup tables exactly match the items of the corresponding drop-down list.
   In the cells next to the drop-down lists, enter the following IFERROR VLOOKUP formulas that will pull the number from the lookup table corresponding to the item selected in the drop-down list.

   Formula for Periods (C6):

   =IFERROR(VLOOKUP(B6, E2:F6, 2, 0), "")

   Formula for Payments are Due (C7):

   =IFERROR(VLOOKUP(B7, E8:F9, 2, 0), "")

   

4. Write a PMT formula to calculate the periodic payment based on your cells. In our case, the formula goes as follows:

   =IFERROR(-PMT(B4/C6, B5*C6, B3, 0, C7), "")

   Please notice the following things:

   • The fv argument (0) is hardcoded in the formula because we always want zero balance after the last payment. In case you wish to allow your users to enter any future value, allocate a separate input cell for the fv argument.
   • The PMT function is preceded with the minus sign to display the result as a positive number.
   • The PMT function is wrapped into IFERROR to hide errors when some input values are not defined.

   The above formula goes in B9. And in the neighboring cell (A9) we display a label corresponding to the selected period (B6). For this, simply concatenate the value in B6 and the desired text:

   =B6&" Payment"

   

5. Finally, you can hide the lookup tables from view, add a few finishing formatting touches, and your Excel PMT calculator is good to go:
   

Excel PMT function not working

If your Excel PMT formula is not working or produces wrong results, it's likely to be because of the following reasons:

• A #NUM! error may occur if either the rate argument is a negative number or nper is equal to 0.
• A #VALUE! error occurs if one or more arguments are text values.
• If the result of a PMT formula is much higher or lower than expected, make sure you are consistent with the units supplied for the rate and nper arguments, meaning you have correctly converted the annual interest rate to the period's rate and the number of years to weeks, months, or quarters as shown in this example.

Practice workbook for download

PMT formula in Excel - examples(.xlsx file)