MBA Finance

Ch06ToolKit1.xlsx

Home >Business & Finance homework help >Financial markets homework help >MBA Finance

Chapter

Tool Kit		Chapter 6			11/20/18
		Risk and Return
6-1 Investment Returns and Risk
Amount invested			$1,000
Amount received in one year			$1,100
Dollar return (Profit)			$100
Rate of return = Profit/Investment =			10%
6-2 Measuring Risk for Discrete Distributions
The relationship between risk and return is a fundamental axiom in finance. Generally speaking, it is totally logical to assume that investors are only willing to assume additional risk if they are adequately compensated with additional return. This idea is rather fundamental, but the difficulty in finance arises from interpreting the exact nature of this relationship (accepting that risk aversion differs from investor to investor). Risk and return interact to determine security prices, hence it is of paramount importance in finance.
A listing of possible outcomes and their probabilities is called a probability distribution, as shown below.
Scenario	Probability of Scenario	Rate of Return in Scenario
Best Case	0.30	37%
Most Likely	0.40	11%
Worst Case	0.30	−15%
	1.00
Figure 6-1
Discrete Probability Distribution for Three Scenarios
Given the probabilities and the outcomes for possible returns, it is possible to calculate the expected return and standard deviation.
Figure 6-2
Calculating Expected Returns and Standard Deviations: Discrete Probabilities
INPUTS:			Expected Return	Standard Deviation
Scenario	Probability of Scenario (1)	Market Rate of Return (2)	Product of Probability and Return (3) = (1) × (2)	Deviation from Expected Return (4) = (2) − D66	Squared Deviation (5) = (4)2	Prob. × Sq. Dev. (6) = (1) × (5)
Best Case	0.30	37%	11.1%	0.2600	0.0676	0.0203		Excel functions for finding expected return and standard deviation of discrete events
Most Likely	0.40	11%	4.4%	0.0000	0.0000	0.0000		Use SUMPRODUCT to find expected return by putting probabilities in first argument array and rates of return in the second argument array.					11%
Worst Case	0.30	−15%	−4.5%	-0.2600	0.0676	0.0203								=SUMPRODUCT(B63:B65,C63:C65)
	1.00	Exp. ret. =	Sum = 11.0%		Sum = Variance =	0.0406		Use SUMPRODUCT to find variance. If the first array has probabilities and the second array subtracts the mean from the array of outcomes and is squared, then this is exactly the calculation shown in the step-by-step manner above to find variance:					0.0406
				Std. Dev. = Square root of variance =		20.1%								=SUMPRODUCT(B63:B65,(C63:C65-D66)^2)
Note: Calculations are not rounded in intermediate steps.
6-3 Risk in a Continuous Distribution
It is possible to add more scenarios.
Scenario	Panel A: Probability of Market Return Scenario	Panel B: Probability of Stock Return Scenario	Rate of Return in Scenario
1	0.0002	0.0198	-66%
2	0.0011	0.0307	-55%
3	0.0054	0.0452	-44%
4	0.0205	0.0625	-33%
5	0.0575	0.0806	-22%
6	0.1201	0.0969	-11%
7	0.1870	0.1082	0%
8	0.2167	0.1123	11%
9	0.1870	0.1082	22%
10	0.1201	0.0969	33%
11	0.0575	0.0806	44%
12	0.0205	0.0625	55%
13	0.0054	0.0452	66%
14	0.0011	0.0307	77%
15	0.0002	0.0198	88%
	1.0000	1.0000
Average =	11.0%	11.0%
Std. dev. =	20.2%	36.2%
Figure 6-3
Discrete Probability Distributions for 15 Scenarios
Panel A: Market Return for 15 Scenarios: Standard Devation = 20.2%
Panel B: Single Company's Stock Return for 15 Scenarios: Standard Devation = 36.2%
At some point, it becomes impractical to keep adding scenarios. Many analysts use the normal distribution to estimate stock returns.
Here is an example of a normal distribution with a similar mean and standard deviation as the discrete distribution shown above.
6-4 Using Historical Data to Estimate Risk
Investors often use historical data to estimate risk. This is quite easy in Excel by using the AVERAGE and STDEV functions.
Standard Deviation Based On a Sample of Historical Data
Inputs:				Realized
			Year	return
			2017	15.0%
			2018	−5.0%
			2019	20.0%
Calculations:
			=AVERAGE(E183:E185)	10.0%
			=STDEV(E183:E185)	13.2%			Use STDEV, the function for a sample.
Measuring the Standard Deviation of MicroDrive
The monthly stock returns for MicroDrive and one of its competitors, SnailDrive, during the past 48 months are shown in the figure below. The actual data are below the figure.
Figure 6-5
Historical Monthly Stock Returns for MicroDrive and SnailDrive
			MicroDrive	SnailDrive
