Calculating equivalent annual % growth rates using multiple-year data

**muppetthumper** · 02-11-2010, 09:11 PM

Hi all,

I've created a spreadsheet to keep track of my modest share holdings and as a record of the past performance of closed out investments.

At present I am able to work out the exact % gain (or loss!) on an investment by taking the monetary change in value net of any fees and dividends, and expressing as a % of purchase value. Its a useful way to help work out where things have gone well, and where they haven't....

However, what I would really like to do is to work out the equivalent annual compound growth rate for any particular share, as this will enable me to make valid comparisons between investments over different time periods. So, for example, lets say I buy a share, hold it for 3 years and sell it for a total gain of 15%. You might suggest just dividing the % figure by the number of years to give an average gain of 5% per year.

However I think a truer reflection of performance is to work out compounded rates. To illustrate this further, lets take a standard interest bearing deposit account. If you had £100 in this account and earned 5% interest each year, you would end up not with £115, but with £115.76 after 3 years. The interest accumulated in all but the final year also earning interest in the subsequent years - the joy of compounding. While its easy to work out how funds accumulate over time with the rate of interest to hand, I don't have a method of performing the reverse calculation - extrapolating the interest rate from an end total and the time.

So for my aforementioned 15% over 3 years example, I can only guess the rate through trial and error, (its something just under 4.8% equivalent annual interest) - and that's just on paper. I wouldn't know where to begin when it comes to writing an excel formula to calculate this. The formula gets more complex still when you factor in time periods that are not whole years.

So, ideally, I would like to have a formula that calculates the time (in days, I guess?) an investment has been held from bought and sold dates (which I write in a format like so: 11-May-2009), and then uses this variable together with the total % gain (or loss) to work out the equivalent % rate of gain per year.

All of which will probably point to the fact that I'd earn more holding the money in a savings deposit account! If anyone can help out with this little headache, I'd most appreciate it. Thanks,

Andrew