How to Calculate Stocks Autocorrelation in Excel

Autocorrelation, also known as serial correlation or lagged correlation, explains the relationship between observations between the same variable over different periods of time. The observations are said to be independent if autocorrelation is zero. Autocorrelation is calculated as a function of mean and variance.

Learn about Autocorrelation

Autocorrelation has application in stock returns. We can calculate autocorrelation in stock returns which can be helpful in equity analysis. For example, let’s say you identify a stock that has exhibited high autocorrelation historically. If you observe that the stock is moving up for the past few days, you can expect the stock movement to match the lagging time series.

Calculation of autocorrelation is similar to calculation of correlation between two time series. The only difference is that while calculating autocorrelation, you use the same time series twice, one original, and the other as the lagged one.

Formula for Calculating Autocorrelation

Example: Stock Autocorrelation in Excel

Let’s take a numerical example to learn how we can calculate the autocorrelation for stock returns data in excel. Let’s say we have the following stock returns data with us.

A	B
Day	Returns
1
2	4%
3	3%
4	-1%
5	2%
6	4%
7	5%
8	3%
9	-1%
10	1%

Step 1: Calculate the Variance

The variance of the series can be calculated using the VAR formula as follows:

=VAR(B2:B10)

= 0.000469

Step 2: Calculate the Covariance

We now need to calculate the k-th lag variance.

For k=1, covariance will be calculated as follows:

=COVAR(B3:B10,B2:B9)

= 0.0001

For k=2, covariance will be calculated as follows:

=COVAR(B4:B10,B2:B8)

=-0.00027

Autocorrelation can now be calculated as follows:

Autocorrelation (k=1) = 0.000469/0.0001 = 4.69

Autocorrelation (k=2) = 0.000469/-0.00027 = -1.737

Autocorrelation is commonly referred to as ACF in formulas.

You can also use the =CORREL() formula in excel to achieve the same results.