XLSTAT: Cointegration Test on Time Series in Excel

 

Dataset for Example

This tutorial uses the (attached) dataset extracted from the database made available by the Bank of England. It is made of two time series:

• The monthly average 12 month Forward exchange rate of US$ into Sterling labeled XUMADSY,

• The monthly average Spot exchange rate US$ into Sterling labeled XUMAUSS.

Both series cover a time period ranging from January 1979 to December 1999.

 

Goal of this tutorial

The exchange rate expresses the price of one currency, the foreign one, in terms of another, the domestic one. In our tutorial, we will be considering the 12 month Forward and the Spot exchange rates of the US dollar in terms of the British Pound over the period 1979-1999. On the one hand, the 12 month forward exchange rate is a hedging contract in which two parties agree at present time upon the rate at which they will exchange the two currencies in the future (12 months ahead). On the other hand, the Spot exchange rate sets the present relative value of the two currencies.

In the context of the Covered Interest Parity (CIP), the log of the Spot and Forward exchange rates is expected to be related to the foreign to domestic differential of interest rates by the following relation:

lnForward - lnSpot = rDiff. where lnForward is the log of the Forward exchange rate, lnSpot is the log of the Spot exchange rate and rDiff is the differential interest rate.

Under the hypothesis of efficient financial markets, a situation of risk-less profit where one would borrow into a foreign currency, invest at risk-free rate in this currency and lock its selling price into domestic currency in a forward sale should not be possible. As a consequence, the interest rate differential is expected to be I(0) (or stationary). As it is generally accepted in the international finance literature that both the Spot and Forward exchange rates are integrated of order 1 (noted or I(1)), a cointegration relationship should exist between the log Spot and Forward exchange rates for the CIP relationship stated above to hold.

In the following tutorial, we should first transform our time series and test their I(1) status. Then, we should assess that those two I(1) processes can combine to a I(0) process, in other words that the two-time series are cointegrated, by the mean of a cointegration test following Johansen's approach.

 

Transformation of time series

First, we have to transform the series extracted from the database of the Bank of England to obtain their log values.

 

Setting Up

• Open XLSTAT.

• Click on the Forecasting/ Time series analysis menu and select the Time series transformation feature.

• The Time series transformation dialog box appears.

In the General tab:

• Select the Time series on the Excel sheet.

• The option Series labels is activated because the first row of the selected data contains the headers of the variables.

In the Options tab:

• Select Box-Cox transformation and set the value of Lambda to 0 to apply a log transformation to the series.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

First the 12-month Forward exchange rate (XUMADSY) and its log transformation (Box-Cox(XUMADSY)):

Followed by the Spot exchange rate (XUMAUSS) and its log transformation (Box-Cox(XUMAUSS)):

 

Descriptive analysis

Now that our time series are ready, we should apply a descriptive analysis on them in order to control that they present the expected characteristics.

 

Setting Up

• Click on the Forecasting/ Time series analysis menu and select the Descriptive analysis feature.

• Once you've clicked on the button, the Descriptive analysis dialog box will appear.

• In the General tab, select the two transformed series, Box-Cox(XUMADSY) and Box-Cox(XUMAUSS), with the help of the Ctrl key to select multiple fields.

• In the Outputs tab, make sure the Autocorrelations and the Partial Autocorrelations options are activated as shown below.

• In the Charts tab, activate the Autocorrelogram (ACF) and Partial Autocorrelogram (PACF) options:

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The descriptive statistics as well as the ACF and PACF are displayed for the two series.

First, as shown below, the ACF of the Box-Cox(XUMADSY) series exhibits a large number of significant lags.

Looking at the PACF, it seems that those high order correlations are explained mainly by the 1 lag autocorrelation as it is the only one strongly significant in the PACF.

This behavior is quite compatible with the expected I(1) time series.

The situation is highly similar for the second time series named Box-Cox(XUMAUSS) as shown below with a strongly significant lag-1 in the PACF.

 

Unit root tests

After those preliminary checks, we are ready to test the I(1) aspect of our time series. We will apply two unit root tests on our transformed time series: a Dickey-Fuller test (DF) and a Phillips-Perron test (PP).

 

Setting Up

• Click on the Forecasting/ Time series analysis menu and select the Unit root and stationarity tests feature.

In the General tab:

• In the time series field, select the two transformed series, Box-Cox(XUMADSY) and Box-Cox(XUMAUSS), and activate the Dickey-Fuller and the Phillips-Perron tests as shown below.

• Regarding the model selection, the model with an intercept is the one that best describes our data. You should therefore select the intercept option for both the DF and PP tests.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The results are displayed for the two series.

For the Box-Cox(XUMADSY) series, neither the DF nor the PP test reject the null hypothesis of a presence of a unit root in the data generating process.

For the second series, the confidence level that one should not reject the null hypothesis is even stronger:

To conclude on this part, it seems that the log of the Forward and Spot exchange rates are both I(1). This is in agreement with what is expected from the economic theory.

We should now check that a linear relationship between those two I(1) series that produces an I(0) series exists.

 

Cointegration test

To test the existence of this relationship, we will perform a cointegration test following Johansen's approach. 

 

Setting Up

• Click on the Forecasting/ Time series analysis menu and select the Cointegration test feature.

• The Cointegration test dialog box appears.

In the General tab: 

• First, select our two transformed time series, Box-Cox(XUMADSY) and Box-Cox(XUMAUSS). Then, we must select a model for the test.

• Both series have nonzero means with no drift and the cointegration relationship as stated at the beginning of this tutorial is not expected to have a linear trend. Therefore, the  H1* restriction seems suitable for our test.

• Finally, we don't know about the VAR order that best apply to our group of series. We will then let XLSTAT estimate its value by selecting the Automatic option as shown below.

In the Options tab:

• Select a model and a criterion for the VAR order estimation. Again, a model with intercept seems appropriate and we will use the AIC criterion.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The  first table (see below) displays the result for the VAR order estimation. In bold, the minimum AIC value gives a VAR order of 3 or VAR(3) for our system which means 2 lags in difference for the Vector Error Correction Model (VECM). We can check that there is a good agreement between the four criteria.

Then the results for both tests, the max eigen test (or lambda test) and the trace test are displayed. Both tests agree on the rank(1) of cointegration of the system or equivalently on the existence of 1 cointegrating relationship between the two series. P-values and critical values for both tests are estimated using the surface regression approach described in MacKinnon-Haug-Mechelis(1998).

Finally, the factorization of the cointegrating matrix is given in the form of the impact matrix (alpha) and the cointegration coefficients (beta) following the normalization proposed by Johansen:

 

Conclusion

We have been able to transform the two time series and perform subsequent tests to validate the hypothesis of two I(1) series.

Then, we performed a cointegration test following Johansen's approach that lead us to conclude that 1 cointegrating relationship might exist between the two series.

Therefore, for the time period running from 1979 to 1999, the log 12 month Forward and log Spot exchange rates series of US$ into Sterling exhibit one cointegrating relationship as expected by the Covered Interest Parity hypothesis. Now, you should try your own analysis using another dataset. What about the same dataset but up to the present days? This might lead to some surprising results.