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?

$\endgroup$

closed as off-topic by Peter Flom Jan 30 at 11:56

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question appears to be off-topic because EITHER it is not about statistics, machine learning, data analysis, data mining, or data visualization, OR it focuses on programming, debugging, or performing routine operations within a statistical computing platform. If the latter, you could try the support links we maintain." – Peter Flom
If this question can be reworded to fit the rules in the help center, please edit the question.

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.

$\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 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$ – Alvaro Fuentes Jan 30 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 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$ – Alvaro Fuentes Jan 30 at 13:21
  • $\begingroup$ Oh and after you type the formula, you should do SHIFT+CTRL+ENTER, not just ENTER. $\endgroup$ – Alvaro Fuentes Jan 30 at 13:22

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