0
$\begingroup$

Hi Can someone please let me know whether cross sectional regression is possible in Excel?

I'm trying to evaluate CAPM from MacBeth regression.

I need to run the cross sectional regression, but seems it is not possible to do it in excel. Is this true?

| cite | improve this question | | | | |
$\endgroup$
2
$\begingroup$

There is probably some built-in way to do it, but you can always do linear regression step by step with matrices. If your dependent variable is a column $Y$ and your independent variables are a set of columns $X$, the vector of regression parameters is:

$(X'X)^{-1}X'Y$

So first you would use the TRANSPOSE function to transpose matrix $X$, which will give you $X'$; you can multiply this with the original $X$ to get $X'X$. Then you would take the inverse of the resulting matrix with the MINVERSE function to get $(X'X)^{-1}$. You also need to multiply $X'$ with the column $Y$ to get $X'Y$.

If you did everything correctly, multiplying your $(X'X)^{-1}$ with your $X'Y$ should give you a vector of regression coefficients, where the first coefficient is associated with the first column of your matrix of independent variables $X$.

One last thing. If you want to have a constant in your regression model, don't forget to add a column of 1s to your matrix of independent variables.

| cite | improve this answer | | | | |
$\endgroup$
  • $\begingroup$ Hi Alvaro. thanks alot. Is it possible to do for very large metrices. like 300x100 practically. I'm trying to use this to a large portfolio. $\endgroup$ – Hiru Jan 30 '19 at 11:29
  • $\begingroup$ With 300 observations of 100 independent variables, your $X'X$ matrix will be 100x100, well within the limits of Excel. By the way, they are closing your question because your are only supposed to ask about statistics here, not how to do things in specific programs. You can ask about that in StackOverflow. $\endgroup$ – suckrates Jan 30 '19 at 12:56
  • $\begingroup$ Thanks Alvaro. One thing. For the calculations, (to show the output) Do I always need to select the specific set of columns and rows in the excel? $\endgroup$ – Hiru Jan 30 '19 at 13:10
  • $\begingroup$ It is easiest if you know how to name matrices and maybe practice first with a small matrix, say 2x3. Select the whole matrix and right click on it. In the dropdown menu you should see an option to give it a name. Once you give it a name, select an empty area that is 3x3 and type the formula =MINV(MMULT(MTRANS(X);X)), supposing you called the matrix "X". At least that's what the functions are called in my German Excel. In the English version I think they will be MINVERSE and TRANSPOSE instead of MINV and MTRANS, respectively. $\endgroup$ – suckrates Jan 30 '19 at 13:21
  • $\begingroup$ Oh and after you type the formula, you should do SHIFT+CTRL+ENTER, not just ENTER. $\endgroup$ – suckrates Jan 30 '19 at 13:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.