Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Calls closed in Given periods | Excel Worksheet Functions | |||
Calculating overlapping time periods | Excel Worksheet Functions | |||
Calculating overlapping time periods | Excel Worksheet Functions | |||
Calculating overlapping time periods | Excel Worksheet Functions | |||
Calculating number of periods when payment amount changes | Excel Worksheet Functions |