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