# Can I perform a Lo and MacKinlay variance test with Excel?

Nikoleta October 19, 2011

I would like to know if I can perform a Lo and MacKinlay variance ratio test in Excel? And if so, how?

1. Anonymous
October 20, 2011 at 2:28 pm

you need some sort of equation to have values to add them in excel
http://www.mathworks.fr/help/toolbox/econ/vratiotest.html

when you perform the VR test you need to specify a sampling interval of the returns i.e. 5, 10, 15, 20 periods etc... 'q' the sampling frequency. perhaps then You can calculate the VR in Excel.

Lo and MacKinlay Variance Ratio test

tLo-Mackinlay variance ratio test
tp://stata.com/statalist/archive/2006-08/msg00045.html

Variance ratio example

The usual formula for the variance
of a ratio is the following first-order Taylor series approximation:

V(x/y) = [Q^2] [R(x) + R(y) – 2R(x,y)]

where
Q = E(x)/E(y) is the ratio of the means,
R(x) = V(x) / {[E(x)]^2} is the "relative variance" of x,
R(y) = V(y) / {[E(y)]^2} is the "relative variance" of y, and
R(x,y) = Cov(x,y) / {E(x)E(y)} is the "relative covariance" of x&y.

And a second-order Taylor series approximation for the mean of a ratio
is the following:

E(x/y) = Q [ 1 + R(y) – R(x,y)]

hope the above reference will come to help, its difficult i believe to do it.

• Atelokin_85
October 22, 2011 at 12:00 am

Really thank you for your help. I will let you know if i managed finally

Cheers