PMT Function

The PMT function is an advanced Excel formula and one of the financial functions used to calculate the monthly payment amount against the simple loan amount. You have to provide the function of basic information, including loan amount, interest rate, and payment duration, and the function will calculate the payment as a result. The payment amount calculated by this PMT Excel formula returns the amount without taxes, reserve payments, and fees (sometimes associated with loans).

Five parameters are used in this PMT Excel function. In which three are compulsory and two are optional.

Compulsory Parameter:

1. Rate: Rate represents the interest rate for the loan amount. If you are making a monthly payment in this PMT Excel function, then you should convert the rate at a monthly rate and the nper in a month too.
2. Nper: Nper is the total number of installments for the loan amount. For example, considering the 5 years terms means 5*12=60.
3. Pv: Pv is the total loan amount or present value.

Optional Parameter:

1. : It is also called the future value. It is optional in this PMT Excel. If not passed in this function, it will be considered zero.
2. : It can be omitted from PMT fn and used as 1 in case payments are due at the beginning of the period and considered as 0 in case the payments are due at the end of the period.

This function is very simple. Let us now see how to use this PMT Excel function with the help of some examples.

Example #1

Suppose the loan amount is 25,000, the interest rate is 10% annual, and the period is 5 years.

Here, the number of payments will be =5*12=60 payments in total.

In this PMT Excel, we have considered C4/12 because a 10% rate is annual. By dividing by 12, we get the monthly rate. Here, the future value and type are considered zero.

The output will be:

• It will return the value in a negative amount as this amount will be credited from your bank.

• Use the negative sign before this PMT Excel to convert it into a positive value.

Then the output will be $531.18.

• You can also change the currency type to change the format of the cell.

Select the cell, convert the format, and select the currency type per your requirements.

Example #2

We can use it to calculate the payments on a Canadian mortgage.

Suppose the loan amount is 25000, the annual interest rate is 10%, compounded semiannually, and the period is 5 years.

Here, the annual rate/2+1 is the semiannual interest in the annual rate, and the rate is 10/2=5% every 6 months. The rate is a power of (1/6) for monthly and semiannual payments.

Example #3

The PMT formula in Excel can be used as an automatic loan calculator.

In the previous example, we calculated the monthly payment by providing the loan amount, interest rate, and number of payments. In an automatic loan calculator, we are using the annual rate, time, and frequency of payments.

Before understanding the automatic loan calculator, create a list for calculating the number of payments. 

Using the list, we can calculate the numbers of payments within a year by using a simple VLOOKUP from the list, which is 26 bi-weekly.

For the total number of payments, multiple it from the total number of years,=3*26=78.

Here, we again took the annual rate of 5% and the loan amount as 25000.

Then the PMT function in Excel looks like: =-PMT(C30/E31,E32,C33) and output will be $346.74.

Below are the few error details that can come in the PMT formula in Excel if the wrong argument is passed in the functions.

#1 - Error handling #NUM!: If the Nper value is 0, it will throw a #NUM! Error.

#2 - Error handling #VALUE!: The result will be a #VALUE! Error when any non-numeric value has been passed in the PMT function formula.

In this function, a monthly amount and rate should be considered in a month.

3. It would help if you converted the monthly or quarterly rate per your requirement.
4. The payment amount calculated by this PMT Excel function returns the amount without taxes, reserve payments, and fees (sometimes associated with loans).