Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Annualizing volatility (2000)

    Could any one help me with the formula to calculate a 20-day volatility, using a 260 day annualization factor. I understand how to calculate the volatility of the daily data using STDEVP function, but I don't know how to work in the 260 day annualization factor into the equation. Any help would be greatly appreciated.

  2. #2
    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: Annualizing volatility (2000)

    Assume your 21 days (day 0 - Day20) are in cells A1 - A21.

    You can calculate this DIRECTLY with an ARRAY formula (confirm with ctrl-shift-enter, not enter):
    <pre>=STDEV(LN((A2:A21)/(A1:A20)))*SQRT(260)
    </pre>

    Format as Percent if desired (or multiply by 100 if you actually use the "percent value").

    Indirectly (and to help explain the ARRAY), put your calcs in B2: B21 (B1 will be blanK since this is the "0-value". In B2 enter:
    <pre>=LN(A2/A1)
    </pre>

    Copy this from B3 to B21.
    Now put in a cell:
    <pre>=STDEV(B1:B21)*SQRT(260)
    </pre>

    and you have the 20-volatility calc with a 260 day ann factor.

    The 2 values are the same. The array just gets by calculating INTERNALLY the intermediate column B

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annualizing volatility (2000)

    Thank-you! This is web site is a wonderful resource.

Posting Permissions

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