How to Use the PMT Function in Excel (With Examples)
Are you up for borrowing? Before you do, make sure you know loan payments are worked out 💰
And Excel financial functions are here to help you with that. One of these, the PMT function will help you calculate periodic loan payments at different interest rates.
The article below will help you know all about the PMT function. So stay tuned and continue reading.
Make sure you download our free sample workbook here to tag along with the guide 📩
Table of Contents
What is the PMT function in Excel?
Just like the name suggests – PMT stands for payments 💸
The PMT function of Excel calculates the payments of a loan based on an interest rate, loan amount, and the number of periods.
For example, if you take an auto loan of $50,000 at an annual interest rate of 10%, and you have 4 years to repay – how much amount will you repay annually?
The PMT function will tell you that:
That’s an annual installment of $15,773. How did we calculate it? We will learn that only in the next section🚴♀️
How to use the PMT function
Using the PMT function in Excel is super easy!
If you are about to sign up for a loan of $100,000 for 5 years at an annual interest rate of 15%, what are you going to pay annually?
To find that:
- Write the PMT function as below:
= PMT (
- Specify the interest rate as the first argument.
= PMT ( B2
Our loan bears a constant interest rate of 15%, so that goes in as our rate argument as Cell B2 👌
If you are not creating a reference to a cell but writing it in the function manually:
Do not forget to suffix the rate with a percentage sign (%). Or else, you can write the rate in decimals. For example, without a percentage sign, 15% becomes 0.15.
Kasper Langmann, Microsoft Office Specialist- Mention the number of periodic payments to be made as the next argument.
= PMT ( B2, B3
The loan extends for up to 5 years. And as we are making annual repayments of the loan, our number of periodic payments is also 5 📆
We have created a reference to cell B3 that contains 5.
Pro Tip!
Must know that the Nper argument stands for the Number of Periodic payments and not the period of the loan 🔢
This means that as the Nper argument, you must mention the number of installments to be paid against the loan (can be monthly or quarterly payments too).
Like you might have to repay the entire loan in 5 years, but your periodic installment might fall due every month. In this case, your Nper would be 60 (12 monthly payments per year for 5 years).
- Write in the amount of your loan.
= PMT (B2, B3, B5)
Our loan amounts to $100,000, and that goes into our function.
The amount of the loan goes as a simple number (if you are writing it inside the function instead of referring to it). So there’s no need to enclose it in double quotation marks.
Kasper Langmann, Microsoft Office SpecialistThat’s all about the required functions of the PMT function.
- Hit Enter, and you’re all set.
So you have to pay $29,831 each year for 5 years. The PMT function has it all sorted 💪
Brain Teaser: So how much are you paying against this loan in total? That’s $149,157 ($29,831 * 5 periodic installments). This also means that you have to pay $49,157 in interest ($149,157 – $100,000).
Pro Tip!
There are two optional arguments to the PMT function too. What do they do?
FV – This means any future value of the loan that would be left back after the last payment is made.
Type – There can be two annuity types.
- 0 (annuity due): this is when the payments are to be made at the end of each period.
- 1 (annuity advance): this is when the payments are to be made at the beginning of each period.
Since they are optional, if any of these arguments are omitted, Excel by default sets them to 0.
So, to take our example a step ahead, if we assume, the loan is only to be repaid up to $85,000. This means $15,000 would be the future value (left back in the end).
- In this case, the PMT function would change as follows:
= PMT (B2,B3,B5,B6]
We have simply created an additional reference to Cell B6 (that contains the future value) as the fourth argument.
- Hit Enter to see the results change as follows:
And now the payments are $32,056 per month.
- Similarly, if you pay each loan installment in advance, write the PMT function as follows:
= PMT (B2,B3,B5,B6,1)
You’d see this happening in the leasing transactions🚔
Where you lease out a car today (and hence the loan starts today), and you pay an installment right away. Similarly, each installment is then paid at the beginning of the period.
Kasper Langmann, Microsoft Office Specialist- Hit Enter and here come the results.
That’s it – Now what
That’s all about the PMT function of Excel. Starting from the syntax of this function to a comprehensive example of its practical application – the above guide discusses it all.
PMT function is a gift for finance people. It simplifies complex financial calculations like anything. But mind the fact that no matter how useful it might be, it is only one small function of Excel 🤏
And there are just so many more functions to Excel. To master Excel functions, you better take the road from simpler Excel functions.
Like the VLOOKUP, SUMIF, and IF functions. Learn these (and many more) functions of Excel by enrolling in my 30-minute free email course here.
Other resources
If you liked reading about the PMT function, you’d for sure love other financial functions of Excel too.
Try reading about the IRR function of Excel. And oh, you can also create an Excel loan payment calculator – learn here how.