Build a Better Process

# Rolling Regression Definition, Tutorial and Examples in Excel

Learning linear regression is a point-in-time exercise. Implementing linear regression becomes a moving-period exercise.
1. Define - Define rolling regression for financial modeling.
2. Context - Use rolling regression in a sentence.
3. Video - See the video for examples in Excel.
4. Script - Follow along with the transcript below.
5. Quiz - Test yourself.
Updated: February 18, 2021
Most college textbooks start with point-in-time regressions to introduce the topic, but when building investment models for production, rolling-period analysis takes on greater importance.

/ factorpad.com / fin / glossary / rolling-regression.html

## When to use Rolling Regressions in Finance

Intermediate

Rolling Regression is an analysis of the changing of relationships among variables over time, specifically of measures generated from a linear regression. Visualizing regression outputs like correlation, r-squared, beta and the standard error over time can be helpful in the analysis of risk for stocks, portfolios and factors.

Synonym: moving-period regression, rolling window regression

For context, recall that measures generated from a regression in Finance change over time. As an example, recall each stock has a beta relative to a market benchmark. Imagine a stock with a beta of 1.50, which means it is more sensitive to the ups and downs of the market. What if that company merged with a stable company with a historic beta of 0.60? In this case it may take months for the time series of observed monthly returns to change the beta. In this case it could be incumbent on the analyst to change the beta.

Beta offers a good example because it is used in many calculations in Finance. The weighted average cost of capital (WACC) in corporate finance utilizs beta, as does the CAPM calculation of the expected return. The single-index model relies on beta as well. A rolling regression of beta will highlight changes over time and offer the wise analyst information on what beta to use for future periods.

### In a Sentence

Ann:  With this rolling regression of alpha, notice how it all came in 1 year?
Jim:  Great insight Ann, keep 'em rolling.

### Video

This video can be accessed in a new window or App , at the YouTube Channel or from below.

Rolling regression definition for investment modeling (4:44)

### Video Script

The script includes two sections where we visualize and demonstrate the concept of a rolling regression.

#### Visualize

We're sitting here in Excel and this is a snippet from our boot camp course (Quant 101).

Here in blue we have 24 monthly returns on a stock Merck and a Market portfolio. Think of this as a 2-year window over which we generated the regression statistics: alpha, beta, standard error, correlation and R-squared.

Now imagine sliding that window down one month, the window now goes from May 2003 to April 2005 and the statistics change.

Our goal is to make our investment models more realistic and accurate, so they adapt to changing relationships, and that we also know about upcoming changes.

Beta, for example, comes from a regression and is used to set expectations on the return and risk of stocks. Notice how the beta changed over time, from 1.43 to 1.28? That's a pretty big move for six months. Let's chart it.

What happened? Well granted this is only a 24-month period, nonetheless we'd have to look at the periods that rolled on here, and rolled off here.

Let's solidify this by creating the rolling regression.

#### Demonstrate

There are four ways to generate regression statistics in Excel, and we'll use the function method here.

First for alpha, or the intercept, use `=INTERCEPT`, open parenthesis, the y-variable Merck, comma, the x-variable the Market, closed parenthesis and Enter, for an alpha of -0.0245, which is not good performance, but look at this cell here, Merck lost 26% in one month alone. Identifying outliers like this can help with your analysis skills.

Second, for beta, use the same procedures and ranges of data except use the function called `=SLOPE`.

Third is the standard error, which is helpful for finding specific risk of the stock Merck, and for that we'll use the function `=STEYX`.

Fourth we'll use the function `=CORREL` and the same procedures for correlation, which is the interpretable measure with a scale of -1 to +1.

And finally, R-squared or correlation squared for a range of 0 to 1.

Now copy that row of regression statistics down and you've done it. Review these with line or bar charts and your portfolio analysis and model building skills are sure to improve.

### Quiz

Rolling regressions help the analyst spot data outliers. | True or False?

True

Which of the following is least improved with the analysis of rolling regression charts. | Time Value of Money or Risk Rotation or Style Drift?

Time Value of Money

Still unclear on rolling regression? Leave a question in the comments section on YouTube or check out the Quant 101 series with 27 free Excel tutorials.

### Related Terms

Our trained humans found other terms in the category statistics for finance you may find helpful.

## What's Next?

• To see all Glossary terms, click Outline.
• To learn about the Risk Return Plot, hit Back.
• Support learning for all, regardless of wealth, click Tip.
• To stick with Statistics and the term R-Squared, click Next.

/ factorpad.com / fin / glossary / rolling-regression.html

rolling regression
rolling window
rolling window regression
regression testing
alpha testing
stock beta regression
portfolio beta regression
rolling beta
rolling alpha
regression analysis example
rolling r-squared
rolling correlation
regression intercept
standard error
outlier analysis
stock regression analysis
portfolio regression analysis
stock factors
investment modeling
rolling regression excel tutorial
regression in excel

A newly-updated free resource. Connect and refer a friend today.