How To Use The PMT Function In Excel
– Free Exercise File Included!

The key to understanding a function is knowing its parameters or arguments and what they mean.

The formula for PMT is as follows:

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.

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

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!

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)

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.

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.

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.

To make things easier, we’ll still be using the same values in our example.

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)

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.

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.

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.