How does the PMT function Excel work?
If you are not an experienced Excel user, you may not have the least idea of what PMT function Excel is and how it works. This tool can be very helpful if you need to deal with finances and calculations.
Source: UGC
Excel has a number of advantages that its regular users often don't know about. Being a table processor, Microsoft Excel also provides several tools for economic and statistical calculations and even a range of graphics tools. Today, it is one of the most popular applications in the whole world.
The PMT function Excel explained
First, we need to focus on the main questions: what is PMT and what do we need it for in Excel?
The Excel PMT function is one of the financial tools that is meant for the calculation of periodic amounts paid back on a loan or some investment. To use the function, you need to provide the system with the interest rate on a particular loan and the number of periods of time required to pay the loan back.
The actual PMT meaning is the simple word ‘payment’.
The PMT function syntax
Source: UGC
It looks as follows:
PMT (rate, nper, pv, [fv], [type])
The first three arguments are required while the remaining two are optional. The formula will not work without the first three arguments while the remaining two may be omitted.
The arguments inside the PMT formula are explained like this:
- rate is the interest rate on a particular loan
- nper (‘number of payment periods’) is the number of payments in which the loan is supposed to be paid back
- pv (‘present value’) is the present value, also known as the principal. In other words, it is the whole amount or the whole worth of the series of future payments
- fv (‘future value’) is an optional argument. It is the rest of the cash you want to see after the loan is paid back completely by the last payment. You may freely omit this argument, and the system will automatically give it a zero value
- type is the remaining optional argument. If you give it the value of 0, it means that payments are due by the end of the determined period. If you give it the value of 1, the payments are due by the beginning of this period
READ ALSO: How to save a Word document as a PDF
Now, here are a couple of points to remember. The money amounts that are paid should be represented by negative numbers (for example, savings). Received amounts of money should be represented by positive numbers (for example, dividends). It is necessary to use the same units of measurement for the rate and nper arguments. You can use N%12 for the rate and N*12 for the nper (monthly payments); N%4 for the rate and N*4 for the nper (quarterly payments); and N% for the rate and N* for the nper (annual payments).
It is necessary to remember that the values you give the program for the calculation of a loan or an investment include everything but taxes or other additional payments associated with the investment or the loan.
The actual mathematical formula behind the PMT Excel function looks like this:
pv*((1+rate)^nper)+pmt(1+rate*type)*(((1+rate)^nper)-1)/rate)+fv = 0
If assumed that rate = 0, then (pmt*nper)+pv+fv = 0
If you are good at mathematics, you can try to use these formulas with any variables.
How to use PMT function in Excel
Source: UGC
You can use the PMT finance function directly to calculate the size of payments on a certain loan or an investment. If you need to compare several loans and choose the one with the most favourable conditions to you, it is even possible to create a calculator using Excel tools.
How to calculate PMT function in Excel
For instance, you may need to calculate the size of regular payments on a loan you are planning to take. There is a generic formula used for Excel that you can use.
Source: UGC
Create a table where the loan amount occupies cell C5, the interest rate stands in cell C6, the number of payment periods occupies cell C7, and the number of periods per year stands in cell C8.
The formula in C10 will look like =PMT(C6/12, C7,-C5).
This is, in a nutshell, what the PMT function Excel is and how you can take advantage of it. This tool can be extremely helpful if you need to make some calculations and do not want to waste time in a bank.
READ ALSO: How to insert checkbox in Word
Source: Legit.ng
