Results 1 to 6 of 6

20050304, 20:08 #1
 Join Date
 Feb 2005
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Series of calculations (2002 SP3)
Hello,
I am calculating 5 year compounded returns starting at a different month from199001 to 199601. The attached sheet shows the calculations for the first three columns and the last column. The idea is to start one row down with the return for the first month (col [img]/forums/images/smilies/cool.gif[/img] and calculate compounded returns (for 60 months) based on previous compounded return (current column) and current return (col [img]/forums/images/smilies/cool.gif[/img]. I also need to show the last value in row 136. There should be 72 columns in all. Do you know how to use Excel formulas to do this? The formula for compounded returns is included in my sheet.
Thanks.

20050304, 22:20 #2
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Series of calculations (2002 SP3)
See attached. There were some dates for monthly returns that were not the month ends, and this may have created a couple of minor errors, since I test for the particular date being before or after the date you want to calculate for....
Comments in the sheet explain what is going on, but feel free to post any further questions.

20050304, 23:09 #3
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Series of calculations (2002 SP3)
<P ID="edit" class=small>(Edited by dcardno on 04Mar05 16:09. Bloody typos)</P>Ooops  found a small error  the lookup column to match the ending date to get the index to return the correct cummulative calculation (boy, that was easier to think than to write) has to be one row longer. I've fixed that, and set the months as the end of the month following the month listed above  that let me get rid of the kludgy date calculation and delete the original date column.
A couple of other comments:
<UL><LI>Returning zeros if the month you are examining is before the start date, and the cummulative return otherwise works because for the first month of interest the prior cumulative return is zero, and compunding a zero return and an actual return will just result in the actual return you are interested in.
<LI>There is no reason to stop after 5 years  you could continue without testing for being after the date of interest  the last row would show cumulative TSX returns from "Date X" to present and then the fiveyear return could be extracted as shown.
<LI>Another alternative is to start with an "index value" (traditionally 100)  and increase or decrease it it by the market return. In another column, the percentage difference between the index value at time X and the index value at time X60 is the 5 year return. This has the advantage (depending on what you are doing) of only needing two columns (plus labels and titles, etc), instead of 70 someodd. You could even determine how many years back to calculate the return by specifying the location with an offset function and then referencing all the offsets to a single cell. I can post an example, if you like.[/list]

20050305, 10:31 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Series of calculations (2002 SP3)
<P ID="edit" class=small>(Edited by sdckapr on 05Mar05 06:31. Changed and improved (shortened) the formula )</P>Insert 2 new rows [Select A1:A2 Insert rows] [These can be hidden if desired]
In the new "b1" enter the date:
1/1/1990
In new C1 enter:
<pre>=VALUE(TEXT(DATE(YEAR($B$1)+5,MONTH($B$1)+COL UMN()3,0),"yyyymmdd"))</pre>
In new C2 enter:
<pre>=VALUE(TEXT(DATE(YEAR($B$1)+5,MONTH($B$1)+COL UMN()3,0),"yyyymmdd"))</pre>
This gives your starting "stringsdate values" for the first column
In C3 enter:
<pre>=LEFT(C1,6)&""&LEFT(C2,6)</pre>
In C4:
=IF(OR(C$1>$A4,C$2<$A4),"",IF(LEFT($A4,6)=LEFT(C$1 ,6),$B4,((1+C3)*(1+$B4))1))
Copy C4 to C5:C135
In C138 enter:
<pre>=VLOOKUP(1000000,C$4:C$135,1)</pre>
Select C1:C138 and copy it to as many columns as you want
Steve

20050305, 12:27 #5
 Join Date
 Feb 2005
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Series of calculations (2002 SP3)
It worked beautifully (with some minor adjustments to do exactly what I needed).
Thanks very much!

20050305, 12:27 #6
 Join Date
 Feb 2005
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Series of calculations (2002 SP3)
Thanks Steve.