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

    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

    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)]

    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