Excel 2013 & 2016: Using PMT to Compute Savings Payments

Updated 2014.01.09

·
Learn how to use the **PMT** function to
determine the amount you must "pay" (deposit) in a savings account to
reach a desired savings goal.

· 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 can also use the **PMT**
function to determine how much you need to save each month to reach a savings
goal. Assume that you currently have $10,000 in the bank but 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?

Once you realize that you need to use the PMT function, you need to identify the arguments:

· "…need to save each month…". The number of periods per year is 12.

·
"…you currently have $10,000 in the
bank…". This is how much you have now: the **present value**. __Note
that this is a negative number__, because it represents the accumulation of a
bunch of previous payments, and __payments are always negative__!

·
"…your goal is $100,000…".
This is how much you want to have in the future: the **future value**.

·
"…over the next 20 years." We
are saving monthly, so there are 12 periods per year times 20 years, so the **number
of periods** is 240.

·
"…get an annual return of 8%". **Annual
rate** is 8%, but since we are making 12 payments per year, the __rate per period__
is 8%/12 = .08/12.

·
"…you will be making deposits at the
beginning of every month." **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, -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: