How to calculate PACF and produce its correlogram in MS excel

So in this previous post I explained how we can calculate and plot an ACF correlogram in excel. Now we move on to how PACF is calculated and plotted using excel. Firstly, lets review what Partial Autocorrelation Function or PACF is about and how is it different from ACF. While ACF is the correlation between k lagged values of a time series, Partial autocorrelation is a correlation between two k lagged datasets of a time series, T_k-p and T_k such that p is the number of intermediate lags between the two series, when the p lags are controlled for their effect on the k_th lagged series and thus, the autocorrelation we get is a pure autocorrelation between the T_k-p and T_k series.

Also, it is a known fact that that the regression coefficients in a multiple regression represent correlation between the independent variables, while controlling for other variables. Thus, we use multiple regression analysis to calculate the PACF for the AAPL time series we have been working upon in this blog series.

First of all created 10 lagged data series as shown below:

 

While the above series is shown upto 21 data series points, we have considered 100 data points out of the total 365 data points for the series. It would be still better to choose the entire 365 points. First, we created the data series labeled from L1 to L10 by replacing the original data by one cell each successively as also shown above. So the first data series starts at sl. no. 0, the next one at 1 and so on upto 10. This creates cascaded data set, as also shown by the lower end of the dataset below:

 

However, the one lag cascaded parts at both the ends of the data are removed and suppressed by the yellow regions. This is done to ensure, we have a compatible lagged data series for multiple regression in excel.

Next, we ran multiple regression in excel using the command Data Analysis>Regression and then input the original AAPL series column data as the Y range variable and the lagged data in the column lagged data labeled L1 through L10 as the X range variables as shown below.

 

We receive the following output

 

Thus, we have the data for PACF for L1 through L10. in the coefficients  column.

This data is further plotted against the Lags 1 through 10 to arrive at the correlaogram for PACF

 

 

Thus, here we are with the correlaogram for the PACF function using excel.