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.SLN 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
There are five parameters that are used in this PMT excel function. In which three are compulsory, and two are optional.
- 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.
- Nper: Nper is the total number of installments for the loan amount. For example, considering the 5 years terms means 5*12=60
- Pv: Pv is the total loan amount or present value.
- [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.
- [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.
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.
The output will be:
- This will return the value in a negative amount as this amount will be credited from your bank.
- To convert it into a positive value, just use the negative sign before this PMT excel.
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.
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 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.
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.
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.
- Error handling #NUM!: If the Nper value is 0, then it will throw a #NUM error.
- 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.
- You need to convert the rate to a monthly rate or quarterly as per your requirement.
- The payment amount calculated by this PMT excel function returns the amount without taxes, reserve payments, fees (sometimes associated with loans).
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.