Excel PMT Function

The PMT Excel function is a financial function that returns the periodical payment amount for a loan. Excel users use the PMT function to figure out the loan payment based on a steady interest rate, the loan amount and the number of cycles for paying the loan.

The implementation of the function is simple. You need to enter the PMT() function followed by the interest rate divided by 12 (as 1 year has 12 months), the Term (cycle of monthly instalments), followed by the Loan Amount.

For example, suppose you are taking a loan of Rs 5,00,000 for 60 months with an annual interest rate of 7.8 per cent. In that case, PMT can help you calculate your monthly instalments easily, and it even finds out how much principal amount and interest amount you are paying each month.

Important Points:

1. The payment returned by the PMT function includes the principal amount and interest rates, but it does not involve taxes, reserve payments, or fees sometimes charged extra with loans.
2. Always be consistent while specifying the rate and nper arguments. Let’s suppose you are making a monthly payment for a five-year loan at an interest rate of 8 per cent annually. Always use 8%/12 to calculate the rate parameter and 5*12 to calculate the nper parameter.

Syntax

Parameters

1. Rate (required)– This argument represents the interest rate for the specified loan amount.
2. Nper (required)– This parameter represents the total number of payments for the loan.
3. pv (required)– It indicates the total loan amount.
4. fv – [optional] – This parameter represents the pending cash value after making the last payment. Since it is an optional parameter, therefore, the default value is 0.
5. type – [optional] – It indicates the type of payments that are due. It takes two values,
   • 0 indicates that the payments are made at the end of the period.
   • 1 indicates that the payments are made at the beginning of the period.

   Since it is also an optional parameter, therefore, the default value is 0.

Let’s understand the use of the PMT function in Excel with the help of real-world examples.

Example 1

In the below example, we have lent a loan for $80,000 for two years from an xyz company. The interest rate is 4.5% per year, and the loan instalment will be made at the start of each month. Fetch the monthly payment using the PMT function.

The formula used is:

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

We get the following values below:

Amount = 80000

Time (nper) = 2 * 12 = 24

Rate of Interest: 4.5%

[type] = At the beginning of each period = 1

Applied Formula:

=-PMT (E4/12, E5, E3)

• You will make the investments every month. Therefore, we have converted the given years into months, i.e.,
  2 years x 12 (a year has 12 months)= 24 months.
• In the above description its already been mentioned that the payment will be initiated at the beginning of each period. Hence the [type] argument would be 1.
• As per the general cash flow convention, outgoing payments are represented by negative numbers, and positive numbers represent incoming payments.
• The output value is negative because it’s an outgoing payment.
• The fetched value holds the principal amount and the interest rate but no external taxes, stock payments, or fees.

Example 2: Excel PMT Calculator

Lending money from a bank is one of the common practices that is done in today’s world. However, different banks are offering different loan options. Before you go ahead and borrow money, it’s wise to calculate all the different loan amounts to find a suitable option for you. For this, we have created our loan payment calculator in Excel.

Calculator Fields

• We will enter five different fields of our PMT formula (loan amount, Annual Interest rate, Term, Periods, Payments are due) in separate cells (B5, B6, B7, B8, B9, respectively) of our Excel worksheet.
• In the first field, provide the loan amount.

• In the second and third fields, we will enter the Annual interest rate and Term period valid for our loan.

• With the Periods field, we will specify how the loan amount is to be paid. It can be Monthly (12), Weekly (52), Quarterly (4), Semi-annual (2), and Annual (1).
• The last field, ‘Payments are due,’ specify when the payments are due. There are two options, a.) Payments are due at the end of each period (represented by 0), and payments are due at the beginning of each period (represented by 1).

Using PMT Formula

1. To begin with, the formula, supply the values to all the above fields based on your requirements.
2. Enter the below given PMT formula and calculate the periodic based loan payment.

Formula used:

=IFERROR(-PMT(C6/D8, C7*D8,C5,0,D9),”OOP! An error occurred”)

Some Important Points:

1. In the above formula, you will notice we have specified ‘0’ for the ‘fv’argument because we want to fetch zero balance after the last payment. Though, you are free to enter any different value as well.
2. Another strange thing is the minus sign entered in front of the PMT formula. Since it is an outgoing payment so the output will be fetched in negative numbers. Therefore we have used the minus symbol to convert the output as a positive value.
3. The PMT function is called inside IFERROR to hide errors if the user forgot to define some input values.

That’s it! Your loan calculator is ready. Now, within a minute, you can calculate the periodical loan amount for any loan option.

Reasons why an Excel PMT function not works

While working, there would occur situations when your PMT functions stop working or throw an unexpected error. It could be likely because of any of the following reasons:

• #NUM! error: This error may occur if the specified ‘nper’ parameter equals 0 or the user has passed a negative number in the ‘rate’
• #VALUE! error: This occurs if one or more specified parameters in the formula are text values.
• If the output of a PMT function is much higher or lower than the anticipated value, recheck that you are steady with the units supplied for the rateand nper parameters. Make sure you have accurately converted the annual interest rate to the period’s rate and years to numbers of weeks, number of months, or quarters.

Eureka! Now you learned how to calculate the PMT formula in Excel.