The Motley Fool Discussion Boards

Previous Page

Investing/Strategies / Retirement Investing


Subject:  Re: Formula to Calculate Growth Date:  3/13/2002  12:16 AM
Author:  UhuruPeak Number:  33984 of 88758

I am wondering what mathematical formula is used to calculate growth of an investment if you have the percent return (or the doubling time of the investment). I could do it if there was a one time addition but what if you add a set amount each year (as in IRA contributions)?

I'm not sure which formula you are after? The amount you'll have in 10 years if your initial capital coumpounds at a 10% rate (A)? or the Rate of Return on your investment (B)?

For (A), you want to do the following calculation:
Final_value = Initial_value * (1+ Rate of Return)^Nb_years
(in Excel speak, FV = IV * POWER((1+RR), Nb_yrs)

For (B), you can use the following formula:
IRR(Value, Guess)
Here is the help Excel provides:



Values must contain at least one positive value and one negative value to calculate the internal rate of return.



Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.

In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.


Suppose you want to start a restaurant business. You estimate it will cost $70,000 to start the business and expect to net the following income in the first five years: $12,000, $15,000, $18,000, $21,000, and $26,000. B1:B6 contain the following values: $-70,000, $12,000, $15,000, $18,000, $21,000 and $26,000, respectively.

To calculate the investment's internal rate of return after four years:

IRR(B1:B5) equals -2.12 percent

To calculate the internal rate of return after five years:

IRR(B1:B6) equals 8.66 percent

To calculate the internal rate of return after two years, you need to include a guess:

IRR(B1:B3,-10%) equals -44.35 percent

Did I answer your question?