Simplify formula using quarterly investment returns

[TAB]

I need some assistance in simplifying a formula that calculates the
annualized return using quarterly returns. I have a spreadsheet that
I have calculated the quarterly returns on my investment account for
the past 7 years. 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

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? An array which takes care of
the (a%+1)*(b%+1) etc... formula that I can keep copying each quarter.

I do calculate the 3yr and 5yr returns also but those formulas stay
the same with 3 years having 12 quarterly returns and 5yr having 20
quarterly returns each

 

[Dana DeLouis]

Hi. Suppose your first three data values are in A1:A3.
In B3, enter this array formula:

=GEOMEAN($A$1:A3+1)-1 (Ctrl+Shift+Enter)

As you add data in Column A, Drag B3 Down.

 

[joeu2004]

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.

 

[joeu2004]

Errata....

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

I believe that should be 4/(count($Y$1:Y28)-1). That off-by-one error
carries through my entire previous explanation. That is, if you have
N data points, you divide by N-1.

 

[joeu2004]

Hi. Suppose your first three data values are in A1:A3.
In B3, enter this array formula:
=GEOMEAN($A$1:A3+1)-1 (Ctrl+Shift+Enter)

I'm not sure, but I think someone pointed out a flaw with GEOMEAN not
too long ago. If my recollection is correct, it is more reliable to
compute the geometric mean with the following array formula (ctrl-
shift-Enter):

=exp(average(ln(1+$A$1:A3))) - 1

I like that formula for another reason: it is easy to modify it to
compute the "geometric std dev" -- simply replace "average" with
"stdev".

In any case, as I pointed out to the OP, that computes the average
__quarterly__ rate of return, not the __annualized__ rate of return
that the OP thought he/she was computing. So everything here needs to
be annualized, to wit:

=geomean(1+$A$1:A3)^4 - 1

=exp(4*average(ln(1+$A$1:A3))) - 1

 

[TAB]

Thank you joeu2004 and Dana DeLouis

I have 10 portfolio's that I was calculating returns for. I used the
formula below and it matched my numbers perfectly. This will be a
great time saver going forward. Thanks.

=EXP(4*AVERAGE(LN(1+$H$26:AC26))) - 1 (ctrl-shift-Enter):

 

[joeu2004]

I'm not sure, but I think someone pointed out a flaw with GEOMEAN
not too long ago. If my recollection is correct, it is more reliable to
compute the geometric mean with the following array formula (ctrl-
shift-Enter):
=exp(average(ln(1+$A$1:A3))) - 1

Not so much a flaw in GEOMEAN as a limitation of binary computer
arithmetic. In a thread on June 23, Ron Rosenfeld speculated about
why GEOMEAN returned #NUM after a certain number of data points:
"With 174 entries, my guess is that your formula is producing a value
outside of the range allowed by Excel". It is unclear what the range
of the terms of GEOMEAN were in that context.

Also, in a thread on Aug 21, Harlan Grove offered a formulation of the
above that does not require an array formula, namely (modified to fit
Dana's example):

=exp(sumproduct(ln(1+$A$1:A3))/count($A$1:A3)) - 1