Results 1 to 6 of 6
  1. #1
    New Lounger
    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.

  2. #2
    3 Star Lounger
    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.

  3. #3
    3 Star Lounger
    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 04-Mar-05 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 five-year 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 X-60 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 some-odd. 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]

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 05-Mar-05 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 "strings-date 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

  5. #5
    New Lounger
    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!

  6. #6
    New Lounger
    Join Date
    Feb 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Series of calculations (2002- SP3)

    Thanks Steve.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •