4
$\begingroup$

This question is not very technical. I have a file with holdings (both for the fund and the benchmark) of a number of securities and need to do a style allocation analysis. For these securities, I have different 'factors' such as dividend yield, market cap, P/E, P/B etc and I have the weights in the fund and in the benchmark. The analysis should be done in excel, but I'm not sure how to do it. I could do a 'weighted average' of each factor and then compare the outcome of portfolio to the outcome of the benchmark. But that seems too straight forward. Do you have any suggestions on how to do such style allocation analysis? Thanks

$\endgroup$
3
$\begingroup$

I'm not exactly sure what you're asking, but since you tagged the question as factor-models, I'll assume you are modeling the next month's returns of stocks as a function of the factors:

$$r_i = \alpha_i + \beta_1f_{i1} + \cdots + \beta_Kf_{iK} + \epsilon_i$$ where

  • $r_i$ is the next return of stock $i$
  • $\alpha_i$ is the part of stock $i$'s return unexplained by factors,
  • $f_{i1},..., f_{iK}$ are your current factor exposures (dividend yields, market caps, P/E, P/B),
  • $\beta_1,..., \beta_k$ are the factor premiums which we obtain through a multilinear regression analysis, and
  • $\epsilon_i$ is an error term.

From your question, it seems like you have a single point-in-time snapshot of all stocks in your universe. Since your question is a couple of months old, I can only assume that you also have the returns $r_i$ from the next month. You can now do a cross-sectional regression, where you model how much of next month's returns can be attributed to your $f_{i1},..., f_{iK}$ factor exposures.

Excel might not be the ideal tool for the job, but it can be done. This isn't the right forum for how to do such things in Excel, but this and this are both links to helpful sites on how to activate and use the Analysis ToolPak add-in for such tasks.

Once you have computed your premiums $\beta_1,..., \beta_k$, you can combine them with the weights of your portfolio. If $w_i$ are the portfolio weights, then $\sum_i w_i r_i$ is the return of your portfolio at the next month, and each $\sum_i w_i \beta_j f_{ji}$ explains how much of the portfolio's return can be attributed to that particular factor. Note that you will most likely have a big chunk leftover $\sum_i w_i \alpha_i$ which can't be explained by factors. Replace the word "portfolio" with "benchmark", and you can repeat the exercise with the whole benchmark using the same $\beta_1,..., \beta_k$ (because you used the whole universe to compute them).

I hope this helps!

$\endgroup$
  • $\begingroup$ this is NOT what the OP was seeking... $\endgroup$ – Jun Jang Sep 6 '18 at 14:10

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.