annualized monthly standard deviation

Hi guys,

I have a question regarding an Excel (2016) sheet I am currently working on. I have the feeling the solution must be quite simple but I spent a few hours researching already and cannot find a solution.
I have 50k rows of data, where I have:

Column F: the monthly stock price of around 400 firms from 2007 to 2016 (hence, each firm has 120 rows of data). The first 120 rows are e.g. Oracle, then the next 120 rows Apple, and so on.

Column G: the monthly return on the S&P 500 in % for the months 2007 through 2016. One month it went up by 3.6%, the next down by 3.5%, etc. Of course, these values begin to repeat after 120 rows when the next firm is listed.

Column H: the monthly percentage change of the stock, so basically it shows how the values in column F change in % terms from month to month.

Column I: the annualized monthly standard deviation of the S&P 500. Idea is straightforward, I take the standard deviation of each 12 months of column G and multiply by sqrt(12). It was easy to copy that down the 50k rows as the values for the S&P start from the beginning every 120 rows (I did it manually 10 times for the 10 years, then copied it down).

Column J: There I want to do the same as in column I, just for the 400 firms, but obviously now I cannot copy the formula down (each stock has unique returns & std. dev. over the time period).

Thus, how do I write a formula that automatically changes every 12 rows the fixed 12-row range that I take from column H to calculate the annualized standard deviation in column J?

excel forum.JPG


Very much appreciate your help!

Best

Of course!

I attached a sample file below, thanks for helping.

Best

Dear AliGW,

thanks a lot for your great help, the formula works perfectly and saved me a lot of time!

have a nice evening

Freshmind