Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 1) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!
PMT explained
Honest truth: you'll never really use the PMT function in anything other than finance.
And straight up, finance can be boring. So let's just bulldoze through this as fast as possible so we get 'er done.
PMT functions are primarily used with two financial instruments: loans and annuities. But within what aspect of these do PMT functions contribute?
The PMT function is used primarily with loans (to calculate periodical payments) and annuities (to calculate returns).
That's all you need to know for now. Let's jump into coding a PMT function with a loan!
Coding a PMT function (loan)
Given the following loan situation, let's code a PMT function to figure out our yearly payment.
Loan amount = $10,000
Interest rate = 6.2%
Periods (in years) = 5
To start, let's check out the PMT function template:
=PMT(rate, nper, pv, [fv], [type])
The rate is going to be the interest rate of the loan, which is 6.2%.
=PMT(0.062, nper, pv, [fv], [type])
The nper argument can be difficult to understand, but the main thing you need to understand about it is that it's the number of periods. In our case, we have 5 periods in years.
=PMT(0.062, 5, pv, [fv], [type])
The nper argument is the number of periods for the financial instrument.
The pv of our PMT function represents the present value of our loan, which is $10,000.
=PMT(0.062, 5, 10000, [fv], [type])
We will not place anything in [fv] or [type] for now since they're surrounded in brackets and therefore not required. We'll utilize them with annuities later.
When we plug out PMT function into Excel, we get the following result:
Our PMT function results in a yearly payment of $2,386.89 in order to pay off this $10,000 loan in 5 years on an interest rate of 6.2%!
In the case that you got "######"...
If you ever get "######" as a result, just expand the column width so that it can include the entire value.
PMT in months
Sometimes, you'll be given a loan that has monthly payments. This differs from what we did above, because we must modify the rate.
You must ensure that your rate and nper span the same amount of time. (Ex: you can't use a yearly interest rate if your number of periods are in months).
Let's modify the situation above to see this in action:
Loan amount = $10,000
Interest rate = 6.2%
Periods (in months) = 5 years x 12 months = 60
That's not all we need to modify though... our rate will no longer be 6.2% either. It will be 6.2%/12. By dividing our yearly interest rate by 12, we make it a monthly interest rate.
Loan amount = $10,000
Interest rate = 6.2%/12
Periods (in months) = 60
When we code this in Excel...
...we get a monthly payment of $194.26!
Coding a PMT function (annuity)
Using PMT with annuities enable us to calculate what payment we need to put into an investment account per period in order to reach a desired future value.
Annuities with PMT are payments into an investment account to grow to a desired future value over time. Loan payments are payments that continually chip away at a present value until its future value is zero, meaning the loan is successfully paid off.
Say we're starting a brand new investment account over the next 10 years that returns 3.5% yearly interest on our investments. Since the account is brand new, our present value (pv) would be 0, our rate would be 3.5%, and our nper would be 10.
Present value = $0
Interest rate = 3.5%
Periods (in years) = 10
As is, here's what our PMT function would look like:
=PMT(0.035, 10, 0)
However, we've forgotten the most important part: the goal of our end investment! For the sake of this example, let's assign that as $10,000.
Present value = $0
Interest rate = 3.5%
Periods (in years) = 10
Future value = $10,000
Now, we need to then assign the [fv] argument as 10000 like so:
=PMT(0.035, 10, 0, 10000)
If we throw in all this PMT function into Excel...
...we get an annual payment of $852.41.
What this means is that in order to accumulate a $10,000 investment from scratch at 3.5% interest over 10 years, we must put $852.41 every year into the investment account.
The [fv] argument allows us to set a desired future balance, typically used with investments/annuities.
PMT with [type]
The [type] argument determines if the payments you're making towards the loan or investment are occurring at the beginning or end of the period.
Oftentimes, problems with the PMT function will deal with payments occurring at the end of periods. That's why the default value for it is "0" (representing "end of period").
However, on the off-chance a problem requires you to make payments at the beginning of each period, you'll need to place a "1" for the [type] argument.
Let's take the investment account example from above, except this time assume that we'll be making payments towards the account at the beginning of each year instead of the end.
=PMT(0.035, 10, 0, 10000, 1)
When we code this in Excel...
...we get a yearly payment towards the investment account of $823.59.
The [type] function determines if you're making payments at the end ("0") or beginning ("1") of each month. The default is end, and any problem that wants beginning will explicitly tell you!
Practice problem
Given the following loan situation, determine the monthly payment. Payments are made at the beginning of each period.
=PMT(B2/12, B3*12, B1, 0, 1)
Answer: $917.77
Note #1: If you're asking why [fv] is "0", it's because we want to pay off the loan, meaning that the desired future value is $0.00!
Note #2: If you're asking why we're dividing by 12 for rate and multiplying by 12 for nper, it's because we're dealing with monthly payments, not yearly payments.
Excel (Exam 1) Follow-along Guide
It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 1) Follow-along Guide for you!
As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!
You can obtain the Excel (Exam 1) Follow-along Guide for FREE by entering your email below!
Next, let's learn how to use the IF function to display data based on a set of conditions!
Free Resources
Each exam concept broken down in simple, real-world examples!
 | Tools | Excel (Exam 1) Follow-along Guide |
 | Lesson | YEAR, MONTH, DAY |
| Lesson | SUM & AutoSUM |
| Lesson | Relative vs. Absolute Cell References |
| Lesson | RANK |
| Lesson | COUNT |
| Lesson | ROUND |
| Lesson | WEEKDAY vs. WORKDAY |
| Lesson | VLOOKUP |
| Lesson | PMT |
| Lesson | IF |
| Lesson | Percent change |
| Lesson | Excel charts |
| Lesson | Importing text files |
Practice Problems
Step-by-step walkthrough for each of the questions you need to be ready for!
"I bought this Cram Kit the night before my exam, and it helped me tremendously. It provides step-by-step explanations for all the units covered in class."
Matt Lamanna
Oct. 24, 2021
I’m a Miami University (OH) 2021 alumni who majored in Information Systems. At Miami, I tutored students in Python, SQL, JavaScript, and HTML for 2+ years. I’m a huge fantasy football fan, Marvel nerd, and love hanging out with my friends here in Chicago where I currently reside.