Multicollinearity Test with Excel

Last Update: March 2, 2020

Multiple regression assumptions consist of independent variables correct specification, independent variables no linear dependence, regression correct functional form, residuals no autocorrelation, residuals homoscedasticity and residuals normality.

This topic is part of Multiple Regression Analysis with Excel course. Feel free to take a look at Course Curriculum.

This tutorial has an educational and informational purpose and doesn’t constitute any type of business, forecasting, trading or investment advice. All content, including spreadsheet calculations and data, is presented for personal educational use exclusively and with no guarantee of exactness of completeness. Past performance doesn’t guarantee future results. Please read full Disclaimer.

No linear dependence or no multicollinearity consists of regression independent variables not being highly correlated.

This is evaluated through multicollinearity test which consists of calculating an inverted correlation matrix of independent variables and assessing its main diagonal values.

  • If main diagonal values were greater than five but less than ten, independent variables might have been highly correlated.
  • If main diagonal values were greater than ten, independent variables were highly correlated.

1. Excel example.

1.1. Multicollinearity test data.

  • Data: S&P 500® index replicating ETF (ticker symbol: SPY) adjusted close prices arithmetic monthly returns, 1 Year U.S. Treasury Bill Yield, 10 Years U.S. Treasury Note Yield, Merrill Lynch U.S. High Yield Corporate Bond Index Yield effective monthly yields, U.S. Consumer Price Index, U.S. Producer Price Index monthly inflations or deflations, West Texas Intermediate Oil prices arithmetic monthly returns, U.S. Industrial Production Index value, U.S. Personal Consumption Expenditures arithmetic monthly changes (1997-2016).

1.2. Multicollinearity test correlation matrix calculation.

  • Multicollinearity test done only on independent variables.

1.3. Multicollinearity test correlation matrix calculation formulas.

1.4. Multicollinearity test inverted correlation matrix calculation.

  • Multicollinearity test done only on independent variables.

1.5. Multicollinearity test inverted correlation matrix calculation formulas.