Average Return (annualized)			12.0%	9.3%
Standard Deviation (annualized)			51.8%	34.2%
						Weights are specified in Section 6-5.
					Portfolio weights
					SnailDrive:	75%
					MicroDrive:	25%
		Period	Market	MicroDrive	SnailDrive	Portfolio
		1	2.37%	2.81%	14.93%	11.90%
		2	12.68%	14.79%	−3.26%	1.25%
		3	−1.13%	0.79%	−10.57%	−7.73%
		4	10.93%	8.71%	−10.76%	−5.89%
		5	−0.02%	0.83%	9.71%	7.49%
		6	−3.31%	−32.42%	6.40%	−3.30%
		7	11.89%	22.56%	0.26%	5.83%
		8	−3.96%	−24.21%	0.52%	−5.66%
		9	−4.90%	8.00%	−8.67%	−4.50%
		10	7.10%	−1.29%	21.62%	15.89%
		11	2.94%	4.43%	3.87%	4.01%
		12	−6.52%	−6.36%	5.00%	2.16%
		13	3.72%	11.79%	−12.32%	−6.29%
		14	4.74%	21.32%	−2.43%	3.51%
		15	−8.21%	−10.28%	4.87%	1.08%
		16	−5.15%	3.96%	−17.85%	−12.40%
		17	3.92%	34.98%	−10.89%	0.57%
		18	1.08%	2.56%	−16.68%	−11.87%
		19	−2.48%	−10.80%	9.02%	4.07%
		20	3.92%	−6.70%	22.60%	15.28%
		21	3.13%	−2.31%	14.25%	10.11%
		22	0.17%	8.26%	−7.68%	−3.69%
		23	5.17%	0.51%	−2.52%	−1.76%
		24	2.56%	−14.61%	9.32%	3.34%
		25	−5.41%	−4.56%	−1.38%	−2.17%
		26	−2.09%	−12.08%	13.92%	7.42%
		27	1.08%	31.68%	3.91%	10.85%
		28	10.47%	4.43%	8.91%	7.79%
		29	−3.74%	0.32%	−3.76%	−2.74%
		30	2.94%	4.59%	−3.95%	−1.82%
		31	−9.50%	2.08%	−1.43%	−0.55%
		32	−3.10%	−15.49%	−6.38%	−8.65%
		33	7.95%	32.39%	12.00%	17.10%
		34	10.93%	15.15%	−2.00%	2.28%
		35	−1.70%	−3.72%	−12.51%	−10.31%
		36	−3.96%	−15.40%	−0.49%	−4.22%
		37	5.17%	−12.67%	9.91%	4.27%
		38	−0.75%	−10.43%	−8.21%	−8.76%
		39	−9.04%	−7.14%	−11.27%	−10.24%
		40	−9.50%	−4.85%	−10.32%	−8.96%
		41	4.74%	8.15%	9.19%	8.93%
		42	−0.38%	−14.72%	−0.43%	−4.00%
		43	4.32%	32.45%	0.99%	8.85%
		44	−1.89%	−28.34%	3.96%	−4.12%
		45	−3.96%	−5.55%	−8.50%	−7.77%
		46	6.58%	5.81%	16.10%	13.52%
		47	−1.32%	4.02%	8.86%	7.65%
		48	4.74%	4.38%	1.30%	2.07%
Full 48 Months			Market	MicroDrive	SnailDrive	Portfolio
		Average monthly return:	0.9%	1.00%	0.77%	0.8%
		Standard deviation of monthly returns:	5.7%	14.94%	9.87%	7.8%
		Average return (annual):	10.8%	12.0%	9.3%	10.0%
		Standard deviation (annual):	19.9%	51.8%	34.2%	27.1%
		Maximum of monthly returns:	12.7%	34.98%	22.60%	17.1%
		Minimum of monthly returns:	-9.5%	-32.42%	-17.85%	-12.4%
Past 12 Months		Month	Market	MicroDrive	SnailDrive	Portfolio
		37	5.2%	-12.7%	9.9%	4.3%
		38	-0.8%	-10.4%	-8.2%	-8.8%
		39	-9.0%	-7.1%	-11.3%	-10.2%
		40	-9.5%	-4.9%	-10.3%	-9.0%
		41	4.7%	8.1%	9.2%	8.9%
		42	-0.4%	-14.7%	-0.4%	-4.0%
		43	4.3%	32.4%	1.0%	8.9%
		44	-1.9%	-28.3%	4.0%	-4.1%
		45	-4.0%	-5.6%	-8.5%	-7.8%
		46	6.6%	5.8%	16.1%	13.5%
		47	-1.3%	4.0%	8.9%	7.7%
		48	4.7%	4.4%	1.3%	2.1%
Past 12 Months			Market	MicroDrive	SnailDrive	Portfolio
		Average monthly return:	-0.11%	-2.41%	0.97%	0.12%
		Average return (annual):	-1.3%	-28.9%	11.6%	1.5%
		Standard deviation (annual):	18.9%	52.4%	31.4%	29.1%
		Total compound return:	-2.9%	-34.3%	7.3%	-2.4%	The total compound return is the total return on $1 invested at the end of month 36. The Excel function =FVSCHEDULE calculates the ending value given an initial amount and a series of returns.
6-5 Risk in a Portfolio Context
Now we are going to analyze the risk of a portfolio instead of the stand-alone risk of individual assets.
Creating a Portfolio
Look at the data for MicroDrive and SnailDrive shown above. The last column shows a portfolio with the weights shown below. Here are the results for the two companies and for the portfolio. Notice that the portfolio has a higher return than SnailDrive and less risk than either of the two stocks.
Portfolio weights
SnailDrive:	75%
MicroDrive:	25%
Full 48 Months			Market	MicroDrive	SnailDrive	Portfolio
		Average monthly return:	0.9%	1.0%	0.8%	0.8%
		Standard deviation of monthly returns:	5.7%	14.9%	9.9%	7.8%
		Average return (annual):	10.8%	12.0%	9.3%	10.0%
		Standard deviation (annual):	19.9%	51.8%	34.2%	27.1%
