STDEV.S over multiple periods

[FranzKafkaIsDead]

Hello,

I am attempting to calculate the annualized volatility of the S&P 500 since inception based on historic monthly data. I'm really struggling with this and I feel like there are multiple things I'm not doing right or understanding, so here's a sample of the data. (The full sheet is 1,100 rows.)

Date	Close
1/1/2017​	2413.32​
2/1/2017​	2496​
3/1/2017​	2492.67​
4/1/2017​	2508.18​
5/1/2017​	2534.8​
6/1/2017​	2544.58​
7/1/2017​	2596.29​
8/1/2017​	2590.29​
9/1/2017​	2625.17​
10/1/2017​	2686​
11/1/2017​	2761.43​
12/1/2017​	2788.58​
1/1/2018​	2931.11​
2/1/2018​	2803.39​
3/1/2018​	2722.74​
4/1/2018​	2719.55​
5/1/2018​	2764.79​
6/1/2018​	2775.46​
7/1/2018​	2875.43​
8/1/2018​	2959.55​
9/1/2018​	2969.35​
10/1/2018​	2757.84​
11/1/2018​	2818.13​
12/1/2018​	2567.01​
1/1/2019​	2763.59​
2/1/2019​	2834.61​
3/1/2019​	2868.41​
4/1/2019​	2963.5​
5/1/2019​	2763.07​
6/1/2019​	2953.53​
7/1/2019​	2986.34​
8/1/2019​	2935.24​
9/1/2019​	2982.69​
10/1/2019​	3034.52​
11/1/2019​	3140.98​
12/1/2019​	3230.78​

So in a perfect world - what I'd like to be able to do is do =STDEV.S(B1:B12 which comes to 108.2021 and then drag the fill handle down, but add in some type of +12 in the formula so that instead of going to B2:B13 on the next equation, it would go to B12:B24. A workaround to this is that I can just calculate 1,100 rolling standard deviation periods and only use the ones for each month of December. (Which I can manage.)

The next major problem I'm having though is that I'm trying to check my math against known data from the S&P and the formula I'm using to calculate standard deviation is giving me a different result than what Yahoo finance is reporting as the 3-year standard deviation for the S&P 500. (I realize that this could be due to the fact that they're using daily data and I'm using monthly data, but the difference I'm showing is big enough that it seems more likely I'm just doing something wrong.)

Just to make sure I'm comparing Apples to Apples, here's what I'm doing to get the three-year standard deviation of the above data set. Yahoo Finance says my answer should come out to be 12.03 (from the following link here).

STDEV.S (B1:B36) = 194.654
194.654 * 1/SQRT 12 (as N=12) = 4.579876
4.579876 * 3 (Arbitrary? Because that brings me closer to the 12 number I'm looking for? Grasping at straws?) 13.73

Pleeeeease help me understand what I'm doing wrong...

 

[JustynaMK]

Welcome to the forum!

I can only help you with the first formula - assuming that your data starts in row 2, you can use:
=STDEV.S(INDEX(B:B,ROW(A1)*12-10):INDEX(B:B,ROW(A2)*12-11))

It will calculate the following ranges: STDEV.S(B2:B13), STDEV.S(B14:B25), and so on...

Not sure about the second part of your post though, sorry!