The instuction is attached. Actually the solution is attached too. But I want the calculation in excel. You can take the solution as reference and calculate it in excel. I need it in an hour or two.?
USC - MARSHALL SCHOOL OF BUSINESS
FBE 441 Investments
David Solomon ? Spring 2015
Homework Assignment #3
1. If CAPM is valid, which of the following situations are possible? Explain. Consider
each situation independently.
Hint: recall that the CAPM has implications for expected returns, correlations,
covariances and variances.
CAPM tells us that assets with higher betas should have higher expected returns. In this
example, portfolio B has a lower beta, but a higher expected return. Contradiction.
Portfolio Expected Standard
Since part of the total asset?s risk can be diversified away, higher standard deviation does
not necessarily imply higher expected returns. Therefore, there is no contradiction with
CAPM implies that the market portfolio has the highest Sharpe ratio
Expected Return - Riskfree rate
. In this example,
SR(Market)=(18-10)/24=0.33, while SR(A)=(16-10)/12=0.5, i.e. SR(Market)<SR(A).
Portfolio A has a lower standard deviation with higher expected returns than the Market.
This implies that the market portfolio is below the minimum variance frontier.
Market?s beta is less than that of A, but the market?s expected return is higher.
According to the CAPM, E(rA )=rf +? A (E(rm )-rf )=10 0.9(18 10) 17.2. Contradiction.
2. Our goal is to estimate the cost of capital of a small computer firm AST using the
CAPM and the Fama-French 3-Factor Model. The risk-free rate is 4% and the
excess expected return on the market is 8.6%.
(a) According to the CAPM the beta of AST is 1.65. What is the CAPM expected
return of AST?
ErAST 0.04 1.65 0.086 0.1819
(b) Estimating the factor loadings for the Fama-French model, we find AST = 1.58,
sAST=1.19 and hAST=-0.15. Given that the expected return on the SMB factor is
5.2%, and the expected return on the HML factor is 4.8%, what is the expected
return of AST according to the Fama-French 3-Factor Model?
E rAST rf ? AST Erm rf s AST ErSMB h AST ErHML
0.04 1.58 0.086 1.19 0.052 0.15 0.048 0.2306
(c) Interpret the factor loadings of AST. What does it mean to have AST,M = 1.58,
AST,SMB =1.19 and AST,HML =-0.15?
AST,M of 1.58 means that the sensitivity of AST with respect to market movement is 1.58
(when the market goes up 1% more than expected, AST goes up by 1.58% more than
expected). Since the average stock has a I,M of one (because the average stock is the
market), AST has more systematic market risk than the average stock. In contrast,
AST,SMB of 1.19 means that AST behaves like a small stock (SMB is ?small minus big?,
so when small stocks go up relative to large stocks by 1% more than expected, AST goes
1.19% more than expected). This is expected because AST is a small stock. Similarly,
AST,HML of -0.15 means that AST behave a little like a growth stock (because HML is
?value minus growth?). This is somewhat expected because AST is a computer stock ?
which tend to be growth stocks.
(d) Under the CAPM, the systematic volatility of a stock is given by ? i2 ? 2 , where
i is the stock?s CAPM beta, and m is the market volatility. For a general factor
model, a similar expression can be derived. In particular, for the Fama-French
model, the systematic volatility of a stock is given by:
s i2?SMB h i2?2
where i is the stock?s loading on the MKT factor, si is the loading on the SMB
factor, and hi is the stock?s loading on the HML factor, assuming all the factors
are uncorrelated. The volatilities m, SMB and HML represent the volatilities of
the MKT, SMB and HML factors respectively.
Using the Fama-French model, what is the systematic volatility (standard
deviation) of AST stock given the volatility of the market portfolio is 16%, the
volatility of the SMB factor is 15% and the volatility of HML is 13%? (Assume
the three factor portfolios are uncorrelated.)
The systematic risk of AST stock is:
? 2 s 2 ? SMB h 2 ? 2
1.58 2 0.16 2 1.19 2 0.15 2 0.15 2 0.132 0.3101
3. Consider a market where two factors are sufficient to describe the returns on
common stocks. For an asset i, the asset?s expected return is given by E(ri) = rf +
i1P1 i2 P2, where P1 and P2 are the factor premiums (expected return of the
factors in excess of the risk-free rate). Both factors are independent. The
following table gives the sensitivities of the stocks ABC and PQR to the two
factors, as well as the expected returns of each stock:
(a) Consider a portfolio, C, made up by selling short $.50 of security PQR and
purchasing $1.50 of ABC. How sensitive will this portfolio be to each of the two
This portfolio puts 150% weight in ABC and ?50% weight in PQR. Since the
sensitivities (betas) are additive, the beta of a portfolio is the weighted average of
component betas, sensitivity of this portfolio with respect to the first factor is 1.50(0.5)0.50(1.5) = 0. Similarly, sensitivity of this portfolio with respect to the second factor is
1.5(0.8)-0.5(1.4) = 0.5.
? C1 w ABC ? ABC ,1 w PRQ ? PRQ ,1
? C 2 w ABC ? ABC , 2 w PRQ ? PRQ , 2
(b) Consider a portfolio, D, made up by borrowing $1.00 at the risk free rate and
investing $1.00 in portfolio C. How sensitive will this portfolio be to each of the
Adding in the riskfree rate does not alter the sensitivities at all. This portfolio return rD =
rC - rf. Hence the portfolio sensitivities:
? D1 1 0 1 0 0
? D 2 1 0 1 0.5 0.5
However, notice that we have not created a zero-cost portfolio with no sensitivity to the
first factor and 0.5 units of sensitivity to the second factor. If we borrowed $2.00 at the
risk free rate and invested $2.00 in portfolio C, we would have created a FMP for the
(c) What combination of securities ABC, PQR and the riskless security will move on
a one-to-one basis with factor 1 and be insensitive to factor 2?
To find a portfolio with unit sensitivity to factor one and insensitive to factor two we
w ABC 0.5 w PRQ 1.5 1
w ABC 0.8 w PRQ 1.4 0
Solving these we find:
w ABC 2.8
w PRQ 1.6
The remainder of the portfolio position is held in the risk-free asset:
w ABC w PRQ w rf 1
w rf 1 1.6 2.8 2.2
We have created a factor mimicking portfolio for the first factor (and neutral exposure to
Data ? Constructing Your Own Version of Momentum
In this exercise, we are going to construct portfolios sorted on momentum and test their
alphas (i.e. abnormal returns) against a CAPM and 3 Factor Model.
Open the file ?momdatafinal.xls?. The file lists returns for 30 stocks. Each row
corresponds to the returns of one company in one month. ?datemonth? is the date, with
the first four digits being the year, and the last two being the month. ?PERMNO? is a
unique company identifier, ?Ticker? gives the company ticker and ?COMNAM? gives the
company name. ?RET? is the returns of the company in that month. ?RF? is the risk free
rate that month, ?mktrf? is the excess market return (Market ? RF) that month, and
?SMB?, ?HML? and ?UMD are factor portfolios sorted on size, book to market ratio, and
Step 1 ? Construct a momentum variable
The definition of momentum is the cumulative returns of the company from 12 months
ago to 2 months ago (inclusive). In other words, a company?s level of momentum in
January 1995 is equal to its cumulative return from January 1994 to November 1994.
Recall that cumulative returns are obtained by multiplying gross returns from each
month, and then subtracting 1.
e.g. Ret(January to March) = (1+ Ret(January))*(1+Ret(February))*(1+Ret(March)) - 1
In Excel, to multiply a sequence of cells together, you can use the PRODUCT function.
Note that since this variable requires 12 months of past data for each stock, you will only
be able to form the variable starting one year after the first entry for that stock.
Use this to first construct the momentum variable (cumulative returns from 2 months ago
to 12 months ago) for each company in each month.
Step 2 ? Sort on the Level of Momentum.
Since excel sometimes does funny things with cell references, you first want to select all
your data (in particular, the momentum variable), and copy it. Then click on a new sheet,
and right click ?Paste Special? and then ?Paste Values?. This will ensure that when we sort
our data, we don?t mess up any earlier cell references.
We now need to rank stocks according to their level of momentum each month. To do
this, select all the data in the new sheet, and then click on ?Sort?. You want to sort on two
levels ? first by datemonth (in ascending order) and then on the level of momentum (also
in ascending order).
Generate a new column that has a value of 1 to 30 according to the level of momentum
for stocks (1 will be the lowest momentum, and 30 the highest momentum).
Step 3 ? Generate Portfolios based on the level of momentum.
Having ranked stocks each month according to the level of momentum, form an equal
weighted portfolio of the 15 highest momentum stocks and the 15 lowest momentum
To form an equal weighted portfolio, simply take the average of each of the returns in the
group that month. In excel, you can take the average of a set of cells using the
So for the low momentum portfolio, take the average of the returns for the 15 lowest
ranked stocks that month, and for the high momentum portfolio, take the average of the
returns for the 15 highest ranked stocks. This will give you two equal weighted portfolio
returns for high momentum and low momentum stocks. You want to have this variable in
a new column ? each of the two portfolios will have only one return each month.
Step 4 - Create excess returns.
The data sheet still has one entry for each stock/month combination. We want to now
work only with the portfolio returns we have generated. Again, since we?re going to be
sorting, first select all the data and copy it, open up a new sheet, and select ?Paste
Special? -> ?Paste Values?.
The easiest way to select only the portfolio returns is to add a number in a new column
that signifies the high/low momentum portfolios. So next to each cell where you?ve put a
return from the high momentum portfolio, put a ?2?, and next to each low momentum
return, put a ?1? (call this variable ?MomLevel?. Now, select all the data and sort first on
?MomLevel?, then on ?Datemonth?. This should now give you the portfolio returns month
by month, first for the low momentum and then the high momentum.
In each case, subtract the risk free rate (?RF?) from the portfolio returns.
Solution: Please see the excel file for an example of how to implement the above steps in
At last, you have your portfolios sorted on high and low momentum.
1. For the high and low momentum portfolios, calculate the average returns, the standard
deviation of returns, and the Sharpe Ratio.
We have the following:
MEAN 0.0134291 0.003033
STDEV 0.053359 0.052195
2. In addition, calculate the average returns, standard deviation of returns and the Sharpe
Ratio for the excess market returns (MktRf), as well as the SMB and HML portfolios.
How do the high and low momentum portfolios compare with these factor portolios?
Which would you prefer?
For the MktRf, SMB and HML portfolios we have:
MEAN 0.0016333 0.003411 0.003076
STDEV 0.0402217 0.022844 0.018397
0.0406083 0.05174 0.046052
Comparing these with the two momentum portfolios, the High Momentum portfolio
looks quite attractive ? its Sharpe Ratio is around four times larger than the Market, SMB
or HML. High Momentum has very high returns here (1.3% per month), but also has high
volatility (5.3% per month). Low momentum stocks look unattractive, with volatility as
high as the high momentum stocks, but much lower returns.
3. Run a CAPM regression for the high momentum and low momentum portfolios. To do
this, select ?Data Analysis? and then select the ?Regression? option. The CAPM
regression is (e.g. for high momentum stocks):
Ret(High Mom) ? Rf = a + b*MktRf + e
As a result, the range of Y variable is the excess returns for the high momentum portfolio.
The range of the X variable is the returns over the same period for ?MktRf? i.e. the excess
Output the results to new worksheets. What are the alphas (intercepts) of the two
portfolios? Are they statistically different from zero?
We have the following:
High Momentum CAPM:
0.011727 0.003924 2.988763
1.041867 0.098157 10.61424
Low Momentum CAPM
0.001377 0.003871 0.355702
1.013757 0.096827 10.46974
In other words, the high momentum stocks have a statistically significant alpha of 1.17%
per month, with a t-stat of 2.99 , while low momentum stocks have a statistically
insignificant (t-stat of 0.35) alpha of 0.14% per month.
4. Run a Fama French 3 Factor Regression for the high and low momentum portfolios. The
procedure is similar to the CAPM regression, but the X variables now include ?MktRf?,
?SMB? and ?HML?, so select the range of all three variables for the ?X Variable? choice
in the regression dialog box.
What are the 3 Factor Alphas for the long and short portfolios? Are they statistically
different from zero?
For the 3 Factor Regressions, we have:
0.008973 0.003714 2.415746
0.880553 0.101115 8.708428
0.601323 0.177491 3.387899
0.908378 0.102292 8.880273
0.345561 0.179557 1.924521
0.50291 0.201386 2.497238
In other words, the high momentum portfolio has a statistically significant alpha (t-stat of
2.42) of 90 basis points per month, while the low momentum portfolio has a statistically
insignificant (t-stat of -0.31) alpha of -12 basis points per month.
5. Calculate a difference portfolio, which buys all high momentum stocks and shorts all low
momentum stocks. To do this, for each month take the high momentum portfolio and
subtract the low momentum portfolio. Run a CAPM regression and a Fama French 3
Factor Regression for the difference portfolio. Does this difference portfolio earn
abnormal returns under each model?
We have the following:
Difference Portfolio CAPM:
0.010351 0.005128 2.018557
0.028109 0.128274 0.219136
Difference 3 Factor
0.01015 0.005272 1.925287
The difference portfolio has a statistically significant (t-stat of 2.02) CAPM alpha of
1.04% per month. For the 3 factor model, the alpha is 1.02% per month, although the tstat is slightly outside the 5% significance level at 1.93.
6. Finally, for the long and short portfolios, run a 4 Factor regression, using the Fama
French 3 Factor variables (?MktRf?, ?SMB?, ?HML?) as well as a momentum factor
(?UMD?). What are the intercepts for each portfolio relative to this model?
Under the 4 Factor Model, we have:
0.831876 0.106219 7.831692
0.397223 0.176854 2.246053
7. Adding in a momentum factor in #6 should control for the effect of momentum on
returns. Given the results in #6, do you think these stocks look representative of the
momentum effect in general? To test this, evaluate the mean, standard deviation and
Sharpe Ratio of the UMD portfolio. How do you think I might have chosen the list of 30
For the high momentum stocks, adding in the momentum factor leaves the alpha about
half as large (55 basis points instead of 90 basis points), although the significance is
lower (t-stat of 1.49). But the alpha is still a reasonably large number, at least in terms of
This raises the possibility that these stocks might have higher returns than just the
momentum effect overall. To confirm this, the return properties of the UMD portfolio
This shows that the high momentum portfolio formed from the 30 stocks has
considerably higher returns and a higher Sharpe Ratio than the overall momentum factor.
As it turns out, I ran through a lot of combinations of 30 stocks until I found one where
there was a significant momentum effect over a 5 year horizon. The momentum effect
has gotten weaker in recent periods, and 5 years is not very much data to hope to find a
This question was answered on: Oct 07, 2020Buy this answer for only: $15
This attachment is locked
Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
About this QuestionSTATUS
Oct 07, 2020EXPERT
GET INSTANT HELP/h4>
We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.
You can also use these solutions:
- As a reference for in-depth understanding of the subject.
- As a source of ideas / reasoning for your own research (if properly referenced)
- For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
NEW ASSIGNMENT HELP?
Order New Solution. Quick Turnaround
Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.
WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.