PMT (Payment)
Use the PMT function to determine the regular
payment on a loan, or the amount you must "pay" (deposit) in a
savings account to reach a desired savings goal.
The function arguments are:
·
Rate
per period
·
Number
of periods
·
Present
value
·
Future
value
·
Type
Example 1: Paying off a Loan
You want to borrow $50,000 for 10 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. What is your
monthly payment?
Solution 1
Once you realize that what you are trying to find is the
regular payment (requiring the PMT function), you need to identify the
arguments: Rate per period: Annual rate
is 6%, but since we are making 12 payments per year, the rate per period
is 6%/12 = .5% = .005.
Number of periods is 10 years times 12 periods per
year = 120.
Present value is the amount of the loan:
$50,000
Future value of the loan (amount you owe when
it is paid off): $0.
Type is "end of period", so
use 0.
The PMT arguments are: PMT(rate, nper, pv, fv, type)
So the formula is:
=PMT(.005, 120, 50000, 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(B1/B3,B2*B3,B4,B5,B6).
To change any of the numbers, all you have to do is change any of the cells B1
through B6; you don't have to change the formula.
Note that the payment is in red numbers with parentheses.
This indicates that it is a negative number. You can make it a positive number
either by putting a minus sign in front of the B4 in the formula, or by putting a minus sign in front of the word PMT.
Example
2: Reaching a Savings Goal
You can also use the PMT function to determine how much
you need to save to reach a savings goal. Assume that your goal is to save $100,000
over the next 20 years. You know that you can get an annual return of 8%,
and that you will be making deposits at the beginning of every month.
How much do you have to save every month to reach your goal?
Solution 2
Once you realize that you need to
use the PMT function, you need to identify the arguments:
Annual rate is 8%, but since we are making 12
payments per year, the rate per period is 8%/12 = .08/12.
Number of periods is 20 years times 12 periods per
year = 240.
Present value is the amount you have currently
saved: $0
Future value is the amount you want to have at
the end of the 20 years: $100,000.
Type is "beginning of
period", so use 1.
The PMT arguments are: PMT(rate, nper, pv, fv, type)
So the formula is:
=PMT(.08/12, 240, -0, 100000, 1)
The pmt
function will return a negative value, because it represents money that you are
"paying out" (even though you are paying it to yourself).
Note that the pv argument is negative because it represents the sum of all
of the money that you have "paid out" (to yourself).
It is never a good idea to build numbers into a
formula. Use references to the cells that hold the numbers:
Note that you can also use this if you currently have some
money in your savings account. Just set the current savings amount to the
appropriate value, e.g. $10,000. So if you have $10,000 in the bank right now,
and plan to make deposits at the beginning of each month for 20 years and earn
8% (annual rate), and your goal is $100,000, all you have to do is change the
Current Savings cell (B6) to $10,000. Important note: the sign of the
present value must be negative (note the –B5 in the formula). The formula
is:
The PMT arguments are: PMT(rate, nper, pv, fv, type)
So the formula is:
=PMT(.08/12, 20*12, -10000,
100000, 1)
The pmt
function will return a negative value, because it represents money that you are
"paying out" (even though you are paying it to yourself).
Note that the pv argument is negative because it represents the sum of all
of the money that you have "paid out" (to yourself).
It is never a good idea to build numbers into a
formula. Use references to the cells that hold the numbers:
Updated 2006.10.25