Excel Model Details

Excel Model Details

Model Inputs tab

This tab is designed to handle various inputs into the model. Important variables include:

·  Regression Starting Row – the row for the first data point in the regression

·  Regression Forecasting Start Row – the row before the out-of-sample forecast is to begin

·  Regression Ending Row – the row for the last data point in the sample

·  Regression Look back period – the size of the regression sample, which enables a rolling regression

·  Yield Type – select to use a predefined average (3 months) or a single point

·  Slope of Term Structure type – select to use a normalized or regular data points

·  Slope / Credit T-stat offset – the sample size of the normalized statistic, if selected

Additionally, there’s a “Run Model” button that will initialize the calculations

Yields

This tab primarily reflects a holding place for the data that is utilized, as well as a space to transform variables

List

This is the spreadsheet that is used to setup the regression, as well as arrange all the data relevant to the yield curve.

Columns of data for the regression are indicated in row 2, cells A-E. The macro works by modifying the rows in these fields, as well as the data in the BG column with regards to slope of the term structure.

Linest

Where the regression formula is stored and the results of the regression are placed. The slope of the term structure that was used is indicated in column H, while the month-year code is indicated in column I.

The R-square and other regression statistics represent the underlying model that produces the predicted Y for the period.

Summary Stats

This tab includes the MSE of each model, Predicted and Actual correlations, Averaged T-statistics over the span of the sample, as well as a snapshot of the latest underlying regression. Finally, a graph detailing the evolution of the R-square is included.

Model Ranks

The tab is used to weight the predictions from the various model to arrive at a final prediction (Column AQ). The final prediction is calculated based upon a weighted average of the R-square for each underlying model, and models that produce an R-square that is less than half the average of the group are held out for the period.

Best Model

The final predicted return from the Model Ranks is imported into this tab, and the strategy is implemented.

Key outputs from this tab include the R-square of the forecasted vs actual returns (cell C1), as well as the Sharpe Ratio of the strategy (cell T5).

Regression

Regression output based upon running the actual returns on our forecasted returns.

Macro Description

formulaRegression – this macro runs a rolling regression over the sample period for a single term structure slope and adds the results, as well as prediction errors, to the Linest tab.

collectRegressionsOne – This macro iterates through all the slopes of the term structure, calling the formulaRegression sub each time.

getRanges – finds the appropriate ranges to create the model correlations in the summary stats tab

rollingError – deprecated; formerly used as a means to select models.

createTstat – used to create the normalized slope of the term structure T-stats with a varying sample window.

createTstatBaa – used to create the Baa spread T-stat with a varying sample window.