Excel 2013 & 2016: Using NPER to determine how long it will take to
reach a savings goal

Updated 2014.01.09

# Objective:

·
Learn how to use the NPER function to determine
how many periods it will take to reach an investment goal when making regular constant
payments at a regular interest rate.

# NPER Arguments:

·
RATE: rate __per period__, not the annual
rate

·
PMT: Payment—always a negative number

·
PV: Present value. The amount you have in the
bank right now (negative) if you are doing a savings problem, or the amount you
owe (positive) if you are doing a loan problem.

·
FV: Future value. The amount you will have in
the bank 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 0). This number is always positive (or 0).

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

## Example: How long
will it take to reach your savings goal?

Your goal is to become a millionaire.
You currently have $100,000 invested and expect to receive an annual return of
10%, compounded monthly. You can invest $500 per month at the beginning of each
month. How long will it take to accumulate $1,000,000?

#### Solution

·
**Rate Per Period**: 10% annual rate, divided
by 12 periods per year = .10/12.

·
**Payment**: $500 (this will be negative,
since it represents money that you are paying out).

·
**Present value**: $100,000 (this will also
be negative, since it represents money that you have already paid out).

·
**Future value**:** **$1,000,000.

·
**Type**: 0=end of period, 1=beginning of
period.

The NPER arguments are: NPER (**rate, pmt, pv, **fv,
type)

So the formula is:

=NPER(.10/12,
-500, -100000,1000000, 1)

Note that although I used constants
for clarity in this example, you should use cell references in formulas, not
constants.

The **pmt**
is negative because it represents money that is being "paid out" to
the bank (even though it is going into *your* account).

The **pv**
argument is negative because it represents the sum of all money that has
previously been "paid out" to the bank.

If you use cell references, you get:

Note that the result is the number of **periods**,
not the number of **years**. To get the number of years, divide the number
of periods by the number of periods per year.