PMT Function - Get the Payment Due for a Loan in Excel

How to calculate the payment amount for a loan or similar financial instrument that has a series of constant payments and a constant interest rate.

This works great when calculating how much you will pay for a loan.  You will be able to find out the size of the payments that you will make, including interest and principal.

Syntax

[] means the argument is optional.

Now that you know the syntax, let's get into some examples for using the PMT function.

Calculate the Payments to be Made for a Loan

Here is our basic loan payment calculation spreadsheet:

The basic loan will have an interest rate, a principal (the present value), and a length or the amount of time that you have to pay the loan (the nper argument).

Here, we have a monthly interest rate of .5% with a current value of $100,000 and a payoff time of 360 monthly periods, which works out to 30 years.

Our PMT function looks like this:

We only have to fill-in the rate, nper, and pv arguments.

Note that for the pv argument, there is a negative sign in front of it.  This is so that the result that we get will be positive.  If it helps, you can think of the loan principal, the pv amount, as negative since that is the amount that you owe the bank.

Adding a Future Value to the PMT Function

If the loan, or the financial instrument for which you are calculating the payments, will still have a value after the end of the payments, you would put this amount in for the fv argument.

If the loan will still be worth $10,000 at the end of the payments, you would do this:

You can see that I input $10,000 for the Future Value argument and then referenced the cell that contains that amount in the PMT function.  You do not put a negative sign in front of this number like you do with the Present Value argument.

Changing When Payments are Made

You can make payments at the beginning or the end of a period and you change the type argument to account for this in the PMT function.

By default, the type argument is 0, which means payments are made at the end of the period.  To change this to making payments at the start of the period, just put a 1 in for this argument.

A Note on Periods

It is VERY important that you have all of your numbers based in the same time-frame.

If you get a yearly interest rate but make payments on a monthly basis, you must divide that rate by 12.  As such, in this case, the number of periods or payments, the nper argument, must also be in months.

This is very easy to forget, especially if you are doing finance problems at a university because they will give you the values in different time-frames and you will have to standardize them.

If you wanted to use the PMT function like above but input all values on a yearly basis, you could perform the appropriate calculations in within the PMT function like this:

Final Notes

The PMT amount DOES INCLUDE the principal and the interest amounts of a payment; it does NOT include anything else, such as taxes or other fees.

Keep your periods/interest rates in line and consistent; failing to do this is a simple, yet costly, mistake.

If you wanted to put all of the values directly into the PMT function, that is no problem.  I put the arguments in their own cells to more clearly illustrate how to use this function.

The PMT function is one of the most helpful financial functions and it is easy to use.

Make sure to download the tutorial that accompanies this tutorial so that you can see how everything works.