Correlation
Loosely speaking, correlation measures the tendency of two variables to move together.
Correlation between MicroDrive and SnailDrive:
r =	-0.133	=CORREL(E232:E279,F232:F279)
6-6 The Relevant Risk of a Stock: The Capital Asset Pricing Model (CAPM)
The Capital Asset Pricing Model (CAPM) provides a measure of risk.
Contribution to Market Risk: Beta
The relevant risk of an individual stock as defined by its beta. Beta measures how much risk a stock contributes to a well-diversified portfolio.
	Beta for Stock i = bi =	riM(si/sM)
A portfolio's beta is the weighted average of the stock's individual betas. Consider the following example.
	Stock Beta:	Weight in Portfolio:	Contribution of Stock to Portfolio Beta:
	bi	wi	bi x wi x sM
Stock 1	0.6	25.0%	0.150
Stock 2	1.2	25.0%	0.300
Stock 3	1.2	25.0%	0.300
Stock 4	1.4	25.0%	0.350
		Portfolio beta =	1.100
The standard deviation of a well-diversified portfolio is:
		Std. Dev. of portfolio = sp =	bp (sM)	Note: if the bp is negative, then σp = \|bp\| (σM).
If the example portfolio had more than 4 stocks and was well-diversified, then its standard deviation would be:
		Beta of portfolio = bp =	1.1
		Std. Dev. of market = sM =	20%
		Std. Dev. of portfolio = sp =	22%
Figure 6-7
The Contribution of Individual Stocks to Portfolio Risk: The Effect of Beta
		Market standard deviation = sM =	20.0%
	Stock Beta:	Weight in Portfolio:	Contribution of Stock to Portfolio Beta:	Contribution of Stock to Portfolio Risk:
	bi	wi	bi x wi	bi x wi x sM		Category Labels for chart.
Stock 1	0.6	25.0%	0.150	3.0%		b1w1sM
Stock 2	1.2	25.0%	0.300	6.0%		b2w2sM
Stock 3	1.2	25.0%	0.300	6.0%		b3w3sM
Stock 4	1.4	25.0%	0.350	7.0%		b4w4sM
			1.100	22.0%		b5w5sM
Estimating Beta
We can use the data shown previously for MicroDrive and SnailDrive to estimate their betas.
Calculating Beta			Market	MicroDrive	SnailDrive
		Standard deviation (annual):	19.89%	51.75%	34.17%
		Correlation with the market:		0.511	0.264
		bi = riM(si/sM)		1.330	0.454
Beta can also be calculated as the slope of a regression of the stock (on the y-axis) and the market (on the x-axis). This can be done using the SLOPE function or by plotting the returns and specifying that the chart show the TRENDLINE.
Calculating Beta as the Slope of a Regression Using Excel Functions (See Excel explanations to right)
				MicroDrive	SnailDrive
			bi = riM(si/sM)	1.330	0.454	=SLOPE(F232:F279,$D$232:$D$279)
			Intercept	-0.002	0.004	=INTERCEPT(F232:F279,$D$232:$D$279)
			R squared	0.261	0.070	=RSQ(F232:F279,$D$232:$D$279)
Calculating Confidence Intervals using Excel Functions									See the comment in this cell for instructions for how to use LINEST and estimate confidence intervals. Mike Ehrhardt: Show below are the outputs from the array function LINEST. The yellow area shows what statistics are output by LINEST. To calculate the statistics for MicroDrive, highlight the gray area. Enter the formula =LINEST(E209:E256,D209:D256,TRUE,TRUE), then hit Ctrl-Shift-Enter. Use a similar process to calculate the regression statistics for SnailDrive in the blue region. Recall from statistics that if you take an estimated coefficient from a simple regression, subtract a target value (which is often zero), and then divide that difference by the standard error of the estimated the coefficient, the result will be a t-statistic, which conform to the Student's t-distribution with n-k-1 degrees of freedom, where n is the number of observations and k is the number of explanatory variables. This approach is used to determine whether the estimated coefficient is statistically significantly different from zero. To find the confidence interval around an estimated regression coefficient, you use this relationship, but you pick a target probability rather than a target value. Using the target probability and the degrees of freedom, the TINV function will provide the corresponding value (i.e., t-stat) for a two-tailed t-test. Therefore, the confidence interval corresponding to the target probability has a range equal to 2(t-stat)(standard error of coefficient). The lower end of the range is equal to the estimated coefficient minus (t-stat)(standard error of coefficient); the upper end of the range is equal to the estimated coefficient plus (t-stat)(standard error of coefficient).
			Input desired probability for confidence interval	95%	95%				Excel function LINEST output in an array		LINEST Output for MicroDrive		LINEST Output for SnailDrive
			Lower boundary of confidence interval for beta	0.666	-0.037	See explanation to right.			Slope	Intercept	1.330	-0.002	0.454	0.004
			Upper boundary of confidence interval for beta	1.994	0.946	See explanation to right.			Standard error slope	Standard error intercept	0.330	0.019	0.244	0.014
			Lower boundary of confidence interval for intercept	-0.040	-0.025	See explanation to right.			R squared	Standard error	0.261	0.130	0.070	0.096
			Upper boundary of confidence interval for intercept	0.036	0.032	See explanation to right.			F	Degrees of freedom	16	46	3	46
									SS Regression	SS Residual	0.274	0.775	0.032	0.425
