PMT is a financial function that calculates the periodic payment for a loan, mortgage or investment, with constant payments and interests rate and a defined period.
Syntax and arguments
The syntax of the function is PMT( rate, nper, pv, [fv], [type] ), see below for the breakdown of each argument:
- rate: This argument is the interest rate for the loan and refers to a cell
- nper: Is the total number of payments for the loan. For example, if I repay my loan on a monthly basis over 3 years, Nper will be 3 x 12 = 36
- pv: This is the present value of the loan. If no payment has been made yet, it will be the total value of the loan. However, if this formula is used for a loan that has already partially been paid, it will be the remaining of the loan.
- fv: Specifies the final balance at the end of all payments. This will be 0 most of the time.
- Note: this argument is optional. If left blank, the PTM formula will assume it is 0.
- type: this last argument determines whether the payment is made at the start of the period or at the end of it. “0” will mean it is made at the end of the period and “1”, at the beginning of it.
- Note: this argument is optional too and if left blank, it will be considered that the payment is being made at the end of the period.
The PTM formula may sound quite complex but is actually quite easy to understand if laid out in a more practical way.
Let’s consider the below scenario:
- Loan: $100,000
- Interest rate: 5%
- Periods: 48 (Frequency x Term)
The calculation for the monthly payment will be as below:
Tips and tricks
As shown in the example above, the result shows as a negative value because it is an amount paid from your account, a debit. To make it a positive result, simply add (-) in front of the formula and the number will be positive.
Beware of the frequency & make sure you are consistent with the unit of measure using your arguments. As you can see in the above example, I have divided the annual interest rate by 12 to obtain the “rate” argument, as I will be making 12 payments per year. The same way if I was to make quarterly payments, I would divide the annual rate by 4 to get my periodic annual rate. It is the same exercise with the nper argument, where I need to multiply the frequency with the term (number of years).
The PTM function does not include any taxes and other fees or charges. However quite often, loans or mortgages involve payment of taxes in some sort. This will need to be added to the payment per period.
To find out the total amount paid over the course of all periods, simply multiply the PTM value by the number of periods (nper).
If you need a Complete tool for calculating loan payments, you can download our Loan Amortization Calculator using the form below. Download our loan calculator now, and never face trouble with your loans again!
Did you like the article? Do you have any query or comments? Let us know in the comments down below.
Download our Loan/Mortgage Calculator Excel template and never struggle with loans again!
Enter your First Name & Email to get our Loan Calculator Template and subscribe to our Newsletter!
- Learn all about the new XLOOKUP Function in Excel! - 2019-09-27
- FOR EACH Loop in Excel VBA - 2018-12-15