# 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.

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.

## Free Guides - Getting Started with R and Python

Enter your name and email address below and we will email you the guides for R programming and Python.