Simplify formula using quarterly investment returns
 

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

Answer: Simplify formula using quarterly investment returns
 

Calculating Annualized Return Using Quarterly Returns

Yes, there is a simpler way to calculate the annualized return using quarterly returns. You can use the PRODUCT function in Excel to multiply all the quarterly returns together and then use the POWER function to raise the result to the power of 4 (since there are 4 quarters in a year). Here's how you can do it:

1. In a new cell, enter the PRODUCT function with the range of quarterly returns as the argument. For example, if your quarterly returns are in cells A1:A4, the formula would be:
   Formula:

   ---

   =PRODUCT(A1:A4) 


   ---

2. Next, use the POWER function to raise the result of the PRODUCT function to the power of 4. The formula would be:
   Formula:

   ---

   =POWER(1+PRODUCT(A1:A4),4) 


   ---

3. This will give you the annualized return for the past 7 years. To calculate the annualized return going forward, simply add the new quarterly return to the range and the formula will update automatically.
   
4. To calculate the 3-year and 5-year returns, you can use the same formula but with the appropriate number of quarterly returns. For example, for the 3-year return with 12 quarterly returns, the formula would be:
   Formula:

   ---

   =POWER(1+PRODUCT(A1:A12),4/12)-1 


   ---

Using this method, you can simplify your formulas and avoid the tedious task of adding each quarterly return to the formula.

Simplify formula using quarterly investment returns
 

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.

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"TAB" wrote in message
ups.com...
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

Simplify formula using quarterly investment returns
 

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.

Simplify formula using quarterly investment returns
 

Errata....

On Sep 30, 4:49 pm, I wrote:
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.

Simplify formula using quarterly investment returns
 

On Sep 30, 1:29 pm, "Dana DeLouis" wrote:
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

Simplify formula using quarterly investment returns
 

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):

Simplify formula using quarterly investment returns
 

On Sep 30, 5:15 pm, I wrote:
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