Excel 2019/365: Using PMT to Compute Loan Payments

Updated 2019-08-07

·
Learn how to use the **PMT** function to
determine the regular payment on a loan

· RATE: Rate per period, not rate per year.

· NPER: Number of periods, not number of years.

·
PV: Present value. If doing a savings problem,
the amount you have in the bank at the beginning (a __negative__ number). If
doing a loan problem, the amount you have borrowed (a __positive__ number).

· FV: Future value. The amount you will have at the end of the number of periods if you are doing a savings problem, or the amount you will owe at the end of the number of periods if you are doing a loan problem (usually, but not always, 0).

· TYPE: Type of payment. 1=payments at the beginning of the period, 0=at the end of the period.

You want to borrow __$150,000__ for __30
years__ at an __annual rate of 6%__. You will make __monthly payments__
(12 per year), and payments will be at the __end__ of each interest period. The
__balance at the end of the loan will be $0__ (you will completely pay it
off). What is your monthly __payment__?

Once you realize that what you are trying to find is the regular payment (requiring the PMT function), you need to identify the arguments:

·
"You want to borrow $150,000…"
This is how much money you have now: the **present value.**

·
"…for 30 years...monthly
payments" These two numbers determine the **number of periods: **30*12
= 360.

·
"…at an annual rate of 6%" This
is the annual rate, but there are 12 periods per year, so the **rate per
period **is 6%/12 = 0.5%, or .005.

·
"…balance at the end of the loan will
be $0…" This is the **future value** of the loan (amount you owe
when it is paid off): $0.

·
"…payments will be at the __end__
of each interest period." **Type** is "end of period", so use
0.

The PMT arguments are: PMT(**rate,
nper, pv, **fv, type)

So the formula is:

=PMT(.005, 360, 150000, 0, 0)

The **pmt** function will return a negative
value, because it represents money that you are "paying out".

It is __never__ a good idea to build
numbers into a formula. Use __references to the cells__ that hold the
numbers:

Note that the formula is
=PMT(C8,C9,C5,C7,C10). To change any of the numbers, all you have to do is
change any of the cells C2 through C10; __you don't have to change the formula__!

Note that the payment will be red with
parentheses. This indicates that it is a negative number. You can make it a
positive number by putting a minus sign in front of the word *PMT.*