| Tip of the Month Archive |

Recursive Forecasts and Rolling Regressions in Excel

Jason L. Higbee
Research Associate
Federal Reserve Bank of St. Louis

  1. Organize your regression/forecast model. Here we have created a factor (known_x’s) by lagging our response (known_y’s), Moody's Seasoned Aaa Corporate Bond Yield from FRED II.



  2. Use Excel’s regression functions (such as INTERCEPT and SLOPE) then repeat the formula.

    screenshot

    As pictured the formula calculates a one-step-ahead forecast of the intercept term with a factor loading of all previous complete observations. Choosing the desired regression function and removing the "$" before the row numbers allows the estimation of rolling regression statistics. Repeat the formula to recursively update the forecast or roll the regressions.

  3. If forecasting, compute the forecast as shown below.

    screenshot

    Alternatively, we could have used FORECAST as the regression function with "B24" as the x input and the known_y’s and known_x’s as specified in step 2.

Recently Viewed Series


Subscribe to our newsletter for updates on published research, data news, and latest econ information.
Name:   Email:  
Twitter logo Google Plus logo Facebook logo YouTube logo LinkedIn logo