Table of Contents
The Breusch-Pagan test is a statistical test used to assess the null hypothesis of homoscedasticity in a linear regression in Excel. It is performed by calculating the lagrange multiplier statistic and comparing it to a chi-squared distribution with degrees of freedom equal to the number of independent variables in the regression. The result of the test will indicate whether the null hypothesis of homoscedasticity should be accepted or rejected.
A is used to determine if is present in a regression analysis.
This tutorial explains how to perform a Breusch-Pagan Test in Excel.
Example: Breusch-Pagan Test in Excel
For this example we will use the following dataset that describes the attributes of 10 basketball players.
We will fit a multiple linear regression model using rating as the response variable and points, assists, and rebounds as the explanatory variables. Then we will perform a Breusch-Pagan Test to determine if heteroscedasticity is present in the regression.
Step 1: Perform multiple linear regression.
Along the top ribbon in Excel, go to the Data tab and click on Data Analysis. If you don’t see this option, then you need to first .
Once you click on Data Analysis, a new window will pop up. Select Regression and click OK. Fill in the necessary arrays for the response variables and the explanatory variables, then click OK.
This produces the following output:
Step 2: Calculate the squared residuals.
Next, we will calculate the predicted values and the squared residuals for each response value. To calculate the predicted values, we will use the coefficients from the regression output:
We will use the same formula to obtain each predicted value:
Next, we will calculate the squared residuals for each prediction:
We will use the same formula to obtain each squared residual:
Step 3: Perform a new multiple linear regression using the squared residuals as the response values.
Next, we will perform the same steps as before to conduct multiple linear regression using points, assists, and rebounds as the explanatory variables, except we will use the squared residuals as the response values this time. Here is the output of that regression:
Step 4: Perform the Breusch-Pagan Test.
Lastly, we will perform the Breusch-Pagan Test to see if heteroscedasticity was present in the original regression.
First we will calculate the Chi-Square test statistic using the formula:
X2 = n*R2new
where:
n = number of observations
R2new = R Square of the “new” regression in which the squared residuals were used as the response variable.
In our example, X2 = 10 * 0.600395 = 6.00395.
Next, we will find the p-value associated with this test statistic. We can use the following formula in Excel to do so:
=CHISQ.DIST.RT(test statistic, degrees of freedom)
In our case, the degrees of freedom is the number shown for df of regression in the output. In this case, it’s 3. Thus, our formula becomes:
=CHISQ.DIST.RT(6.00395, 3) = 0.111418.
Because this p-value is not less than 0.05, we fail to reject the null hypothesis. We do not have sufficient evidence to say that heteroscedasticity is present in the original regression model.