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

Add to Favorites

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

PMT(rate, nper, pv, [fv], [type])

Argument

Description

Rate

The interest rate per period.  This rate remains the same for the duration of the calculation.  Make sure this rate is per period; if the rate is 5% but there are two periods per year, the number for this argument would be 2.5%.

Nper

The number of periods used for the calculation.  For instance, if there are 12 periods in a year, like for a loan payment, and you are making payments for 10 years, there will be 120 periods, and that is what you would put for this argument.

Pv

This is the present value, which is the current value of the entire series of payments.  For a loan, this is the principal amount.

[Fv]

Optional argument.  This is the future value argument and it is not required.  It is the amount that you will have after you make the last payment.  Usually this amount would be 0 (zero), like when you finish paying off a loan.  If you leave this argument blank, it will default to 0.

[Type]

Optional argument.  This tells the PMT function when the payment will be made, either 0, for the end of the period, or 1, for the beginning of the period.  If you leave this argument empty, Excel will assume it is 0, or that payments are made at the end of each period.

[] 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:

=PMT(B2,B3,-B4)

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:

=PMT(C2/12,C3*12,-C4,C5,C6)

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.


Excel Function: PMT()
Downloadable Files: Excel File