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.

## 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.