How To Perform A Breusch-Pagan Test In Excel

This guide will explain how to perform a Breusch-Pagan test in Excel to check for heteroscedasticity in a regression model.

In statistics, the concept of heteroscedasticity refers to a situation where each predictor variable in a model has a different range of variability. 

A classic example of heteroscedasticity can be found when modeling income and expenditure on meals. As an individual’s income increases, the variability of food consumption is likely to increase. 

A person with a low income would spend a constant amount on food daily while a wealthier person may occasionally buy inexpensive meals and splurge on more expensive meals at other times.

The Breusch-Pagan test checks for heteroscedasticity by comparing the variance of the residuals of a regression model to the predicted values of the model. The test calculates a chi-square statistic based on this comparison, and the resulting p-value is used to determine whether or not there is evidence of heteroscedasticity.

We can perform the Breusch-Pagan test in Excel by using the Regression tool that comes with the Analysis Toolpak add-in. From the tool’s output, we can find the value of the squared residuals for each observation.

The squared residuals can then be used to calculate the chi-square statistic. If the p-value of the statistic is low enough, we can conclude that heteroscedasticity is present in our model.

In this guide, we will provide a step-by-step tutorial on how to perform a Breusch-Pagan test in Excel. We will cover how to find the squared residuals using the Regression tool and how to compute the chi-square statistic using the CHISQ.DIST.RT function.

Let’s dive right in!

A Real Example of a Breusch-Pagan Test in Excel

Let’s explore a simple example where we may need to perform a Breusch-Pagan test in Excel.

In the table seen below, we have a dependent variable y that we want to predict using our independent variables x1, x2, and x3.

breusch-pagan test

 

We want to know if the dataset shows signs of heteroscedasticity that may affect the accuracy of our regression model.

To conduct the Breusch-Pagan test, we need to first estimate the regression model, then calculate the squared residuals of the model.

find squared residuals

 

The test then regresses the squared residuals on the independent variables of the original model and obtains an R-squared value. 

We will plug this R-squared value into the CHISQ.DIST.RT function to return the p-value of the given statistic. If the p-value is less than the chosen significance level, then we have sufficient evidence to conclude that heteroscedasticity is present in our model.

use chisq.dist.rt function

 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to perform the Breusch-Pagan test yourself, head over to the next section to read our step-by-step breakdown on how to do it!

How To Perform A Breusch-Pagan Test In Excel

This section will guide you through each step you need to perform a Breusch-Pagan test in Excel.

Follow these steps to determine whether your regression model is affected by heteroscedasticity:

  1. First, we’ll have to activate the Analysis Toolpak add-in.
    In the File tab, click on Options to display the Excel Options dialog box.

    Open Excel options
    Head over to the Add-ins tab and click on the button labeled ‘Go…’ to proceed.
  2. Check the option labeled ‘Analysis ToolPak’ and click on OK.

    activate analysis toolpak

  3. In the Data tab, click on the Data Analysis option. Scroll through the list of analysis tools until you find the Regression option. Click on OK to proceed.

    we'll need to use the regression tool for breusch-pagan test

  4. Set the Input Y Range to refer to the dependent variable of your dataset. Afterward, set the Input X Range to refer to all the independent variables in your dataset. Check the ‘Labels’ option if your selections include the column headers.

    use regression tool with dependent and independent variables
    Select an output range, then click on OK.
  5. The tool should now return a summary output in the indicated output range. Take note of the coefficients of each term in the equation.

    summary output of regression tool will include coefficients of each term

  6. Use the coefficients to create a formula to find the predicted value of each observation.

    use coefficients to get predicted value

  7. We can find the value of the squared residuals by squaring the difference between the observed y value and the predicted y value from the previous step.

    breusch-pagan test

  8. We’ll use the Regression tool once more to find the R Square value. Instead of the observed y value, we will instead use the squared residuals as the input Y range.

    breusch-pagan test

  9. To conduct the Breusch-Pagan test, we must identify a few statistics. First, we must identify the number of observations found in our dataset. Next, we’ll need the R-square value output from the regression tool in the previous step.

    breusch-pagan test
    We can find the Chi-Squared Test statistic by multiplying the R Square value by the number of observations.
  10. Lastly, we’ll use the CHISQ.DIST.RT function to find the associated p-value of our test statistic.

    breusch-pagan test
    Since our p-value is not less than 0.05, we do not have sufficient evidence to prove that heteroscedasticity is present in our model.

 

 

This tutorial should cover everything you need to know to perform a Breusch-Pagan Test in Excel.

We’ve explained how to use the Analysis Tookpak add-in to perform a multiple linear regression on a dataset. The coefficients of the regression will allow us to find the squared residual value of each observation.

We can use the squared residuals to create a new regression that we can perform a chi-squared test on to test for heteroscedasticity.

The Breusch-Pagan test is just one type of statistical test you can perform in Excel. For example, another way to analyze variance is through the Levene’s Test. You can read our step-by-step guide on how to use Levene’s Test to assess whether the variances of two or more groups are equal.

That’s all for this guide! If you’re still looking to learn more about Microsoft Excel, be sure to check out our library of Excel resources, tips, and tricks! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

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

You May Also Like