How To Use The PMT Function In Excel
– Free Exercise File Included!
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
The PMT function — which stands for Payment — is one of Excel’s financial functions.
It is an advanced Excel formula that calculates the periodic loan payment based on the constant interest rate, number of payments, and the loan amount.
In simple terms, this function outputs a payment amount for your loans assuming they have a constant interest rate and a constant number of payments.
Don’t let the “advanced” word scare you! We’ll be slicing the PMT function into digestible bits in this article.
Let’s get started!
Table of Content
The PMT Syntax and Its Parameters
The key to understanding a function is knowing its parameters or arguments and what they mean.
The formula for PMT is as follows:
The PMT Syntax:
=PMT(rate, nper, pv, [fv], [type])
For sure, it looks a bit complicated. But it’s actually easier than it looks. 😉
There are 5 parameters in the syntax. 3 of them (the first 3) are required to run the function while the last 2 are only optional.
Here are the required arguments:
- Rate (rate) is the interest rate of the loan. This can be in percentage or in decimal number.
- Nper is the number of payments (or installments) for the loan.
- Pv (Present value) is the principal. This is the total loan amount.
These are the optional arguments:
- Fv (Future value) represents the amount you wish to have after making the last payment. When not included in the formula, the value is zero (0).
- Type represents the annuity type or when the payments are due — the end (0) or at the beginning (1) of a period. When omitted, it is assumed to be zero (0).
By default, the function’s output is a negative number in the currency format, rounded to 2 decimal places. Excel returns an amount in a negative number to be in line with the cash flow model.
The PMT function only includes the items described above. That means taxes and other fees are not included in the calculation.
Now that you know the basics, let’s try and use the formula in a simple loan payment calculation.
Using the PMT Function
Let’s say you borrowed $50,000 which is paid annually for 5 years with an annual interest rate of 5%.
- ‘rate’: 5%
- ‘nper’: 5
- ‘pv’: $50,000
From this, you’ll be able to come up with the PMT formula right away:
=PMT(5%, 5, 50000)
Of course, you can simply use the formula above and write it on Excel right away. However, the reason you’re using Excel is to see all the relevant data and be able to change the associated elements without having to rewrite the formula.
So in this case, we’ll be referencing the cells of the associated arguments — B2, B3, and B4. The formula will then become:
=PMT(B2, B3, B4)
Once you press ‘Enter’, you’ll then see your annual loan payment.
That’s it! See how easy using the PMT function is for a simple loan calculation?
This time, we’ll take it a notch higher by applying all the parameters of the formula.
Using the Whole PMT Formula
To use the whole formula, we need to add the optional values.
For the future value, of course, we’d like the loan to be fully paid! So we’ll use zero (0). For the annuity type, let’s assume the payment is due at the beginning of each period (start of the year).
- ‘fv’: 0
- ‘type’: 1
With the addition of the optional values, the formula now becomes:
=PMT(5%, 5, 50000, 0, 1) or =PMT(B9, B10, B11, B12, B13)
Press ‘Enter’ to run the formula and see your annual loan payment.
Of course, payments aren’t always paid annually. That’s why it’s also important to know how to calculate the weekly, monthly, quarterly, and semi-annual payments.
PMT in Various Payment Frequencies
When calculating the payment for different payment frequencies, you would need to adjust the ‘rate’ and ‘nper’ arguments.
For the ‘rate’, all you have to do is to divide the annual interest rate by the number of payments per year.
- Weekly: ‘rate’/52
- Monthly: ‘rate’/12
- Quarterly: ‘rate’/4
- Semi-annual: ‘rate’/2
Because ‘nper’ represents the number of payments, all you have to do is multiply the number of years by the number of payments per year.
In our example, you have 5 years to pay the loan.
- If you’re paying weekly, then it’s 5*52
- Monthly: 5*12
- Quarterly: 5*4
- Semi-annual: 5*2
When applied to the formula:
- Weekly =PMT(5%/52, 5*52, 50000)
- Monthly =PMT(5%/12, 5*12, 50000)
- Quarterly =PMT(5%/4, 5*4, 50000)
- Semi-annual =PMT(5%/2, 5*2, 50000)
Here’s how it looks in Excel:
That wasn’t so hard, right?
If you feel like you understand the formula and the changes in ‘rate’ and ‘nper’, you’re now ready to make an advanced PMT calculator in Excel.
Advanced PMT Calculator in Excel
To make things easier, we’ll still be using the same values in our example.
The difference with this calculator is that we are able to change the period of payment (weekly, monthly, etc…) and also the annuity type via a dropdown.
To use a dropdown, you need to enter the predefined options for the different payment periods and the annuity type.
Once you have the options defined, you can now add a dropdown for the “Periods” and “Payment Due”.
After that, use a VLOOKUP function to match what’s on the dropdown with their value.
Here are their formulas:
- Periods =VLOOKUP(F5,I3:J7,2,0)
- Payment Due =VLOOKUP(F6,I10:J11,2,0)
The last step is writing the PMT formula:
=PMT(F2/G5, F3*G5, F4, 0, G6)
Remember, this formula is similar to the one we’ve used in the previous section.
If you want to add a label for your output that changes according to the period chosen, simply use a concatenate function:
=CONCAT(F5,” Payment”) or =F5 & ” Payment”
Once you’ve taken care of that, congratulations! You now have an advanced loan payment calculator.
Errors Using the PMT Function
Failures are part of learning, aren’t they? 😊
If you tried making your own calculator, there might be a few errors you’ll encounter:
- #N/A happens you referenced an empty cell(s). To resolve this, check the arguments of the formula and make sure they’re pointing to the right cell.
- #NUM! means you’ve used an invalid numeric value. Most likely, the ‘nper’ value of your PMT formula is zero (0).
- Lastly, #VALUE! happens when you’ve used a ‘text’ format value instead of a numeric one. Check for commas (and other typos) on your values.
It’s normal to commit mistakes here and there. What’s important is you learn from them.
Wrapping things up…
With the PMT function, you don’t have to rely anymore on the tools of others. You can simply create your own loan calculator and add all the functionalities you want.
Although the PMT function is considered an advanced formula, you can attest how it easy it is to use one.
If you want to master the PMT function, all you have to do is practice again and again. Use real life examples and values so you would be able to appreciate it more.