# ACF - Autocorrelation Function

Calculates the sample autocorrelation function (ACF) of a stationary time series.

## Syntax

ACF(X, Order, K, Method)

X is the univariate time series data (a one dimensional array of cells (e.g. rows or columns)).

Order is the time order in the data series (i.e. the first data point's corresponding date (earliest date=1 (default), latest date=0)).

Order Description
1 ascending (the first data point corresponds to the earliest date) (default)
0 descending (the first data point corresponds to the latest date)

K is the lag order (e.g. 0=no lag, 1=1st lag, etc.). If missing, the default lag order of zero (i.e. Lag=0) is assumed.

Method is the calculation method for estimating the autocorrelation function (0= Sample Autocorrelation (Default), 2=Periodogram-based estimate , 2=Cross corelation).

Value Method
0 Sample autocorrelation method.(default)
1 Periodogram-based estimate. method
2 Cross-correlation method

## Remarks

1. The time series is homogeneous or equally spaced.
2. The time series may include missing values (e.g. #N/A) at either end.
3. The lag order (k) must be less than the time series size, or else an error value (#VALUE!) is returned.
4. The ACF values are bound between -1 and 1, inclusive.
5. Method 1: The sample autocorrelation is computed as:$$\hat{\rho}(h)=\frac{\sum_{k=h}^T{(y_{k}-\bar y)(y_{k-h}-\bar y)}}{\sum_{k=1}^T(y_{k}-\bar y)^2}$$Where:
• $y_{t}$ is the value of the time series at time t.
• $h$ is the lag order.
• $T$ is the number of non-missing values in the time series data.
• $\bar y$ is the sample average/mean of the time series.
$\bar y=\frac{\sum_{i=1}^{N} y_i}{N}$
6. Note that we subtract the full sample mean $\bar y$
7. Method 2: Periodogram estimate.
In this method, we compute the spectral density (periodogram is an estimator) of the sample data set, and use it to compute the sample auto-correlation.
8. Although, the ACF estimate using perodogram-based is usually biased, it generally exhibits a smaller standard error.
9. Method 3: cross correlation method :$$\rho(h)=\frac{\sum_{i=1}^{N-h}(y_i-\bar y)\times (y_{i+h}-\bar y_h)}{\sqrt{\sum_{i=1}^{N-h}(y_i-\bar y)^2 \times\sum_{j=h}^N (y_j-\bar y_h)^2}}$$Where:
• $y_{t}$ is the value of the time series at time t.
• $h$ is the lag order.
• $\bar y=\frac{\sum_{i=1}^{N-h} y_i}{N-h}$
• $\bar y_h=\frac{\sum_{i=h}^N y_i}{N-h}$
• $T$ is the number of non-missing values in the time series data.
• $\bar y$ is the sample average/mean of the time series.
10. Special Cases:
• By definition, $\hat{\rho}(0) \equiv 1.0$

## Examples

Example 1:

A B
1 Date Data
2 1/1/2008 #N/A
3 1/2/2008 -1.28
4 1/3/2008 0.24
5 1/4/2008 1.28
6 1/5/2008 1.20
7 1/6/2008 1.73
8 1/7/2008 -2.18
9 1/8/2008 -0.23
10 1/9/2008 1.10
11 1/10/2008 -1.09
12 1/11/2008 -0.69
13 1/12/2008 -1.69
14 1/13/2008 -1.85
15 1/14/2008 -0.98
16 1/15/2008 -0.77
17 1/16/2008 -0.30
18 1/17/2008 -1.28
19 1/18/2008 0.24
20 1/19/2008 1.28
21 1/20/2008 1.20
22 1/21/2008 1.73
23 1/22/2008 -2.18
24 1/23/2008 -0.23
25 1/24/2008 1.10
26 1/25/2008 -1.09
27 1/26/2008 -0.69
28 1/27/2008 -1.69
29 1/28/2008 -1.85
30 1/29/2008 -0.98

Formula Description (Result)
=ACF(\$B\$2:\$B\$30,1,1) Autocorrelation of order 1 (0.235)
=ACF(\$B\$2:\$B\$30,1,2) Autocorrelation of order 2 (-0.008)
=ACF(\$B\$2:\$B\$30,1,3) Autocorrelation of order 3 (0.054)