How to Perform a Breusch-Pagan Test in Excel


Breusch-Pagan Test is used to determine if heteroscedasticity 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.

Raw data in Excel

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 install the free Analysis ToolPak.

Data Analysis Toolpak in Excel

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.

Multiple regression in Excel

This produces the following output:

Regression output in Excel

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:

Predicted values formula for regression in Excel

We will use the same formula to obtain each predicted value:

Predicted regression values in Excel

Next, we will calculate the squared residuals for each prediction:

Calculating residuals in Excel

We will use the same formula to obtain each squared residual:

Squared residual calculation in Excel

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:

Breusch-Pagan regression in Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *