| March 2004 Tip of the Month | | Tip of the Month Archive | |
Recursive Forecasts and Rolling Regressions in Excel
Jason L. Higbee
Research Associate
Federal Reserve Bank of St. Louis
- 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.
- Use Excel’s regression functions (such as INTERCEPT
and SLOPE) then repeat the formula.
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.
- If forecasting, compute the forecast as shown below.
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.
