1
$\begingroup$

My dissertation requires me to evaluate fund performance, and for that I need to find the alpha for each fund. I have 173 funds total. I have all the inputs for the 3-factor model, and I realise running a regression and finding the intercept is the fund's alpha - however, is there a faster way of doing this due to the number of funds I have? I do not want to run a regression for every single fund, so is there a quicker way of doing this in Excel?

Thanks in advance.

$\endgroup$
  • $\begingroup$ What's your math/programming background? Been exposed to matrices and linear algebra? Done anything in R? MATLAB? or Python? $\endgroup$ – Matthew Gunn Jan 25 '18 at 14:08
  • $\begingroup$ I have limited math and programming background (more qualitative work) so I wouldn't know how to use Matlab etc. Is there a way I could calculate the alphas in excel? $\endgroup$ – Connor B Jan 25 '18 at 14:21
4
$\begingroup$

In the long run, you'd probably be better off learning a real programming language like Python, R, or MATLAB. While you can do this in Excel using mmult, transpose, and minverse, it's rather horrible.

In any case, you should know about the mathematical idea of a matrix, matrix multiplication, and the inverse of a matrix. (Multiplying by the inverse of a matrix is solving a linear system.)

In my notation, capital letters (eg. A) will be matrices, bold letters (eg. $\mathbf{b}$) will denote column vectors, and lower case letters will denote scalars (i.e. regular numbers). $A_{ij}$ will refer to the $i$th row of column $j$ of matrix $A$.

The brief recipe:

  • Let $R$ be a $n$ by $k$ matrix of returns where we have $n$ time periods and $k$ assets.
  • An excess return is a return in excess of the risk free rate. First you need to compute a matrix $Y$ of excess returns. Let $r^f_t$ denote the risk free rate at time $t$. The excess return of asset $i$ at time $t$ is the return minus the risk free rate: $$ Y_{ti} = R_{ti} - r^f_t$$
  • You also need a $n$ by $3$ matrix $F$ of the three Fama-French factors. (Note these are already zero cost portfolios since the risk free rate or other portfolio return has been sbutracted off.) Form a matrix $X$ by pre-appending a column of 1s. $$ X = \begin{bmatrix} \mathbf{1} & F \end{bmatrix}$$

Then your solution to running those 173 regressions is given by: $$ \hat{B} = (X'X)^{-1} X'Y$$ The alphas will be in the first row of $\hat{B}$. The residuals are given by: $$ \hat{U} = Y - X \hat{B}$$ For each column of $\hat{U}$, calculate estimate of the variance of the error terms as:

$$ \hat{\sigma}^2_i = \frac{1}{n - k - 1} \sum_{t=1}^n \hat{U}_{ti}^2 $$

Let $\hat{\mathbf{d}} = \operatorname{diag}((X'X)^{-1})$ be the diagonal elements of $(X'X)^{-1}$. Your standard error for the alpha estimate for security $i$ would be:

$$ \hat{s}_{i1} = \sqrt{\hat{\sigma}^2_i \hat{d}_1}$$ Standard error for estimate of beta would be (assuming the market excess return is in the 2nd column of X): $$ \hat{s}_{i2} = \sqrt{\hat{\sigma}^2_i \hat{d}_2}$$

$\endgroup$
  • $\begingroup$ BTW, you can do better than this in terms of sensible standard-errors. You really should do heteroskedasticity robust standard-errors (rather than the homoskedastic assuming standard-errors here) but this recipe is complicate enough already. $\endgroup$ – Matthew Gunn Feb 24 '18 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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