On Dec 11, 7:39 am, trey1982
wrote:
How can I go about creating a formula that calculates the returns for strings
of positive returns? Sample data is below

It is helpful in situations like this to provide an example of how you
would compute what you want on paper. My idea of what you want to
compute might not agree with yours.

Consider the first sequence of consecutive positive returns (1.37%,
0.99%, 0.12%). If these are returns on an investment, I would compute
the cumulative return by:

(1+1.37%)*(1+0.99%)*(1+0.12%) - 1

The annualized return for that 3-month period would be:

( (1+1.37%)*(1+0.99%)*(1+0.12%) )^(12/3) - 1

If one of those is what you want, put one the following array formulas
(commit with ctrl+shift+Enter) into E1 and copy down:

=if(D1="", "", product(1+offset(B1,-D1+1,0,D1,1)) - 1)

=if(D1="", "", product(1+offset(B1,-D1+1,0,D1,1))^(12/D1) - 1)

Format the cells as Percentage with 2 decimal places.

The first formula computes the cumulative return. The second formula
computes the annualized return.

Note: If you do not see curly braces around the formulas, you typed
Enter instead of ctrl+shift+Enter. To corrrect that mistake, select
the first cell, press F2, then press ctrl+shift+Enter. Copy the
corrected formula down.

HTH.


----- complete original posting -----

On Dec 11, 7:39 am, trey1982
wrote:
How can I go about creating a formula that calculates the returns for strings
of positive returns? Sample data is below i.e. return for Jan-06 to Mar-06,
Jun-06, and Aug-06 to Feb-07. (everytime column D0)

thanks...trey

Motnh



Jan-06 1.37% 1
Feb-06 0.99% 2
Mar-06 0.12% 3 3
Apr-06 -0.45% 0
May-06 -0.18% 0
Jun-06 0.30% 1 1
Jul-06 -0.21% 0
Aug-06 0.38% 1
Sep-06 0.74% 2
Oct-06 0.37% 3
Nov-06 1.50% 4
Dec-06 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0