NPER (Number of Periods)

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.

Arguments:

• Rate (per period)
• Payment
• Present value
• Future value
• Type (1=payments at the beginning of the period, 0=payments at the end)

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

Your goal is to become a millionaire. You currently have $100,000 in a savings account that pays 10% annually. You can save $500 per month. How long will it take to accumulate $1,000,000?

Solution 1

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.

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

So the formula is:

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

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.

Updated 2006.01.10