Calculating return for multiple separate time periods
 

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

Calculating return for multiple separate time periods
 

It might be easier if you have a start date (eg in E1) and an end date
(in F1) to define a continuous range, and then a formula in G1 to
provide the returns within that period. Where you have discontinuous
ranges, you can put these in the rows below (eg Jun-06 in both E2 and
F2, and Aug-06 in E3 and Feb-07 in F3), and copy the formula from G1
into G2:Gn as appropriate, and then add these up.

Hope this helps.

Pete

On Dec 11, 3:39 pm, 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 - Hide quoted text -

- Show quoted text -

Calculating return for multiple separate time periods
 

Pete,

I have the start and end date columns figured out. but i cant figure out
how to write a formula to calculate the return between the a given start and
end date.

trey

"Pete_UK" wrote:

It might be easier if you have a start date (eg in E1) and an end date
(in F1) to define a continuous range, and then a formula in G1 to
provide the returns within that period. Where you have discontinuous
ranges, you can put these in the rows below (eg Jun-06 in both E2 and
F2, and Aug-06 in E3 and Feb-07 in F3), and copy the formula from G1
into G2:Gn as appropriate, and then add these up.

Hope this helps.

Pete

On Dec 11, 3:39 pm, 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 - Hide quoted text -

- Show quoted text -

Calculating return for multiple separate time periods
 

Enter this formula in E1 and copy down as needed:

=IF(D1="","",SUM(B1:OFFSET(B1,,,-1*D1)))

Format as %

--
Biff
Microsoft Excel MVP


"trey1982" wrote in message
...
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

Calculating return for multiple separate time periods
 

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