ACFCI - ACF Confidence Interval Function

Calculates the confidence interval limits (upper/lower) for the autocorrelation function.

Syntax

ACFCI(X, Order, K, Method, alpha, upper)

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. k=0 (no lag), k=1 (1st lag), etc.). If missing, the default of k=1 is assumed.

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

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

alpha is the statistical significance level. If missing, the default of 5% is assumed.

upper If true, returns the upper confidence interval limit. Otherwise, returns the lower limit.

Value Description
0 return lower limit
1 return upper limit

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 ACFCI function calculates the confidence limits as: $$\hat\rho_k - Z_{\alpha/2}\times \sigma_{\rho_k} \leq \rho_k \leq \hat\rho_k+ Z_{\alpha/2}\times \sigma_{\rho_k}$$ Where:
• $\rho_k$ is the population autocorrelation function.
• $\sigma_{\rho_k}$ is the standard error of the sample autocorrelation.
• $\hat{\rho_{k}}$ is the sample autocorrelation function for lag k.
• $Z\sim N(0,1)$
• $P(\left|Z\right|\geq Z_{\alpha/2}) = \alpha$
5. For the case in which the underlying population distribution is normal, the sample autocorrelation also has a normal distribution:

$\hat \rho_k \sim N(\rho_k,\sigma_{\rho_k}^2)$

Where:
• $\hat \rho_k$ is the sample autocorrelation for lag k.
• $\rho_k$ is the population autocorrelation for lag k.
• $\sigma_{\rho_k}$ is the standard error of the sample autocorrelation for lag k.
6. Bartlett proved that the variance of the sample autocorrelation of a stationary normal stochastic process (i.e. independent, identically normal distributed errors) can be formulated as: $$\sigma_{\rho_k}^2 = \frac{\sum_{j=-\infty}^{\infty}\rho_j^2+\rho_{j+k}\rho_{j-k}-4\rho_j\rho_k\rho_{i-k}+2\rho_j^2\rho_k^2}{T}$$
7. Furthermore, the variance of the sample autocorrelation is reformulated: $$\sigma_{\rho_k}^2 = \frac{1+\sum_{j=1}^{k-1}\hat\rho_j^2}{T}$$ Where:
• $\sigma_{\rho_k}$ is the standard error of the sample autocorrelation for lag k.
• $T$ is the sample data size.
• $\hat\rho_j$ is the sample autocorrelation function for lag j.
• $k$ is the lag order.

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)
=ACFCI(\$B\$2:\$B\$30,1,1,5%,1) Upper confidence interval for ACF of order 1 (0.37)
=ACFCI(\$B\$2:\$B\$30,1,1,5%,0) lower confidence interval for ACF of order 2 (-0.37)