Figure 6-8
Stock Returns of MicroDrive and the Market: Estimating Beta
									Shown below is the output from Excel's regression tool. From the menu bar, select Data, then Data Analysis, the Regression.
									MicroDrive									Snail Drive
									SUMMARY OUTPUT									SUMMARY OUTPUT
									Regression Statistics									Regression Statistics
									Multiple R	0.5108093315								Multiple R	0.2625490104
									R Square	0.2609261731								R Square	0.0689319829
									Adjusted R Square	0.2445023103								Adjusted R Square	0.0482415825
									Standard Error	0.1312392274								Standard Error	0.0950833686
									Observations	47								Observations	47
									ANOVA									ANOVA
										df	SS	MS	F	Significance F					df	SS	MS	F	Significance F
									Regression	1	0.2736337523	0.2736337523	15.8870161053	0.0002437609				Regression	1	0.0301204181	0.0301204181	3.3315925075	0.0746025035
									Residual	45	0.7750680663	0.0172237348						Residual	45	0.4068381142	0.009040847
									Total	46	1.0487018186							Total	46	0.4369585323
										Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%		Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
									Intercept	-0.0019771906	0.0193616376	-0.1021189763	0.9191159351	-0.0409735306	0.0370191493	-0.0409735306	0.0370191493	Intercept	0.0008949294	0.0140275874	0.0637978128	0.9494137787	-0.0273580818	0.0291479406	-0.0273580818	0.0291479406
									0.0237045632	1.3301353673	0.3337141895	3.9858519924	0.0002437609	0.6580004872	2.0022702474	0.6580004872	2.0022702474	0.0237045632	0.4413077009	0.2417773247	1.8252650513	0.0746025035	-0.0456568281	0.9282722298	-0.0456568281	0.9282722298
