On Sep 30, 12:53 pm, TAB wrote:
To get an annualized return for the past 7 years
(and going forward) my formula looks like this with the letters
representing quarterly returns.
((a%+1)*(b%+1)*(c%+1)........)^(1/(# of quarters)-1

First, that is the average quarterly rate of return. The average
annualized rate of return would have the term 4/(# of quarters) for
the exponent.


So each quarter I have to add another quarter of data to the formula
which get tedious when doing it on several investment portfolios.
Is there a simpler way of doing this?

To calculate the rate of return of each quarter, presumably you
compute yK/y[K-1] - 1, where yK and y[K-1] are the values of the
investment in the current (K-th) and previous quarters respectively.
If your spreadsheet still has all of the per-quarter valuations
y1,y2,...,yN, the average quarterly rate of return is simply (yN/
y1)^(1/N) - 1, and the average annualized rate of return is (yN/y1)^(4/
N) -1. So you can write the following formulas (suppose you have 7
years of quarterly data in Y1:Y28):

=(Y28/$Y$1)^(4/count($Y$1:Y28)) - 1

As you add more data, simply copy the formula down. Y28 will be
change automagically.