PMT is an important function in finance, especially when it comes to calculating the payments for loans. No matter what type of business you work within, you might hear the acronym PMT used in the office. But what does PMT stand for in finance, where would you use it and how can you calculate it?
What is PMT in finance?
PMT is an abbreviation of the word ‘payment’. In finance, PMT is a function which is used to calculate payments which are due at specific frequencies, for example loans or mortgages. In order to use the PMT function, the interest rate will need to be constant, and the payments will need to be level.
The PMT function is often used by businesses to calculate their monthly repayments on a business loan, or to calculate payments due to the business from customer finance deals.
When is the PMT function used?
If you need to calculate the future payments of a loan, you will probably be using the PMT function, helping show the cash required for monthly debt obligations. However, this assumes that the interest rate and payments are constant over time.
For example, if you are taking out a business loan of £25,000 to be paid over 3 years at an annual interest rate of 4%, you could use the PMT function to discover what your monthly payments would be. This would allow you to calculate that the monthly repayments for your business loan would be £737.30 per month, with a total repayment figure of £26,542.97. This kind of result helps businesses judge affordability and plan monthly cash needs.
Some of the purposes for which the PMT function can be used include:
Calculate mortgage repayments
Calculate car loan repayments
Calculate personal loan repayments
PMT is also used for investment planning to work out how much to save or invest each month to reach a future value or target return.
How to use the PMT function in Excel
Microsoft Excel incorporates a PMT function to automatically calculate loan repayments. This allows users to quickly and easily calculate repayment sums, without needing to perform any complex manual calculations.
Before we take a look at the format of the PMT calculation performed by Excel, there’s a few variables that we need to understand: the following arguments are rate, nper, pv, fv and type.
‘Rate’ is the interest rate of the loan, which is typically given as an annual percentage rate, or APR. If you are making payments on an annual basis, you can simply type in the APR here. However, if the payments are being made on a monthly basis, you’ll need to divide the APR by 12 to find the monthly rate. In the excel formula, this means using the monthly interest rate by dividing the annual rate by the number of payment periods per year, which assumes a fixed interest rate and a constant interest rate.
‘Nper’ is an abbreviation for the number of periods – that is the number of repayments that will be made during the term of the loan. For example, if you will be paying for a loan monthly over 5 years, the number of repayment periods will be 60, so you would enter 60 as the nper.
‘PV’ stands for present value, meaning the loan principal amount or total amount borrowed today.
‘FV’ stands for future value, or the cash balance you want remaining after the last payment.
‘Type’ shows whether periodic payments are due at the end of a period or at the beginning.
Now that we understand the variables used within Excel’s PMT calculation, let’s move on to looking at the format of the formula.
The format used by Excel to input data for the PMT calculation uses the following parameters and following arguments in the PMT function:
=PMT(rate,nper,pv,fv,type)This is the formula that you will need to type into the formula bar of Excel to calculate PMT. The syntax rate nper pv fv type, sometimes written as nper pv fv type, helps determine constant payments or regular payments, and these following parameters affect payment timing, the loan term, and the total payment result.
How to calculate PMT manually
It isn’t easy to calculate PMT manually. In fact, it is far quicker to calculate PMT using either Microsoft Excel or a financial calculator. However, if you are interested in the mathematical formula which is used by both Excel and financial calculators to calculate PMT, here it is:
PMT = pv*apr/12*(1+apr/12)^(nper*12)/((1+apr/12)^(nper*12)-1)
As you can see, the formula for calculating PMT manually is complex, meaning that it is easy to make an error during your calculation. For this reason, we would suggest leaving the PMT calculations to technology on this one!
Related questions
What is the formula for calculating loan payments?
The formula that you will need to use to calculate constant payments for a loan is known as PMT. The easiest way to calculate PMT is by using the PMT function in Microsoft Excel or by using a financial calculator to work out regular or periodic loan repayments. There are also many online tools which allow you to calculate monthly loan repayments, but you should always take steps to ensure that the website you are using is genuine and trustworthy before relying on the figures that it generates.
Why is Excel PMT negative?
When you use Microsoft Excel to calculate PMT, you will notice that it returns a negative value. This is because it represents payments which are being made from yourself to the lender. If you would rather that the PMT function generates a positive value, you will need to enter the loan amount (the PV variable) as a negative figure.
Where is the PMT function in Excel?
The PMT function is built into Microsoft Excel as default. This means that you can simply type the PMT function into the formula bar to begin the calculation. Alternatively, you can press the “FX” button next to the formula bar and type “PMT” into the search bar. The dialogue box will then talk you through the variables you need to input—rate, nper, pv, fv, and type—to calculate PMT in Excel, with the type setting showing whether payments are made at the beginning or end of the period.
Use PMT to calculate monthly loan repayments
PMT is an extremely useful tool in finance, especially in financial analysis and corporate finance, which is used for calculating loan repayment figures. The formula for calculating PMT is complex and leaves a lot of space for mistakes, though it can also support investment planning. However, Microsoft Excel offers a straightforward and fast way to calculate PMT automatically, with no manual calculations required, although taxes may affect the real cost or return beyond the basic result.
PMT is an important function in finance, especially when it comes to calculating the payments for loans. No matter what type of business you work within, you might hear the acronym PMT used in the office. But what does PMT stand for in finance, where would you use it and how can you calculate it?
