PMT Function

PMT Function in Excel

PMT function is an advanced excel formula and one of the financial functionsFinancial FunctionsExcel is known for making complex formulas easy to use and apply. Most needed functions are 1.Future Value 2.FVSchedule 3.Present Value 4.Net Present Value 5.XNPV 6.PMT 7.PPMT 8.Internal Rate of Return 9.Modified Internal Rate of Return 10.XIRR 11.NPER 12.RATE 13.EFFECT 14.NOMINAL 15.SLNread more used to calculate the monthly payment amount against the simple loan amount. Simple, you have to provide the function of basic information, including loan amount, interest rate, and duration of payment, 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, fees (sometimes associated with loans).

PMT Function Formula

PMT Formula

Explanation

There are five parameters that 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 should convert 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. [Fv]: It is also called the future value, and it is optional in this PMT excel, and if not passed in this function, then it will be considered as zero.
  2. [Type]: It can be omitted from PMT fn and used as 1 in case if 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.

How to Use the PMT Function in Excel? (with Examples)

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

You can download this PMT Function Excel Template here – PMT Function Excel Template

Example #1

Suppose the loan amount is 25,000, and 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, and by dividing by 12, we get the monthly rate. Here future value and type are considered zero.

PMT Function (Example-1)

The output will be:

PMT Function (Example-1-1)
  • To convert it into a positive value, just use the negative sign before this PMT excel.
PMT Function (Example-1-2)

Then the output will be $531.18.

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

Just select the cell and convert the format and select the currency type as per your requirements.

PMT Function (Example-1-3)

Example #2

It can be used to calculate the payments on a Canadian mortgage.

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

Here annual rate/2+1 is the semiannual interest in terms of annual rate, and the rate is 10/2=5% for every 6 months. As payments are monthly and payments are semiannual, so the rate is a power of (1/6).
PMT Function (Example-2)

Example #3

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 a number of payments. In an automatic loan calculator, we are using annual rate, time, and frequency of payments.

Before understanding the automatic loan calculator, create a list for calculation of the number of payments i.e.

PMT Function (Example-2-1)

By using the list, we can calculate the numbers of payments within a year by using simple vlookup from the list. i.e., 26 for Bi-weekly.

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

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

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

PMT Function (Example-3)

Things to Remember

Below are the few error details that can come in PMT formula in excel as the wrong argument will be passed in the functions.

  1. Error handling #NUM!: If the Nper value is 0, then it will throw a #NUM error.
PMT Function (Example-4)
  1. Error handling #VALUE!: the result will be a #VALUE! Error when any non-numeric value has been passed in the PMT function formula.
PMT Function (Example-4-1)

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

  1. You need to convert the rate to a monthly rate or quarterly as per your requirement.
  2. The payment amount calculated by this PMT excel function returns the amount without taxes, reserve payments, fees (sometimes associated with loans).

Recommended Articles

This has been a guide to PMT Function in Excel. Here we discuss the PMT Formula and how to use the PMT function along with practical examples and downloadable excel templates.

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>