Get documents about "Risk and Return Calculation Using Excel
W
Description
Risk and Return Calculation Using Excel document sample
Document Sample
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
Ch 03 Tool Kit 1/23/2005
Chapter 3. Tool Kit for Risk and Return: Part II
PROBABILITY DISTRIBUTIONS
The probability distribution is a listing of all possible outcomes and the corresponding probability.
Probability of
Occurrence Rate of Return Distribution
E F G H
0.10 10% 6% 14% 4%
0.20 10% 8% 12% 6%
0.40 10% 10% 10% 8%
0.20 10% 12% 8% 15%
0.10 10% 14% 6% 22%
1.00
EXPECTED RATE OF RETURN AND STANDARD DEVIATION
The expected rate of return is the rate of return that is expected to be realized from an investment. It is
determined as the weighted average of the probability distribution of returns.
To calculate the standard deviation, there are a few steps. First find the differences of all the possible returns
from the expected return. Second, square that difference. Third, multiply the squared number by the
probability of its occurrence. Fourth, find the sum of all the weighted squares. And lastly, take the square
root of that number.
Calculation of expected return and standard deviation for E
Expected rate of return for E Standard deviation for E
Probability of Deviation from Squared
Occurrence Rate of Return Product r hat deviation Sq Dev * Prob.
10% 10% 1.00% 0% 0.00% 0.00%
20% 10% 2.00% 0% 0.00% 0.00%
40% 10% 4.00% 0% 0.00% 0.00%
20% 10% 2.00% 0% 0.00% 0.00%
10% 10% 1.00% 0% 0.00% 0.00%
100% Sum: 0.00%
Expected Std. Dev. =
Rate of Return, r Square root of
hat = 10% sum = 0.00%
If the probabilities are fairly simple, then a short-cut method is to use the excel functions for AVERAGE and
STDEVP, but to "trick" them by entering arguments more than once, in a way that "weights" them like the
probabilities. For example, for stock E we would enter 6% once, since it has only a one in ten probability. We
would enter 8% twice, since it has a two in ten probability. We would enter 10% 4 times, since it has a four in
ten probability. We can do the same thing with the standard deviation function. Note that we use STDEVP
and not STDEV, since we are measuring the standard deviation for the entire population and not for a sample.
We call this the "indirect" method.
Indirect method r hat = 10% s= 0.00%
Calculation of expected return and standard deviation for F
Expected rate of return for F Standard deviation for F
Michael C. Ehrhardt Page 1 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
Probability of Deviation from Squared
Occurrence Rate of Return Product r hat deviation Sq Dev * Prob.
10% 6% 0.60% -4% 0.16% 0.02%
20% 8% 1.60% -2% 0.04% 0.01%
40% 10% 4.00% 0% 0.00% 0.00%
20% 12% 2.40% 2% 0.04% 0.01%
10% 14% 1.40% 4% 0.16% 0.02%
100% Sum: 0.05%
Expected Std. Dev. =
Rate of Return, r Square root of
hat = 10% sum = 2.19%
Indirect method r hat = 10% s= 2.19%
Calculation of expected return and standard deviation for G
Expected rate of return for G Standard deviation for G
Probability of Deviation from Squared
Occurrence Rate of Return Product r hat deviation Sq Dev * Prob.
10% 14% 1.40% 4% 0.16% 0.02%
20% 12% 2.40% 2% 0.04% 0.01%
40% 10% 4.00% 0% 0.00% 0.00%
20% 8% 1.60% -2% 0.04% 0.01%
10% 6% 0.60% -4% 0.16% 0.02%
100% Sum: 0.05%
Expected Std. Dev. =
Rate of Return, r Square root of
hat = 10% sum = 2.19%
Indirect method r hat = 10.00% s= 2.19%
Calculation of expected return and standard deviation for H
Expected rate of return for H Standard deviation for H
Probability of Deviation from Squared
Occurrence Rate of Return Product r hat deviation Sq Dev * Prob.
10% 4% 0.40% -6% 0.36% 0.04%
20% 6% 1.20% -4% 0.16% 0.03%
40% 8% 3.20% -2% 0.04% 0.02%
20% 15% 3.00% 5% 0.25% 0.05%
10% 22% 2.20% 12% 1.44% 0.14%
100% Sum: 0.28%
Expected Std. Dev. =
Rate of Return, r Square root of
hat = 10.00% sum = 5.27%
Indirect method r hat = 10.00% s= 5.27%
Michael C. Ehrhardt Page 2 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
COVARIANCE
The covariance is a measure that combines the variance of a stock's return with the tendency of those returns
to move up or down at the same time another stock moves up or down.
To calculate the covariance, there are a few steps. First find the differences of all the
possible returns from the expected return; do this for both stocks. Second, multiply the
differences of both stocks. Third, multiplythe previous product by the probability of its
occurrence. Fourth, find the some of all the weighted products. The result is the covariance.
Calculation of covariance between F and G
Probability of Deviation of F Deviation of G Product of Product *
Occurrence from r hat from r hat deviations Prob.
10% -4% 4% -0.1600% -0.02%
20% -2% 2% -0.0400% -0.01%
40% 0% 0% 0.0000% 0.00%
20% 2% -2% -0.0400% -0.01%
10% 4% -4% -0.1600% -0.02%
100%
Covariance =
sum = -0.048%
Calculation of covariance between F and H
Probability of Deviation of F Deviation of H Product of Product *
Occurrence from r hat from r hat deviations Prob.
10% -4% -6% 0.2400% 0.02%
20% -2% -4% 0.0800% 0.02%
40% 0% -2% 0.0000% 0.00%
20% 2% 5% 0.1000% 0.02%
10% 4% 12% 0.4800% 0.05%
100%
Covariance =
sum = 0.108%
Calculation of covariance between F and E
Probability of Deviation of F Deviation of E Product of Product *
Occurrence from r hat from r hat deviations Prob.
10% -4% 0% 0.0000% 0.00%
20% -2% 0% 0.0000% 0.00%
40% 0% 0% 0.0000% 0.00%
20% 2% 0% 0.0000% 0.00%
10% 4% 0% 0.0000% 0.00%
100%
Covariance =
sum = 0.000%
Michael C. Ehrhardt Page 3 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
CORRELATION COEFFICIENT
Like covariance, the correlation coefficient also measures the tendency of two stocks to move together, but it is
standardized and it is always in the range of -1 to +1. The correlation coefficient is equal to the covariance
divided by the product of the standard deviations.
Calculation of the correlation between F and G
rFG = Covariance FG ÷ SigmaF * SigmaG
= -0.048% ÷ 2.19% 2.19%
= -0.048% ÷ 0.048%
rFG = -1.0
Calculation of the correlation between F and H
rFH = Covariance FH ÷ SigmaF * SigmaH
= 0.108% ÷ 2.19% 5.27%
= 0.108% ÷ 0.116%
rFH = 0.935
PORTFOLIO RISK AND RETURN: THE TWO-ASSET CASE
Suppose there are two assets, A and B. w A is the percent of the portfolio invested in asset A.
Since the total percents invested in the asset must add up to 1, (1-w A) is the percent of the
portfolio invested in asset B.
The expected return on the portfolio is the weighted average of the expected returns on
asset A and asset B.
^ ^ ^
r p w A r A (1 w A ) r B
The standard deviation of the portfolio, sp, is not a weighted average. It is:
s p WAs A (1 WA )2 s B 2WA (1 WA ) r AB s A s B
2 2 2
ATTAINABLE PORTFOLIOS: THE TWO ASSET-CASE
Asset A Asset B
Expected return, r hat 5% 8%
Standard deviation, s 4% 10%
Using the equations above, we can find the expected return and standard deviation of a
portfolio with different percents invested in each asset.
Michael C. Ehrhardt Page 4 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
Correlation = 1
Proportion of
Proportion of Portfolio in
Portfolio in Security A Security B
(Value of wA) (Value of 1-w B) rp sp
1.00 0.00 5.00% 4.0%
0.90 0.10 5.30% 4.6%
0.80 0.20 5.60% 5.2%
0.70 0.30 5.90% 5.8%
0.60 0.40 6.20% 6.4%
0.50 0.50 6.50% 7.0%
0.40 0.60 6.80% 7.6%
0.30 0.70 7.10% 8.2%
0.20 0.80 7.40% 8.8%
0.10 0.90 7.70% 9.4%
0.00 1.00 8.00% 10.0%
rAB = +1: Attainable Set of Risk/Return
Combinations
10%
Expected return
5%
0%
0% 5% 10% 15%
Risk, sp
Correlation = 0
Proportion of
Proportion of Portfolio in
Portfolio in Security A Security B
(Value of wA) (Value of 1-w A) rp sp
1.00 0.00 5.00% 4.0%
0.90 0.10 5.30% 3.7%
0.80 0.20 5.60% 3.8%
0.70 0.30 5.90% 4.1%
0.60 0.40 6.20% 4.7%
0.50 0.50 6.50% 5.4%
0.40 0.60 6.80% 6.2%
0.30 0.70 7.10% 7.1%
0.20 0.80 7.40% 8.0%
0.10 0.90 7.70% 9.0%
0.00 1.00 8.00% 10.0%
Michael C. Ehrhardt Page 5 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
rAB = 0: Attainable Set of Risk/Return
Combinations
10%
Expected return 5%
0%
0% 5% 10% 15%
Risk, sp
Correlation = -1
Proportion of
Proportion of Portfolio in
Portfolio in Security A Security B
(Value of wA) (Value of 1-w A) rp sp
1.00 0.00 5.00% 4.0%
0.90 0.10 5.30% 2.6%
0.80 0.20 5.60% 1.2%
0.70 0.30 5.90% 0.2%
0.60 0.40 6.20% 1.6%
0.50 0.50 6.50% 3.0%
0.40 0.60 6.80% 4.4%
0.30 0.70 7.10% 5.8%
0.20 0.80 7.40% 7.2%
0.10 0.90 7.70% 8.6%
0.00 1.00 8.00% 10.0%
rAB = -1: Attainable Set of Risk/Return
Combinations
10%
Expected return
5%
0%
0% 5% 10% 15%
Risk, sp
Table 2 Expected Return and Standard Deviation under Various Assumptions
Proportion of
Proportion of Portfolio in
Portfolio in Security A Security B
(Value of wA) (Value of 1-w A) rp sp
Michael C. Ehrhardt Page 6 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
Case I rAB = Case II rAB = Case III rAB =
+1.0 0.0 -1.0
1.00 0.00 5.00% 4.0% 4.0% 4.0%
0.75 0.25 5.75% 5.5% 3.9% 0.5%
0.50 0.50 6.50% 7.0% 5.4% 3.0%
0.25 0.75 7.25% 8.5% 7.6% 6.5%
0.00 1.00 8.00% 10.0% 10.0% 10.0%
Michael C. Ehrhardt Page 7 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
ATTAINABLE AND EFFICIENT PORTFOLIOS: MANY ASSETS
OPTIMAL PORTFOLIOS
Michael C. Ehrhardt Page 8 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
EFFICIENT SET WITH A RISK-FREE ASSET
OPTIMAL PORTFOLIO WITH A RISK-FREE ASSET
Michael C. Ehrhardt Page 9 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
CALCULATING BETAS
We downloaded stock prices and dividends from http://finance.yahoo.com for General
Electric using its ticker symbol, GE. We also downloaded data for the S&P 500 (^SPX)
which contains most actively traded stocks, and the Fidelity Magellan mutual fund
(FMAGX). We computed returns, as shown in Chapter 2. We also obtained the monthly
rates on 3-month Treasury bills from the FRED II data base at the St. Louis Federal
Reserve, http://research.stlouisfed.org.
rRF, Risk-Free
rM, Market rp, Fidelity Rate (Monthly Excess stock
Return (S&P Magellan Fund Return on 3- Excess market return
Date 500 Index) rj, GE Return Return Month T-Bill) return (rM-rRF) (rj-rRF)
September 2004 1.8% 3.3% 2.1% 0.14% 1.6% 3.2%
August 2004 0.2% -1.4% 0.2% 0.12% 0.1% -1.5%
July 2004 -3.4% 2.6% -3.9% 0.11% -3.5% 2.5%
June 2004 1.8% 4.8% 1.5% 0.11% 1.7% 4.6%
May 2004 1.2% 3.9% 1.0% 0.09% 1.1% 3.8%
April 2004 -1.7% -1.9% -1.7% 0.08% -1.8% -2.0%
March 2004 -1.6% -6.2% -1.3% 0.08% -1.7% -6.2%
February 2004 1.2% -2.7% 1.4% 0.08% 1.1% -2.8%
January 2004 1.7% 8.6% 1.3% 0.07% 1.7% 8.5%
December 2003 5.1% 8.8% 5.2% 0.08% 5.0% 8.7%
November 2003 0.7% -1.2% 0.4% 0.08% 0.6% -1.2%
October 2003 5.5% -2.7% 4.9% 0.08% 5.4% -2.8%
September 2003 -1.2% 1.4% -1.5% 0.08% -1.3% 1.3%
August 2003 1.8% 4.0% 1.7% 0.08% 1.7% 3.9%
July 2003 1.6% -0.9% 1.3% 0.08% 1.5% -0.9%
June 2003 1.1% 0.6% 1.1% 0.08% 1.1% 0.5%
May 2003 5.1% -2.5% 4.4% 0.09% 5.0% -2.6%
April 2003 8.1% 15.5% 8.2% 0.09% 8.0% 15.4%
March 2003 0.8% 6.0% 1.1% 0.09% 0.7% 5.9%
February 2003 -1.7% 4.8% -1.2% 0.10% -1.8% 4.7%
January 2003 -2.7% -5.0% -2.8% 0.10% -2.8% -5.1%
December 2002 -6.0% -9.5% -6.6% 0.10% -6.1% -9.6%
November 2002 5.7% 7.4% 5.2% 0.10% 5.6% 7.3%
October 2002 8.6% 2.5% 9.4% 0.13% 8.5% 2.3%
September 2002 -11.0% -17.7% -10.8% 0.14% -11.1% -17.8%
August 2002 0.5% -6.4% 1.0% 0.14% 0.4% -6.5%
July 2002 -7.9% 10.8% -7.2% 0.14% -8.0% 10.7%
June 2002 -7.2% -6.1% -7.7% 0.14% -7.4% -6.3%
May 2002 -0.9% -1.3% -0.2% 0.14% -1.1% -1.4%
April 2002 -6.1% -15.6% -6.4% 0.14% -6.3% -15.8%
March 2002 3.7% -2.9% 3.4% 0.15% 3.5% -3.0%
February 2002 -2.1% 4.1% -1.7% 0.14% -2.2% 4.0%
January 2002 -1.6% -7.3% -3.2% 0.14% -1.7% -7.5%
December 2001 0.8% 4.6% 1.0% 0.14% 0.6% 4.4%
November 2001 7.5% 5.7% 7.5% 0.16% 7.4% 5.6%
October 2001 1.8% -2.1% 2.4% 0.18% 1.6% -2.3%
September 2001 -8.2% -8.7% -8.1% 0.22% -8.4% -8.9%
August 2001 -6.4% -6.0% -6.5% 0.28% -6.7% -6.2%
July 2001 -1.1% -10.9% -1.5% 0.29% -1.4% -11.2%
June 2001 -2.5% 0.0% -2.5% 0.29% -2.8% -0.3%
May 2001 0.5% 1.0% 1.1% 0.30% 0.2% 0.7%
April 2001 7.7% 15.9% 8.8% 0.32% 7.4% 15.6%
March 2001 -6.4% -9.7% -6.4% 0.37% -6.8% -10.0%
February 2001 -9.2% 1.1% -9.4% 0.41% -9.6% 0.7%
January 2001 3.5% -4.1% 3.2% 0.43% 3.0% -4.5%
December 2000 0.4% -3.0% 0.9% 0.48% -0.1% -3.4%
Michael C. Ehrhardt Page 10 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
November 2000 -8.0% -9.6% -8.5% 0.51% -8.5% -10.1%
October 2000 -0.5% -5.2% -1.8% 0.51% -1.0% -5.7%
Average return
(annual) -4.8% -8.2% -5.2% 2.1% -6.9% -10.3%
Standard deviation
(annual) 16.5% 24.5% 16.8% 0.4% 16.6% 24.6%
Correlation with market return, r 0.593 0.99 -0.26 1.00 0.60
R-square 0.35 0.99 0.07 1.00 0.35
Slope 0.88 1.01 -0.01 1.01 0.89
Using the AVERAGE function and the STDEV function, we found the average historical
returns and standard deviations. (We converted these from monthly figures to annual
figures. Notice that you must multiply the monthly standard deviation by the square root
of 12, and not 12, to convert it to an annual basis.) These are shown in the rows above.
We also use the CORREL function to find the correlation of the market with the other assets
Using the function Wizard for SLOPE, we found the slope of the regression line, which is
the beta coefficient. We also use the function Wizard and the RSQ function to find the
R-Squared of the regression.
Using the Chart Wizard, we plotted the GE returns on the y-axis and the market returns on
the x-axis. We also used the menu Chart > Options to add a trend line, and to display the
regression equation and R2 on the chart. The chart is shown below. We also used the
regression feature to get more detailed data. These results are also shown below.
GE Analysis
The beta coefficient is about 0.88, as shown by the slope coefficient in the regression
equation on the chart. The beta coefficient has a t statistic of 5.00, and there is virtually a
zero chance of getting this if the true beta coefficient is equal to zero. Therefore, this is a
statistically significant coefficient. However, the confidence interval ranges from 0.53 to
1.24, which is very wide. The R2 of about 0.35 indicates that 35% of the variance of the
stock return can be explained by the market. The rest of the stock's variance is due to
factors other than the market. This is consistent with the wide scatter of points in the
graph.
GE Regression Results (See columns J-N)
Historic
rj = - 0.0034 + 0.8826 rM Realized Beta
Returns Coefficient 0.8826
R2 = 0.3522 on GE, rj(%)
t statistic 5.00
20%
Probability of t stat. 0.001%
Lower 95% confidence interval 0.53
Upper 95% confidence interval 1.24
10%
Intercept
Coefficient -0.00335
t statistic -0.40
0% Probability of t stat. 69.0%
-30% -20% -10% 0% 10% 20% 30% Lower 95% confidence interval -0.02
Upper 95% confidence interval 0.01
-10%
Historic Realized Returns
on the Market,
rM(%)
Michael C. Ehrhardt Page 11 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
-20%
Magellan Analysis
The beta coefficient is about 1.01, as shown by the slope coefficient in the regression
equation on the chart. The beta coefficient has a t statistic of 64.94, and there is virtually a
zero chance of getting this if the true beta coefficient is equal to zero. Therefore, this is a
statistically significant coefficient. The confidence interval ranges from 0.98 to 1.04, which
is very small compared to the confidence interval for a single stock. The R2 of about 0.99
indicates that 99% of the variance of the portfolio return can be explained by the market.
This is consistent with the very narrow scatter of points in the graph. The estimate of the
intercept is equal to 0.00, and has a t statistic of -0.45 with a probability of 65.8%. Since
this is greater than 5%, we would say that the coefficient is not statistically significant-- in
other words, the true intercept might well be equal to zero.
Magellan Regression Results (See columns J-N)
Historic
rp = - 0.00033 + 1.0114 rM Realized Beta
R2 = 0.9892 Returns Coefficient 1.0114
on Magellan,
t statistic 64.94
rP(%)
20% Probability of t stat. 0.0%
Lower 95% confidence interval 0.98
Upper 95% confidence interval 1.04
10%
Intercept
Coefficient -0.00033
t statistic -0.45
Probability of t stat. 65.8%
Lower 95% confidence interval 0.00
0%
Upper 95% confidence interval 0.00
-30% -20% -10% 0% 10% 20% 30%
Historic Realized Returns
on the Market, rM(%)
-10%
-20%
Michael C. Ehrhardt Page 12 7/27/2011
304208c0-07a0-4f3f-868c-ec78326cfd63.xls Model
The Market Model vs. CAPM
We have been regressing the stock (or portfolio) returns against the market returns.
However, CAPM actually states that we should regress the excess stock returns (the stock
return minus the short-term risk free rate) against the excess market returns (the market
return minus the short-term risk free rate). We show the graph for such a regression
below. Notice that it is virtually identical to the market model regression we used earlier
for GE. Since it usually doesn't change the results whether we use the market model to
estimate beta instead of the CAPM model, we usually use the market model.
(See columns J-N)
CAPM (excess return) Model Regression Results
y = 0.8871x - 0.0035 Beta
Excess Returns
R2 = 0.3576 on GE, rS-rRF Coefficient 0.8871
t statistic 5.06
20%
Probability of t stat. 0.0%
Lower 95% confidence interval 0.53
Upper 95% confidence interval 1.24
10% Intercept
Coefficient -0.00353
t statistic -0.42
Probability of t stat. 67.5%
0% Lower 95% confidence interval -0.02
-30% -20% -10% 0% 10% 20% 30% Upper 95% confidence interval 0.01
-10% Excess Returns
on the Market, rM-rRF
-20%
Table 3-4 Regression Results for Calculating Beta
Regression Probability of Lower 95% Upper 95%
Coefficient t Statistic t Statistic Confidence Confidence
Panel a: General Electric
(Market model)
Michael C. Ehrhardt Page 13 7/27/2011
