Calculate How Long to Make $1m with NPER

James wrote to me and asked: if he buys shares worth $2000 and gets 4% return, how many times does he need to reinvest all of his capital before he makes $1m.

Of course James was quick to point out that this scenario doesn’t take into account loosing on any of his trades. Reasonable I think, after all we live in a perfect ‘Excel’ world 🙂

To solve James’ question we can use the NPER function like this:

=NPER(4%,0,-2000,1000000)

=158.4522

So, James needs to invest his $2000+returns 159 times (rounded up since you can’t invest .4522 times) to make $1m. With no losses along the way I might add. Good luck James!

Excel NPER Function

The NPER function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

The syntax for the NPER Function

=NPER(rate,pmt,pv,[fv],[type])

rate – the interest rate per period.

pmt – the amount being invested each period. I cannot change over the life of the investment.

pv – the present value, the amount invested at the beginning. Or if you want to be technically correct it’s the amount that a series of future payments is worth now.

fv – the future value, or the value you want at the end when your last payment is made. Defaults to 0 if omitted.

type – enter 0 or omit for investing at the end of each period, 1 for the beginning of each period.

Note:

1. The arguments in the syntax surrounded by square brackets, namely 'fv' and 'type' are optional, however the first 3 arguments are required. If you omit them they default to zero.
2. Either the pmt, pv or fv must be a negative value or you will end up with a #NUM! error. Preferably your outgoing payment, will be the negative value as this is in line with cash flow conventions where payments are represented as negative values.

How Long To Repay Loan with Excel NPER Function

If you’ve got a mortgage or loan and you want to know how long it will take to pay it off you can also use NPER.

Let’s say your loan is $300,000 at a rate of 9% per annum and you want to pay it off fast so you decide to make repayments of $5000 at the end of each month.

=NPER(9%/12,-5000,300000)

=80.0101 months, or 6.6675 years.

Notice how the interest rates is entered as 9%/12 to convert it into a monthly rate and the loan repayment of $5000 is a negative value in line with cash flow conventions.

Thanks for your question James.