Home / Formulas / Annualized Standard Deviation

Annualized Standard Deviation

The ExcelHelpDesk Support team received the following request for help

Problem – 5 Year Annualized Standard Deviation

I have quarterly performance returns for a portfolio and I believe I got the annual standard deviation for 2006, 2007, 2008, 2009, and 2010 using Excel. The next thing I need to do is get an 5 Year Annualized Standard deviation number and I have no idea how to do it. Any help would be appreciated.

For this problem we need to provide a formula that will calculate the “5 Year Annualized Standard Deviation” for the existing returns calculated on an annual basis. We researched this calculation and found the following…

Standard Deviation

Standard Deviation = degree of variation of returns.
Calculated as (((R1 – M)2 + (R2 – M)2 + … + (RN – M)2) / (N-1))1/2
Where Ri – return of specific period, M – mean of return, N – number of periods

N will equal the number of periods in a year regardless of the length of time that is being annualized.
So if you are using monthly returns, N=12; quarterly returns, N=4; daily returns, n=252; annual returns, N=1

Annualized Standard Deviation

Annualized standard deviation = Standard Deviation * (N)1/2
Where N = number of periods in the calculation.

This is where our question got to and now to achieve the 5 Year Annualized Standard Deviation we need the following…

5 Year Annualized Standard Deviation

Let’s say we have 5 years of returns as in the question posted above. With annual returns N=5
We then calculated the Standard Deviation of those returns and multiply that by the Square Root of N Years. So we have = Standard Deviation of the Returns * (SQRT(N Years)

Here is the worksheet screenshot demonstrating the calculation that is required.

We used the following reference site for the resolution of this posted problem.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment

About Excel Help Desk

Check Also

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.

6 comments

  1. So, if I have 10 years of quarterly returns, is it * (40)^1/2? Thanks in advance….

    • Kent,
      For 10 years of quarterly returns it would be 10*12 to arrive at the number of months.
      Then we would have (120)^1/2 included to the calculation. Even though you have quarterly returns the calculation is over 10 years and needs to include all the months for that period
      Regards
      Excel Help Desk

  2. So if the time period is 2 years, would it be (24 mo standard dev) * (24)^1/2

  3. Hi,

    You are almost there based on the outline calculation above for a 3 year standard deviation the calculation would be

    = (36 Months Standard Deviation) * (36)^1/2

    or

    = (36 Months Standard Deviation) * (SQRT(36))

    Let us know if this was able to assist you in the calculation of standard deviation

  4. Excel Help Desk,

    You know it’s interesting. I did not know that I could arrive at the 5 year annualized standard deviation without including return numbers. I was inputting the standard deviation of the 20 quarterly returns and then multiplying by the square root of 20. This is a huge help and a great learning experience. Your Excel Help Desk is truly a great resource. I really appreciate your time and effort on this. If your ever in Lancaster, drinks on me.

    Regards,
    John Panas