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.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

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.

Kasper Langmann, Co-founder of Spreadsheeto

Now that you know the basics, let’s try and use the formula in a simple loan payment calculation.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

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
showing all data for simple loan payment calculator

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.

Kasper Langmann, Co-founder of Spreadsheeto

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)

simple pmt formula used in simple loan payment calculator

Once you press ‘Enter’, you’ll then see your annual loan payment.

sample of a simple loan payment calculator

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
all data for enhanced loan payment calculator

With the addition of the optional values, the formula now becomes:

=PMT(5%, 5, 50000, 0, 1) or =PMT(B9, B10, B11, B12, B13)

whole pmt formula used in enhanced loan payment calculator

Press ‘Enter’ to run the formula and see your annual loan payment.

sample of an enhanced loan payment calculator

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:

pmt formulat used in different payment frequences

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.

Kasper Langmann, Co-founder of Spreadsheeto

To use a dropdown, you need to enter the predefined options for the different payment periods and the annuity type.

predefined options for dropdown

Once you have the options defined, you can now add a dropdown for the “Periods” and “Payment Due”.

dropdown in an advanced loan payment calculator

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)
vlook formula used in advanced loan payment calculator

The last step is writing the PMT formula:

=PMT(F2/G5, F3*G5, F4, 0, G6)

whole pmt formula in an advanced loan payment calculator

Remember, this formula is similar to the one we’ve used in the previous section.

Kasper Langmann, Co-founder of Spreadsheeto

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”

interactive label for loan payment

Once you’ve taken care of that, congratulations! You now have an advanced loan payment calculator.

functionality of 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.

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-04T13:11:22+00:00