Bonjour à tous,
voila la situation, j'ai un examen de VBA à rendre pour demain (en anglais)
et autant vous dire que je suis aussi bon en VBA que Domenech en sélectionneur...
J'imagine que personne ne va me répondre mais bon je suis obligé de tenter... Alors voila mon énoncé, si jamais quelqu'un est assez fou pour le faire, tant mieux !!!!! (pj: tableau excel fourni...)
Using the stock return data in the Excel file “Final Assignment 3 (Option 1) – Data.xlsx”, write a VBA function to estimate and test the CAPM using the methodology illustrated in the course presentation. The spreadsheet contains monthly excess stock (R_i) and market returns (R_M) above the risk free rate.
The following steps need to be performed:
Regress the time series of excess stock returns on the market excess returns labeled “FTSE All Share” using a rolling window of 60 months, i.e. using months 1-60 in the first regression, then months 2-61 in the second regression and so on for each stock. Make sure to include an intercept term (α_i) in the time series regressions as well. So the regression equation should look as follows:
R_i=α_i+β_(i,M) R_M+ε_i (1)
After that, collect the times series of slope coefficients (β_(i,M)) for each stock and regress stock excess returns (R_(i,t+1)) on the slope coefficients (β_(i,M,t)) in monthly cross-sectional regressions. The regression equation should look as follows
R_(i,t+1)=a_t+b_t β_(i,M,t)+ε_(t ) (2)
Note that equation (2) is a cross-sectional regression as opposed to a time series regression in equation (1), i.e. every month, month t+1 stock returns are regressed on month t betas of all stocks.
Equation (2) results in two time series of regression coefficients, a and b. Compute and interpret t -statistics on each of these time series and conclude what the results mean regarding the validity of the CAPM. Compute t-statistics as follows:
t=(mean(x))/((std(x))/(N-1))
where x is the variable examined (the regression coefficients a and b in our case) and N is the number of observations in each time series.
Perform all computations in a VBA function that takes the stock excess returns array and market excess return array as inputs and computes a 2x2 output array containing the mean regression coefficients a and b as well as their associated t-statistics. Verify your function’s results by performing the above steps manually in Excel.
Note: As the returns shown in the spreadsheet are over a period of rising markets, you can assume that stock returns are positively related to their betas if the CAPM holds.
voila la situation, j'ai un examen de VBA à rendre pour demain (en anglais)
et autant vous dire que je suis aussi bon en VBA que Domenech en sélectionneur...
J'imagine que personne ne va me répondre mais bon je suis obligé de tenter... Alors voila mon énoncé, si jamais quelqu'un est assez fou pour le faire, tant mieux !!!!! (pj: tableau excel fourni...)
Using the stock return data in the Excel file “Final Assignment 3 (Option 1) – Data.xlsx”, write a VBA function to estimate and test the CAPM using the methodology illustrated in the course presentation. The spreadsheet contains monthly excess stock (R_i) and market returns (R_M) above the risk free rate.
The following steps need to be performed:
Regress the time series of excess stock returns on the market excess returns labeled “FTSE All Share” using a rolling window of 60 months, i.e. using months 1-60 in the first regression, then months 2-61 in the second regression and so on for each stock. Make sure to include an intercept term (α_i) in the time series regressions as well. So the regression equation should look as follows:
R_i=α_i+β_(i,M) R_M+ε_i (1)
After that, collect the times series of slope coefficients (β_(i,M)) for each stock and regress stock excess returns (R_(i,t+1)) on the slope coefficients (β_(i,M,t)) in monthly cross-sectional regressions. The regression equation should look as follows
R_(i,t+1)=a_t+b_t β_(i,M,t)+ε_(t ) (2)
Note that equation (2) is a cross-sectional regression as opposed to a time series regression in equation (1), i.e. every month, month t+1 stock returns are regressed on month t betas of all stocks.
Equation (2) results in two time series of regression coefficients, a and b. Compute and interpret t -statistics on each of these time series and conclude what the results mean regarding the validity of the CAPM. Compute t-statistics as follows:
t=(mean(x))/((std(x))/(N-1))
where x is the variable examined (the regression coefficients a and b in our case) and N is the number of observations in each time series.
Perform all computations in a VBA function that takes the stock excess returns array and market excess return array as inputs and computes a 2x2 output array containing the mean regression coefficients a and b as well as their associated t-statistics. Verify your function’s results by performing the above steps manually in Excel.
Note: As the returns shown in the spreadsheet are over a period of rising markets, you can assume that stock returns are positively related to their betas if the CAPM holds.