EXAMPLE: CALCULATING BETA COEFFICIENTS FOR AN ACTUAL COMPANY
Now we show how to calculate beta for an actual company, General Electric.
Step 1. Retrieve Data
We downloaded stock prices and dividends from http://finance.yahoo.com for General Electric, using its ticker symbol GE, and for the S&P 500 Total Retun Index ( symbol ^SP500TR), which is an index incorporating the prices and dividends of the 500 companies listed in the S&P 500, which tracks 500 actively traded large stocks. For example, to download the GE data, enter its ticker symbol in the upper left section and click Go. Then select Historical Prices from the upper left side of the new page. After the daily prices come up, click monthly prices, enter a start and stop date, and click "Get Prices." When presenting monthly data, the date shown is for the first date in the month, but the data are actually for the last day of trading in the month, so be alert for this. Note that these prices are "adjusted" to reflect any dividends or stock splits. Scroll to the bottom of the page and click "Download to Spreadsheet."
The downloaded data are in csv format. Convert to xlsx by opening a new Excel worksheet, copying the date and adjusted index price data to it, and saving as an xlsx file. Then repeat the process to get the S&P index data. At this point you have returns data for GE and the S&P 500 Total Return Index, as we show below.
Step 2. Calculate Returns
Next, calculate the percentage change in adjusted prices (which already reflect dividends) for GE and the S&P to obtain returns, with the spreadsheet set up as shown below. Yahoo actually adjusts the stock prices to reflect any stock splits or dividend payments. For example, suppose the stock price is $100 in July, the company has a 2-for-1 split, and the actual price in August is $60. The reported adjusted price for August would be $60, but the reported price for July would be $50, which reflects the stock split. This gives an accurate stock return of 20%: ($60-$50)/$50 = 20%, the same as if there had not been a split, in which case the return would have been ($120-$100)/$100 = 20%. Or suppose the actual price in September is $50, the company pays a $10 dividend, and the actual price in October is $60. Shareholders have had a return of ($60+$10-$50)/$50 = 40%. Yahoo reports an adjusted price of $60 for October, and an adjusted price of $42.857 for September, which gives a return of ($60-$42.857)/$42.857 = 40%. In other words, the percent change in the adjusted price accurately reflects the actual return.
At this point, we are ready to calculate some statistics and to find GE's beta coefficient. This is shown below the data.
Not in Textbook: Stock Return Data for GE and the S&P 500 Total Return Index
Month	Market Level (S&P 500 Total Return Index) at Month End	Market's Return	GE Adjusted Stock Price at Month End Michael C. Ehrhardt: Yahoo actually adjusts the stock prices to reflect any stock splits or dividend payments. For example, suppose the stock price is $100 in July, the company has a 2-for-1 split, and the actual price in August is $60. The reported adjusted price for August would be $60, but the reported price for July would be $50, which reflects the stock split. This gives an accurate stock return of 20%: ($60-$50)/$50 = 20%, the same as if there had not been a split, in which case the return would have been ($120-$100)/$100 = 20%. Or suppose the actual price in September is $50,the company pays a $10 dividend, and the actual price in October is $60. Shareholders have had a return of ($60+$10-$50)/$50 = 40%. Yahoo reports an adjusted price of $60 for October, and an adjusted price of $42.857 for September, which gives a return of ($60-$42.857)/$42.857 = 40%. In other words, the percent change in the adjusted price accurately reflects the actual return.	GE's Return
November 2017	5,155.44	3.1%	$18.29	-9.3%
October 2017	5,002.03	2.3%	$20.16	-15.8%
September 2017	4,887.97	2.1%	$23.94	-1.5%
August 2017	4,789.18	0.3%	$24.31	-4.1%
July 2017	4,774.56	2.1%	$25.36	-4.4%
June 2017	4,678.36	0.6%	$26.52	-1.4%
May 2017	4,649.34	1.4%	$26.88	-5.6%
April 2017	4,584.82	1.0%	$28.46	-2.7%
March 2017	4,538.21	0.1%	$29.26	0.8%
February 2017	4,532.93	4.0%	$29.04	0.4%
January 2017	4,359.81	1.9%	$28.93	-5.3%
December 2016	4,278.66	2.0%	$30.55	2.7%
November 2016	4,195.73	3.7%	$29.74	5.7%
October 2016	4,045.89	-1.8%	$28.13	-1.0%
September 2016	4,121.06	0.0%	$28.41	-5.2%
August 2016	4,120.29	0.1%	$29.97	0.3%
July 2016	4,114.51	3.7%	$29.87	-0.3%
June 2016	3,968.21	0.3%	$29.97	4.1%
May 2016	3,957.95	1.8%	$28.78	-1.7%
April 2016	3,888.13	0.4%	$29.28	-3.3%
March 2016	3,873.11	6.8%	$30.27	10.0%
February 2016	3,627.06	-0.1%	$27.52	0.1%
January 2016	3,631.96	-5.0%	$27.49	-5.9%
December 2015	3,821.60	-1.6%	$29.20	4.0%
November 2015	3,882.84	0.3%	$28.07	3.5%
October 2015	3,871.33	8.4%	$27.11	15.7%
September 2015	3,570.17	-2.5%	$23.43	0.8%
August 2015	3,660.75	-6.0%	$23.24	-4.2%
July 2015	3,895.80	2.1%	$24.25	-0.9%
June 2015	3,815.85	-1.9%	$24.48	-2.6%
May 2015	3,891.18	1.3%	$25.13	0.7%
April 2015	3,841.78	1.0%	$24.95	9.1%
March 2015	3,805.27	-1.6%	$22.86	-3.7%
February 2015	3,866.42	5.7%	$23.73	8.8%
January 2015	3,656.28	-3.0%	$21.81	-4.6%
December 2014	3,769.44	-0.3%	$22.86	-4.6%
November 2014	3,778.96	2.7%	$23.96	2.6%
October 2014	3,679.99	2.4%	$23.34	1.6%
September 2014	3,592.25	-1.4%	$22.98	-1.4%
August 2014	3,643.33	4.0%	$23.30	3.3%
July 2014	3,503.19	-1.4%	$22.56	-3.5%
June 2014	3,552.18	2.1%	$23.38	-1.9%
May 2014	3,480.29	2.3%	$23.83	-0.4%
April 2014	3,400.46	0.7%	$23.92	3.9%
March 2014	3,375.51	0.8%	$23.03	2.5%
February 2014	3,347.38	4.6%	$22.46	1.4%
January 2014	3,200.95	-3.5%	$22.16	-9.6%
December 2013	3,315.59	2.5%	$24.52	5.1%
November 2013	3,233.72	NA	$23.32	NA
Description of Data
Average return (annual):		12.2%		-4.3%
Standard deviation (annual):		9.6%		18.7%
Minimum monthly return:		-6.0%		-15.8%
Maximum monthly return:		8.4%		15.7%
Correlation between GE and the market:				0.51
Beta: bGE = rGE,M (sGE / sM)				0.99
Beta (using the SLOPE function):				0.99
Intercept (using the INTERCEPT function):				-0.01
R2 (using the RSQ function):				0.26
Step 3. Examine the Data and Calculate Beta
Using the AVERAGE function and the STDEV function, we found the average historical return and standard deviation for GE and the market. (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 used the CORREL function to find the correlation between GE and the market. We used the SLOPE, INTERCEPT, and RSQ functions to estimate the regression for beta.
6-7 The Relationship between Risk and Return in the Capital Asset Pricing Model
The SML shows the relationship between the stock's beta and its required return, as predicted by the CAPM.
rRF	6%	<< Varies over time, but is constant for all firms at a given time.
rM	11%	<< Varies over time, but is constant for all firms at a given time.
bi	0.5	<< Varies over time, and varies from firm to firm.
The SML predicts stock i's required return to be:
RPM = rM - rRF
ri = rRF + bi(RPM)
RPM = rM - rRF =	5%
ri = rRF + bi(RPM)	8.5%
With the above data, we can generate a Security Market Line that is flexible enough to allow for changes in
any of the input factors. We generate a table of values for beta and expected returns, and then plot the
graph as a scatter diagram.
	Beta	Security Market Line: ri	Risk-Free Rate
	0.00	6.0%	6%
	0.50	8.5%	6%
	1.00	11.0%	6%
	1.50	13.5%	6%
	2.00	16.0%	6%
Figure 6-9
The Security Market Line
The Security Market Line shows the projected changes in expected return, due to changes in the beta coefficient. However, we can also look at the potential changes in the required return due to variations in other factors, for example the market return and risk-free rate. In other words, we can see how required returns can be influenced by changing inflation and risk aversion. The level of investor risk aversion is measured by the market risk premium (rM – rRF), which is also the slope of the SML. Hence, an increase in the market return results in an increase in the maturity risk premium, other things held constant.
Portfolio Returns
The same relationship holds for required returns: The required return on a portfolio is simply a weighted average of the required returns of the individual assets in the portfolio. The weights are the percentage of total portfolio funds invested in each asset. The required return on a portfolio is also equal to:
	rp = rRF + bp(RPM)
The expected return on a portfolio is simply a weighted average of the expected returns of the individual assets in the portfolio. The weights are the percentage of total portfolio funds invested in each asset. Consider the following portfolio and the hypothetical illustrative returns data.
Stock	Amount of Investment	Portfolio Weight	Expected Return	Weighted Expected Return
Southwest Airlines	$300,000	0.3	15.0%	4.5%
Starbucks	$100,000	0.1	12.0%	1.2%
FedEx	$200,000	0.2	10.0%	2.0%
Dell	$400,000	0.4	9.0%	3.6%
Total investment =	$1,000,000	1.0
			Portfolio's Expected Return =	11.3%
6-8 The Efficient Markets Hypothesis
The Efficient Markets Hypothesis (EMH) asserts that (1) stocks are always in equilibrium and (2) it is impossible for an investor to “beat the market” and consistently earn a higher rate of return than is justified by the stock’s risk.
6-9 The Fama-French Three-Factor Model
The Fama-French 3-Factor model shows the actual stock return given the risk-free rate, the return on the market, the return on the SMB portfolio, and the return on the HML portfolio:
Suppose a company announces that it is going to include more outsiders on its board of directors and that the company’s stock falls by 2% on the day of the announcement. Does that mean that investors don’t want outsiders on the board?
		Actual return on announcement day =	-2%
Suppose you estimate the following coefficients of the Fama-French model using historical actual data prior to the announcement date:
ai =	0.0
bi =	0.9
ci =	0.2
di =	0.3
These are the returns on the announcement day:
rRF ≈	0.0%
rM =	-3.0%
rSMB =	-1.0%
rHML =	-2.0%
The predicted return on the announcement day:
	Predicted return =	rRF,t + ai + bi(rM,t - rRF,t) + ci(rSMB,t) + di(rHML,t)
	Predicted return =	-3.5%
	Unexplained return =	Actual return - predicted return
	Unexplained return =	1.5%

Security Market Line: ri

0 0.5 1 1.5 2 0.06 8.4999999999999992E-2 0.11 0.13500000000000001 0.16 Risk-Free Rate

Risk-Free Rate

0 0.5 1 1.5 2 0.06 0.06 0.06 0.06 0.06

Beta

Required Return

0.37 0.11 -0.15 0.3 0.4 0.3

Outcomes: Market Returns for 3 Scenarios

Probability of Scenario

-0.66 -0.55000000000000004 -0.44000000000000006 -0.33000000000000007 -0.22000000000000008 -0.11000000000000008 0 0.11 0.22 0.33 0.44 0.55000000000000004 0.66 0.77 0.88 1.7511394806058752E-4 1.0680516885827041E-3 5.4186431553394105E-3 2.0452870329666931E-2 5.7451043229686055E-2 0.12012007299591695 0.18697232380635256 0.21668376169278963 0.18697232380635254 0.12012007299591698 5.7451043229685972E-2 2.0452870329666917E-2 5.4186431553394643E-3 1.0680516885827052E-3 1.7511394806057901E-4

Outcomes: Market Returns

Probability

Normal Distribution

Return

Probability

MicroDrive 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 2.8123990924714889E-2 0.14788012262092151 7.8502963553737266E-3 8.7129468545996486E-2 8.3412755288645168E-3 -0.32424777774866015 0.22562272254962037 -0.2421188972496903 8.0033999253312074E-2 -1.2880104827403847E-2 4.4340333222723204E-2 -6.3572272894571916E-2 0.11793465207595152 0.2132415493195787 -0.10275248323559877 3.9638703647158331E-2 0.34980897959761476 2.5618545344283039E-2 -0.10798804364762635 -6.6991550094965782E-2 -2.309098107106064E-2 8.2631500961910756E-2 5.0805676359279997E-3 -0.14613977701929601 -4.5612755984871939E-2 -0.12083292328306293 0.31684468589336262 4.4284410066492273E-2 3.1607939009120284E-3 4.5883106630769353E-2 2.07552456031646E-2 -0.15485866662189754 0.32391586170454112 0.15150100919688878 -3.7185495271657959E-2 -0.1539751666991345 -0.12667133730723446 -0.10429634808460107 -7.1363934717645017E-2 -4.8514454737545759E-2 8.146235222622937E-2 -0.14717242637944389 0.32446717204181608 -0.2833663222356419 -5.5520125185090474E-2 5.8097374046303288E-2 4.0169352047872364E-2 4.3819809396092653E-2 SnailDrive 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 0.1492690115098104 -3.2625626037028344E-2 -0.10573003868698551 -0.10761017744603606 9.7091665313481013E-2 6.4019696075159227E-2 2.551740058812201E-3 5.1823111248978371E-3 -8.6675542060738534E-2 0.21619937238442177 3.8682354577748018E-2 4.9976714369936108E-2 -0.1231552558765608 -2.4334023349728399E-2 4.8709991986939309E-2 -0.17848886713987205 -0.10894557506251189 -0.16677693278427547 9.02224897298773E-2 0.22602742043638951 0.14253323854601735 -7.6768659825705354E-2 -2.5191647655802694E-2 9.3197313516893962E-2 -1.3762620727642588E-2 0.13919465840323408 3.9062205051343148E-2 8.9076694586209232E-2 -3.7587548782611876E-2 -3.951356902881889E-2 -1.4254586969160453E-2 -6.3757773671727844E-2 0.11996362520663377 -2.003461329144321E-2 -0.12506 156308683511 -4.944118628225227E-3 9.9145429710516583E-2 -8.206824068176545E-2 -0.11269202815269945 -0.10323090464415442 9.1937173710245873E-2 -4.2822325102026349E-3 9.8823607828717221E-3 3.955891709074854E-2 -8.5047861043818507E-2 0.16096750035334298 8.8623069016098177E-2 1.3046620641216391E-2

Month of Return

Monthly Rate of Return

MicroDrive

y = 1.33x - 0.002 R² = 0.2612

2.3704563216878447E-2 0.12678838148916216 -1.1271722117620619E-2 0.10934853098288892 -1.5001243046439301E-4 -3.3094084606626398E-2 0.11894009781525601 -3.9637113175926407E-2 -4.900848341 6118451E-2 7.1005972903079154E-2 2.9372997557913449E-2 -6.5174257007283729E-2 3.7154046682836443E-2 4.7403568255148722E-2 -8.2053210024488593E-2 -5.1497959731137855E-2 3.9150545149045569E-2 1.0802516698457023E-2 -2.48410615005232E-2 3.9150545149045569E-2 3.129112880648411E-2 1.6802075480611047E-3 5.1725910558059016E-2 2.5581205766142971E-2 -5.4058128270569515E-2 -2.0870420462762541E-2 1.0802516698457023E-2 0.10467065567288585 -3.7416426909536671E-2 2.9372997557913449E-2 -9.5039047334366744E-2 -3.0986002707321395E-2 7.9483740655806087E-2 0.10934853098288892 -1.6981645157961755E-2 -3.9637113175926407E-2 5.1725910558059016E-2 -7.5293925805213309E-3 -9.0361172024363487E-2 -9.5039047334366744E-2 4.7403568255148722E-2 -3.8255645271969832E-3 4.3218973200413506E-2 -1.8916714871863298E-2 -3.9637113175926407E-2 6.5807443379660199E-2 -1.316071492587351E-2 4.7403568255148722E-2 2.8123990924714889E-2 0.14788012262092151 7.8502963553737266E-3 8.7129468545996486E-2 8.3412755288645168E-3 -0.32424777774866015 0.22562272254962037 -0.2421188972496903 8.0033999253312074E-2 -1.2880104827403847E-2 4.4340333222723204E-2 -6.3572272894571916E-2 0.11793465207595152 0.2132415493195787 -0.10275248323559877 3.9638703647158331E-2 0.34980897959761476 2.5618545344283039E-2 -0.10798804364762635 -6.6991550094965782E-2 -2.309098107106064E-2 8.2631500961910756E-2 5.0805676359279997E-3 -0.14613977701929601 -4.5612755984871939E-2 -0.12083292328306293 0.31684468589336262 4.4284410066492273E-2 3.1607939009120284E-3 4.5883106630769353E-2 2.07552456031646E-2 -0.15485866662189754 0.32391586170454112 0.151501009196888 78 -3.7185495271657959E-2 -0.1539751666991345 -0.12667133730723446 -0.10429634808460107 -7.1363934717645017E-2 -4.8514454737545759E-2 8.146235222622937E-2 -0.14717242637944389 0.32446717204181608 -0.2833663222356419 -5.5520125185090474E-2 5.8097374046303288E-2 4.0169352047872364E-2 4.3819809396092653E-2 Market vs. Market

Market vs. Market

2.3704563216878447E-2 0.12678838148916216 -1.1271722117620619E-2 0.10934853098288892 -1.5001243046439301E-4 -3.3094084606626398E-2 0.11894009781525601 -3.9637113175926407E-2 -4.9008483416118451E-2 7.1005972903079154E-2 2.9372997557913449E-2 -6.5174257007283729E-2 3.7154046682836443E-2 4.7403568255148722E-2 -8.2053210024488593E-2 -5.1497959731137855E-2 3.9150545149045569E-2 1.0802516698457023E-2 -2.48410615005232E-2 3.9150545149045569E-2 3.129112880648411E-2 1.6802075480611047E-3 5.1725910558059016E-2 2.5581205766142971E-2 -5.4058128270569515E-2 -2.0870420462762541E-2 1.0802516698457023E-2 0.10467065567288585 -3.7416426909536671E-2 2.9372997557913449E-2 -9.5039047334366744E-2 -3.0986002707321395E-2 7.9483740655806087E-2 0.10934853098288892 -1.6981645157961755E-2 -3.9637113175926407E-2 5.1725910558059016E-2 -7.5293925805213309E-3 -9.0361172024363487E-2 -9.5039047334366744E-2 4.7403568255148722E-2 -3.8255645271969832E-3 4.3218973200413506E-2 -1.8916714871863298E-2 -3.9637113175926407E-2 6.5807443379660199E-2 -1.316071492587351E-2 4.7403568255148722E-2 2.3704563216878447E-2 0.12678838148916216 -1.1271722117620619E-2 0.10934853098288892 -1.50012430464 39301E-4 -3.3094084606626398E-2 0.11894009781525601 -3.9637113175926407E-2 -4.9008483416118451E-2 7.1005972903079154E-2 2.9372997557913449E-2 -6.5174257007283729E-2 3.7154046682836443E-2 4.7403568255148722E-2 -8.2053210024488593E-2 -5.1497959731137855E-2 3.9150545149045569E-2 1.0802516698457023E-2 -2.48410615005232E-2 3.9150545149045569E-2 3.129112880648411E-2 1.6802075480611047E-3 5.1725910558059016E-2 2.5581205766142971E-2 -5.4058128270569515E-2 -2.0870420462762541E-2 1.0802516698457023E-2 0.10467065567288585 -3.7416426909536671E-2 2.9372997557913449E-2 -9.5039047334366744E-2 -3.0986002707321395E-2 7.9483740655806087E-2 0.10934853098288892 -1.6981645157961755E-2 -3.9637113175926407E-2 5.1725910558059016E-2 -7.5293925805213309E-3 -9.0361172024363487E-2 -9.5039047334366744E-2 4.7403568255148722E-2 -3.8255645271969832E-3 4.321 8973200413506E-2 -1.8916714871863298E-2 -3.9637113175926407E-2 6.5807443379660199E-2 -1.316071492587351E-2 4.7403568255148722E-2

x-axis: Historical Market Returns

y-axis: Historical MicroDrive Returns

Portfolio standard deviation = 22%

b1w1sM = 3.0%

b2w2sM = 6.0%

b3w3sM = 6.0%

b4w4sM = 7.0%

b1w1sM b2w2sM b3w3sM b4w4sM 0.03 0.06 0.06 6.9999999999999993E-2 -0.66 -0.55000000000000004 -0.44000000000000006 -0.33000000000000007 -0.22000000000000008 -0.11000000000000008 0 0.11 0.22 0.33 0.44 0.55000000000000004 0.66 0.77 0.88 1.977148949688342E-2 3.0692439939570888E-2 4.5197958685507564E-2 6.2478934368428322E-2 8.0632887903820935E-2 9.6879145814807499E-2 0.10820778739354575 0.11227871279487092 0.10820778739354575 9.6879145814807499E-2 8.0632887903820935E-2 6.2478934368428322E-2 4.5197958685507564E-2 3.0692439939570888E-2 1.977148949688342E-2

Outcomes: Stock Returns

Probability

35%

6-1

SECTION 6-1
SOLUTIONS TO SELF-TEST
Suppose you pay $500 for an investment that returns $600 in one year. What is the annual rate of return?
Amount invested			$500
Amount received in one year			$600
Dollar return			$100
Rate of return			20%

6-2

SECTION 6-2
SOLUTIONS TO SELF-TEST
An investment has a 20% chance of producing a 25% return, a 60% chance of producing a 10% return, and a 20% chance of producing a -15% return. What is its expected return? What is its standard deviation?
Probability	Return	Prob x Ret.
20%	25%	5.0%
60%	10%	6.0%
20%	-15%	-3.0%
	Expected return =	8.0%
Alternatively, use the Excel SUMPRODUCT function, which will multiply each value in the first array be the corresponding value in the next array, and the sum them. This is exactly the calculation shown in the step-by-step manner above:
	Expected return =	8.0%
Probability	Return	Deviation from expected return	Deviation2	Prob x Dev.2
20%	25%	17.0%	2.890%	0.578%
60%	10%	2.0%	0.040%	0.024%
20%	-15%	-23.0%	5.290%	1.058%
			Variance =	0.01660
			Standard deviation =	12.9%
Alternatively, use the Excel SUMPRODUCT function, which will multiply each value in the first array be the corresponding value in the next array, and the sum them. If the first array has probabilities and the second array subtracts the mean from the array of outcomes and is squared, then this is exactly the calculation shown in the step-by-step manner above to find variance:
	Variance =	0.01660
	Standard deviation =	12.9%

6-4

SECTION 6-4
SOLUTIONS TO SELF-TEST
A stock’s returns for the past three years are 10%, -15%, and 35%. What is the historical average return? What is the historical sample standard deviation?
		Realized
	Year	return
	1	10%
	2	-15%
	3	35%
	Average =	10.0%
	Standard deviation =	25.0%

6-5

SECTION 6-5
SOLUTIONS TO SELF-TEST
Stock A's returns the past five years have been 10%, −15%, 35%, 10%, and −20%. Stock B's returns have been −5%, 1%, −4%, 40%, and 30%. What is the correlation coefficient for returns between Stock A and Stock B?
		Realized Returns
	Year	Stock A	Stock B
	1	10%	-5%
	2	-15%	1%
	3	35%	-4%
	4	10%	40%
	5	-20%	30%
	Average =	4.0%	12.4%
	Standard deviation =	22.2%	21.1%
		Correlation between Stock A and Stock B:	-0.35

6-6

SECTION 6-6
SOLUTIONS TO SELF-TEST
An investor has a 3-stock portfolio with $25,000 invested in Apple, $50,000 invested in Ford, and $25,000 invested in Walmart. Dell’s beta is estimated to be 1.20, Ford’s beta is estimated to be 0.80, and Walmart's beta is estimated to be 1.0. What is the estimated beta of the investor’s portfolio?
Stock	Investment	Beta	Weight	Beta x Weight
Apple	$25,000	1.2	0.25	0.30
Ford	$50,000	0.8	0.50	0.40
Walmart	$25,000	1.0	0.25	0.25
Total	$100,000
			Portfolio beta =	0.95

6-7

SECTION 6-7
SOLUTIONS TO SELF-TEST
A stock has a beta of 0.8. Assume that the risk-free rate is 5.5% and that the market risk premium is 6%. What is the stock’s required rate of return?
Beta		0.8
Risk-free rate		5.5%
Market risk premium		6.0%
Required rate of return		10.30%

6-9

SECTION 6-9
SOLUTIONS TO SELF-TEST
An analyst has modeled the stock of a company using a Fama-French three-factor model and has estimate that ai = 0, bi = 0.7, ci = 1.2, and di = 0.7. Suppose the daily risk-free rate is approximately equal to zero, the market return is 11%, the return on the SMB portfolio is 3.2%, and the return on the HML portfolio is 4.8% on a particular day. The stock had an actual return of 16.9% on that day. What is the stock's predicted return for that day? What is the stock’s unexplained return for the day?
ai	0.0%
bi	0.70
ci	1.20
di	0.70
Actual stock return	16.9%
Risk-free rate	0.0%
Market return	11.0%
SMB return	3.2%
HML return	4.8%
Predicted return	14.90%
Unexplained return	2.00%