PMT Function in Excel
The 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. 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).
Table of contents
PMT Function Formula
Five parameters 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 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. It is optional in this PMT Excel. If not passed in this function, it will be considered zero.
- [Type]: 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.
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, 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 bankCredited From Your BankBank credit is usually referred to as a loan given for business requirements or personal needs to its customers, with or without a guarantee or collateral, with an expectation of earning periodic interest on the loan amount. The principal amount is refunded at the end of loan tenure, duly agreed upon, and mentioned in the loan covenant..
- 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.
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.
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.
Things to Remember
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.
- It would help if you converted the monthly or quarterly rate per your requirement.
- The payment amount calculated by this PMT Excel function returns the amount without taxes, reserve payments, and fees (sometimes associated with loans).
This article is 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.
- Excel PPMT FunctionExcel PPMT FunctionThe PPMT function in Excel is a financial function that calculates the payment for a given principal and returns an integer result. This function can be used to calculate the principal amount of an installment for any period.
- IPMT Function in ExcelIPMT Function In ExcelThe IPMT function calculates the interest to be paid on a given loan with constant interest and periodic payments. It is a financial function that computes the interest portion of a payment for a given period.
- Excel NPER FunctionExcel NPER FunctionNPER, commonly known as the number of payment periods for a loan, is a financial term and an inbuilt financial function in Excel that can be used to calculate NPER for any loan. This formula takes rate, payment made, present value and future value as input from a user.
- User-Defined FunctionUser-Defined FunctionUser Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.