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

Updated 2014.01.09

· 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.

·
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.

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?

·
**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.