Objective
We now show how to make predictions from a Ridge regression model. In particular, we will make predictions based on the Ridge regression model created for Example 1 with lambda = 1.6. The raw input data is repeated in range A1:E19 of Figure 1 and the unstandardized regression coefficients calculated in Figure 2 of Ridge Regression Analysis Tool are repeated in range G2:H6 of Figure 1.
Figure 1 – Ridge regression predictions
The predictions for the input data are shown in column J. In fact, the values in range J2:J19 can be calculated by the array formula
=H2+MMULT(A2:D19,H3:H6).
Alternatively, they can be calculated by the array formula
=RidgePred(A2:D19,A2:D19,E2:E19,H9)
as defined below, or by the array formula =RegPredCC(A2:D19,H2:H6).
We can also calculate the residuals, as shown in column K. E.g. the residual for the first sample (cell K2) can be calculated by the formula =E2-J2. The sum of squares of these values (cell K2) is 554.91 as calculated by the formula =SUMSQ(K2:K19). Now, MSE = SSE/(n-k-1) = 554.91/(18-4-1) = 42.68541 (cell H8), which is the same value shown in cell X11 of Figure 2 of Ridge Regression Analysis Tool. This can also be calculated by the formula RidgeMSE(A2:D19,E2:E19,H9), as defined below.
We can use the same approach to calculate predicted values for x data not in the original sample. E.g. the predicted y value of 41.09 (cell E21) for the data in range A21:D21 can be calculated via the worksheet formula =H2+MMULT(A21:D21,H3:H6) or alternatively by the worksheet formula =RidgePred(A21:D21,A2:D19,E2:E19,H9) or by the worksheet formula =RegPredCC(A22:D22,H2:H6).
Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack provides the following functions.
RidgeMSE(Rx, Ry, lambda) = MSE of the Ridge regression defined by the x data in Rx, y data in Ry, and the given lambda value.
RidgePred(Rx0, Rx, Ry, lambda): returns an array of predicted y values for the x data in range Rx0 based on the Ridge regression model defined by Rx, Ry, and lambda; if Rx0 contains only one row then only one y value is returned.
References
bquanttrading (2015) Ridge regression in Excel/VBA
https://asmquantmacro.com/2015/12/11/ridge-regression-in-excelvba/
Marquardt, D. W. and Snee, R. D. (1975) Ridge regression in practice. The American Statistician
https://www.jarad.me/stat615/papers/Ridge_Regression_in_Practice.pdf
PennState (2018) Ridge regression. Applied Data Mining and Statistical Learning
https://online.stat.psu.edu/stat857/node/155/