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:

 

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/018-NPER-Function_files/image001.jpg

 

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.