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 = 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