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!
How Excel’s NPER Function Works
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:
- 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.
- 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.
NPER Calculates How Long To Repay Loan
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.
... I would highly recommend My Online Training Hub for all your Microsoft Office needs .... Geniuses
{ 2 comments… read them below or add one }
similar to James’ question if the return in 4% a year and I invest $200 a month and reinvest the all plus return how would that formula look and how does it affect the time? is the time in years or months?
Hi Primilene,
If you’re investing monthly then you need to adjust your interest rate to monthly and the result will be displayed in months.
e.g. =NPER(4%/12,-200,0,1000000)
=862.9389 months
Kind regards,
Mynda.