Unformatted text preview: Beta estimation using Excel (1)
• Use “LINEST” function and “Ctrl+Shift+Enter” to
Use output all relevant statistics
output
• For more detailed uses, you can google to search
• The output is:
• Beta 1.499104 0.00198 Alpha se (beta) 0.397254 0.003909 se (alpha) R^2 0.470909 Fstat 14.24056 16 d.f. SSreg 0.003551 0.003989 SSresidual 0.01579 se 549 Beta estimation using Excel (2)
• Use “Regression analysis” in “Data analysis” (You
Use
•
•
• • will need to include “Analysis Toolpak” in “Addins”)
will
Letting riskfree rate fixed for simplicity
Coefficient of “X Variable 1” is the estimated
Coefficient
market beta for the individual stock
market
Dell’s market beta = 1.50 (of tstat = 3.77 and
confidence interval [0.657, 2.341]), what does that
mean?
Dell’s alpha = 0.002 (of tstat = 0.51 and
confidence interval [0.010, 0.006]), what does that
mean?
550 Beta estimation using Excel (3)
• Dell’s market beta = 1.50 (of tstat = 3.77 and confidence interval [0.657, 2.341])
 It means that Dell is risky in the sense that, when
the market goes up by 10%, Dell overreacts and
goes up by 15%. Thus, Dell carries higher market
risk (because it is very sensitive to market moves)
• Dell’s alpha = 0.002 (of confidence interval [0.010, 0.006])
 It means that Dell does not provide abnormal
returns, consistent with efficient market hypothesis
551 Exercise 5
• Now, try to estimate the alpha and beta of HP’s daily data (in “HP” tab) and see if you can get
this outcome
Coefficients St. Err.
Alpha
Market
beta t Stat 0.00247 0.002102 1.17267
1.16852 0.213651 5.469295 552 Hedge risk using factor models
Let’s use market risk (market premium) as the
Let’s
only risk source for simplicity
for
The question is “How can we hedge market risk?”
We will see an example that hedge market risk
We
(i.e., eliminate the market risk)
(i.e.,
For Excel, see “HedgeMarketRisk” in
For
“Lecture2_StockPricing”
“Lecture2_StockPricing”
As you can see, by sho...
View
Full
Document
This document was uploaded on 03/03/2014.
 Spring '09

Click to edit the document details