PMT Function excel is a very useful tool to calculate, equated payments either in investments or financing scenarios. In this article, you will get the full understanding of how to use it flawlessly.
What is the PMT function in Excel?
The PMT function is categorised under financial Excel functions. This function helps in calculating the total payment required to settle a loan or an investment with a fixed interest rate over a specific time period. It can be used as a worksheet function (WS) and a VBA function in Excel. The PMT function can be entered as a part of the formula in a cell of a worksheet.
How does the PMT function Excel work?
PMT Excel Syntax:
PMT ( rate, nper, pv, [fv], [type] )
- Parameters:
- Rate – The interest rate for the loan
- Nper – The number of payments for the loan
- PV – The present value or principal of the loan
- FV – It is the future value of the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes an FV value of 0
- Type – It indicates when the payments are due. If this parameter is omitted, it assumes a Type value of zero. If the Type value is, one, payments are due at the beginning of the period, and if the Type value is zero, the payments are due at the end of the period.
- Returns:
The PMT function returns a numeric value
- Applies to:
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Example: SIP using PMT Formula in Excel
Let’s assume that we need to invest in such a manner that, after two years, we’ll receive $75,000. The rate of interest is 3.5% per year and the payment will be made at the start of each month. The time period is entered in months so 2 years are 2*12=24 months. The details are:
The formula used is:
We get the results below:
Using PMT for Loan Cases
Now, let’s look at how we can use the PMT function in excel, to calculate EMIs on loans. So, first understand that a loan let’s say INR 50,00,000/-, with an interest rate of 9% per annum and a loan tenure of 20 years.
So, firstly always remember that, since we are calculating loans on a monthly basis, we need to convert the following to monthly rates.
- Rate of 9%, convert to monthly by dividing it by 12 months. i.e., 0.75%
- Period of 20 years also needs to be converted to 240 months.
Finally, let’s get this all together into the PMT function excel.
So, the EMI should be INR -44986 Per month.
Important Points to Remember
So, it’s crucial to remember some basic pointers about PMT function excel.
- PMT sign is the opposite of the PV sign. So if it’s an investment or EMI then PMT has to put in a negative sign.
- Also, PMT can only calculate values for equal cashflows only.
- PMT will throw an error if PV & FV are both positive.
Full form of PMT In Excel
Many times, students have the query that what exactly is the full form of PMT.Pmt actually stands for the simple word “ Payment”, signifying recurring payments over a period of time.