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