Home > Tips and Tutorials

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

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.
   
   
   
   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.
   
   
   
   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.