P1.T4.VAR-HULL-Ch15_LearningSpreadsheet_4b.3.volatility_ma

[gargi.adhikari]

In reference to P1.T4.VAR-HULL-
Ch15_LearningSpreadsheet_4b.3.volatility_ma:
I was trying to work out the Std Dev formula:
SQRT[ sum( squared x) / (n-1) - { (sum(x) ^ 2 ) / n*(n-1) } ]
which is :-
=SQRT(I4/(n-1)-F4^2/(n*(n-1))) in the sheet below.
= SQRT [ .1603/ (120-1) - {(20.13/100 ) ^ 2 } / 120* (120-1) ] --> but this is not yielding the 2.9840% ...am getting a different no plugging in all the values...

When I had used the traditional Std Dev formula : SQRT[ sum{( x-avg)^ 2} / (n-1) ], I had gotten the 2.984 % value....but plugging in the values into the other formula am not getting the same value as indicated in the Learning Spreadsheet...what am I missing here ... ??????

 

[David Harper CFA FRM]

Hi @gargi.adhikari

I pulled the XLS out and added a few cells, please see https://www.dropbox.com/s/zqx6jkolfs95tr2/0705-hull-volatility.xlsx?dl=0

Is it perhaps merely that .1603 is a typo and should be .1063? Please note that my cell J5 returns the expected 2.9840% = SQRT(0.1063/(n-1)-0.2013^2/(n*(n-1)))
I can't recall where I retrieved this elegant formula for the sample variance (as I don't think Hull gives it, but i could be mistaken); the derivation looks simple, let me know if you can't find it and I'll derive it .... it is utilizing variance(x) = E(x^2) - E[(x)]^2.

You might have noticed (is maybe why you are investigating, very cool!) that, per Hull himself, it is conventionally okay to assume the mean return is zero, such that a quick estimate of volatility here is given by = 2.9888% = sqrt(10.630%/119) where 10.630% is the sum of the squared daily returns. Such a 2.9888% is different than the solved 2.9840% which is technically more correct because it uses the squares of the daily deviations (i.e., deviation of the return from the mean return).

In this way, the elegant formula:
Σ[u(i)^2]/(n-1) - [Σu(i)]^2/[n*(n-1)], where u(i) is the daily log return

does return the technically accurate sample variance which incorporates that series' actual non-zero mean,because it is equivalent to 1/(n-1) * Σ[(u(i) - mean]^2. I hope that clarifies!

 

[gargi.adhikari]

@David Harper CFA FRM Let me start by saying Happy 4th (belated) and thanks for taking the time for the clarification. Yes when I plugged the 10.63% , the variation of the std-dev formula yielded the expected value of 2.984%. I do prefer to use the formula Σ[u(i)^2]/(n-1) - [Σu(i)]^2/[n*(n-1)] for the std - dev and so wanted to make sure I was doing it right. Thanks again !

 

[Arka Bose]

Hi David, the formula you have written, Σu(i)^2/(n-1) - [Σu(i)]^2/[n*(n-1)], where u(i) is the daily log return , shouldnt it be Σu(i)]^2/[n^2*(n-1) (the second term?) since it is the mean u/n which is squared?

 

[David Harper CFA FRM]

Hi @arkabose I thought so too, at first, but see the attached derivation. And I did test Σ[u(i)^2]/(n-1) - [Σu(i)]^2/[n*(n-1)] with various values, and it is robust! Thanks,