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

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

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.

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.

Example 1:

Files Examples

Related Links

• P.A.P Moran; Testing of Significance of Correlation between Time Series, page 397; Biometrica, 1947
• Wikipedia - Confidence Interval

References

• D. S.G. Pollock; Handbook of Time Series Analysis, Signal Processing, and Dynamics; Academic Press; Har/Cdr edition(Nov 17, 1999), ISBN: 125609906
• James Douglas Hamilton; Time Series Analysis; Princeton University Press; 1st edition(Jan 11, 1994), ISBN: 691042896
• Tsay, Ruey S.; Analysis of Financial Time Series; John Wiley & SONS; 2nd edition(Aug 30, 2005), ISBN: 0-471-690740
• Box, Jenkins and Reisel; Time Series Analysis: Forecasting and Control; John Wiley & SONS.; 4th edition(Jun 30, 2008), ISBN: 470272848
• Walter Enders; Applied Econometric Time Series; Wiley; 4th edition(Nov 03, 2014), ISBN: 1118808568