Question

In: Finance

Risk and Return 1. Calculate monthly returns for Darden Restaurants and the S&P 500. Be sure...

Risk and Return
1. Calculate monthly returns for Darden Restaurants and the S&P 500. Be sure to include the dividends for Darden in the return. Show All excel formulas.
	Darden (DRI)		S&P 500
Month	Price	Dividend	Price
6/1/16	59.11		2098.86		DRI Return	SP Return	DRI- Ave	S&P - Ave		(DRI-Ave)x(S&P-Ave)
7/1/16	57.446472	0.56	2173.600098
8/1/16	58.043533		2170.949951
9/1/16	57.742203		2168.27002
10/1/16	61.009743	0.56	2126.149902
11/1/16	69.65522		2198.810059
12/1/16	69.10405		2238.830078
1/1/17	69.636215	0.56	2278.870117
2/1/17	71.520325		2363.639893
3/1/17	80.129959		2362.719971
4/1/17	81.585648	0.56	2384.199951
5/1/17	85.746788		2411.800049
6/1/17	87.202744		2423.409912
7/1/17	80.877548	0.63	2470.300049
8/1/17	79.708763		2471.649902
9/1/17	76.494789		2519.360107
10/1/17	79.883553	1.26	2575.26001
11/1/17	83.174545		2584.840088
12/1/17	94.715607		2673.610107
1/1/18	94.547913	0.63	2823.810059
2/1/18	91.521347		2713.830078
3/1/18	84.631676		2640.870117
4/1/18	92.186485	0.63	2648.050049
5/1/18	87.410004		2705.27002
6/1/18	93.269997		2767.320068

2. Calculate the average, standard deviation, and variance of returns for Darden and the S&P 500. Use the function wizard (fx).							3. Use the covariance equation to find the covariance between Darden and the S&P 500.

				Average					Sum
				Std. Dev					n-1
				Variance					COV

6. Use Regression Analysis to find the beta for Darden. You should get the same answer as in question 5. Print your results below.							4. Use the Correlation Coefficient equation to find the correlation between Darden and the S&P 500.


									Corr

							5. Use the Beta equation to find the beta of Darden.

									Beta

							7. Answer the following concept questions: a. What is the probability that the true beta for Darden is equal to zero? b. If the market risk premium is 7% and the risk-free rate is 2%, what return would you expect to earn on Darden given YOUR beta calculation?

Solutions

Expert Solution

(1) Computation of the monthly return for Darden Restaurants and the S&P 500.We have,

Risk and Return
	Darden (DRI)		S&P 500
Month	Price	Dividend	Price
06-01-2016	59.11		2098.86	DRI Return	SP Return	DRI- Ave	S&P - Ave	(DRI- Ave)2	(S&P - Ave)2	(DRI-Ave)x(S&P-Ave)
07-01-2016	57.44647	0.56	2173.6001	-1.866905769	3.56098539	-3.938421494	2.42762549	15.51116386	5.893365522	-9.56101241
08-01-2016	58.04353		2170.95	1.039334496	-0.12192431	1.039334496	-0.121924314	1.080216194	0.014865538	-0.126720145
09-01-2016	57.7422		2168.27	-0.519144829	-0.12344508	-0.519144829	-0.123445084	0.269511353	0.015238689	0.064085877
10-01-2016	61.00974	0.56	2126.1499	6.628669848	-1.94256793	6.628669848	-1.942567928	43.93926396	3.773570155	-12.87664145
11-01-2016	69.65522		2198.8101	14.17064976	3.41745222	14.17064976	3.417452219	200.8073146	11.67897967	48.42751846
12-01-2016	69.10405		2238.8301	-0.791283123	1.82007622	-0.791283123	1.82007622	0.62612898	3.312677445	-1.440195595
01-01-2017	69.63622	0.56	2278.8701	1.580464531	1.78843582	1.580464531	1.788435817	2.497868135	3.198502672	2.826559376
02-01-2017	71.52033		2363.6399	2.705646767	3.71981603	2.705646767	3.719816034	7.320524429	13.83703132	10.06450823
03-01-2017	80.12996		2362.72	12.03802416	-0.03891972	12.03802416	-0.038919719	144.9140256	0.001514745	-0.468516515
04-01-2017	81.58565	0.56	2384.2	2.515524811	0.90912085	2.515524811	0.909120855	6.327865077	0.826500729	2.286916067
05-01-2017	85.74679		2411.8	5.100333333	1.15762514	5.100333333	1.157625139	26.01340011	1.340095963	5.904274084
06-01-2017	87.20274		2423.4099	1.697971474	0.48137751	1.697971474	0.481377509	2.883107126	0.231724306	0.817365279
07-01-2017	80.87755	0.63	2470.3	-6.530982557	1.93488261	-6.530982557	1.934882612	42.65373316	3.743770721	-12.63668459
08-01-2017	79.70876		2471.6499	-1.445129123	0.05464328	-1.445129123	0.054643281	2.088398182	0.002985888	-0.078966597
09-01-2017	76.49479		2519.3601	-4.032146378	1.93029785	-4.032146378	1.930297853	16.25820441	3.726049803	-7.783243497
10-01-2017	79.88355	1.26	2575.26	6.077229653	2.21881353	6.077229653	2.218813533	36.93272025	4.923133494	13.4842394
11-01-2017	83.17455		2584.8401	4.119736637	0.3720043	4.119736637	0.372004301	16.97222996	0.1383872	1.532559748
12-01-2017	94.71561		2673.6101	13.87571402	3.43425574	13.87571402	3.434255736	192.5354395	11.79411246	47.65275046
01-01-2018	94.54791	0.63	2823.8101	0.488099073	5.61787044	0.488099073	5.617870444	0.238240705	31.56046833	2.742077358
02-01-2018	91.52135		2713.8301	-3.201092339	-3.89473721	-3.201092339	-3.894737206	10.24699216	15.16897791	12.46741343
03-01-2018	84.63168		2640.8701	-7.52793881	-2.68844986	-7.52793881	-2.688449862	56.66986272	7.227762663	20.23848606
04-01-2018	92.18649	0.63	2648.05	9.671094071	0.27187751	9.671094071	0.271877513	93.53006053	0.073917382	2.629353006
05-01-2018	87.41		2705.27	-5.181324573	2.1608342	-5.181324573	2.160834197	26.84612433	4.669204425	-11.19598332
06-01-2018	93.27		2767.3201	1.175347992	2.293673	1.175347992	2.293672999	1.381442901	5.260935827	2.695863953
Total				51.78789312	28.3339975		27.20063764	948.5438383	132.4137729	132.4137729
Average return				2.071515725	1.133359902

Note: Monthly return = [ Dividend income + ( Ending price - Begenning price)] / Begenning price

(2-a) Computation of the average return.We have,

Average return = Sum of all return / Number of return

For Darden:

Average return =51.78789312 / 25 = 2.071515725 %

For S&P 500:

Average return = 28.3339975 / 25 = 1.133359902 %

(2-b) Comutation of variance.We have,

For Darden:

Variance = Sum of (DRI- Ave)² / n - 1

Variance = 948.5438383 / 25 - 1 = 39.52265 %

For S&P 500:

Variance = Sum of (S&P- Ave)² / n - 1

Variance = 132.4137729 / (25 - 1) = 5.5172405 %

(2-c) Computation of the standard deviation.We have,

For Darden:

Standard deviation = Square root of variance

Standard Deviation = = 6.28670 %

For S&P 500:

Standard deviation = Square root of variance

Standard Deviation = = 2.348880 %

(3) Computation of the covariance between Darden and the S&P 500.We have,

Covariance =

Covariance = 132.4137729 / (25 -1) = 5.517240 %

(4) Computation of the correlation between Darden and the S&P 500.We have,

Correlation between Darden and the S&P 500 = Covariance / (Standard deviation of Dardan stock x Standard deviation of S&P500)

Correlation between Darden and the S&P 500 =5.517240 / ( 6.28670 x 2.348880)

Correlation between Darden and the S&P 500 = 0.373627

(5) Computation of beta for Darden stock by beta equation.We have,

Beta of Darden = Correlation Coefficient between darden stock and S&P x Standard deviation of S&P/ Standard deviation of darden stock

Beta of Darden = 0.373627 x 2.348880/ 6.28670 = 0.139597

Hence, the beta of stock is 0.139597

(6) Computation of beta for Darden stock by regresion analysis.We have,

Beta of Darden Stock = Covariance of Darden stock and S&P500 / (Standard deviation of darden stock)²

Beta of Darden Stock = 5.517240 / (6.28670)² = 0.139597

Hence,the beta of Darden stock is 0.139597

(7-a) Computation of the probability that the true beta for Darden is equal to zero.We have,

True beta of Darden stock is equal to zero when expected average return is equal to risk-free rate of return.

Risk-free rate of return = 2 %

Average return = 2.071515725 %

Probability that the true beta is equal to zero = ( 2.071515725 - 2.00) / 2.00 = 0.0357*100 = 3.57 %

(7-b) Computation of the expected return of Darden stock.We have,

Using capital asset pricing model.We get:

Expected return = Risk-free return + Beta of stock x Market risk premium

Expected return = 2 + 0.139597 x 7 = 2.97718 %

Hence, the expected return of Darden stock is 2.97718 %.

jeff jeffy answered 1 year ago

Next > < Previous

Related Solutions

Risk and Return 1. Calculate monthly returns for Darden Restaurants and the S&P 500. Be sure...

Risk and Return 1. Calculate monthly returns for Darden Restaurants and the S&P 500. Be sure to include the dividends for Darden in the return. Please show equations in Excel. Darden (DRI) S&P 500 Month Price Dividend Price 6/1/16 59.11 2098.86 DRI Return SP Return DRI- Ave S&P - Ave (DRI-Ave)x(S&P-Ave) 7/1/16 57.446472 0.56 2173.600098 8/1/16 58.043533 2170.949951 9/1/16 57.742203 2168.27002 10/1/16 61.009743 0.56 2126.149902 11/1/16 69.65522 2198.810059 12/1/16 69.10405 2238.830078 1/1/17 69.636215 0.56 2278.870117 2/1/17 71.520325 2363.639893 3/1/17 80.129959...

1. With the limited information below on monthly returns for a stock and the S&P 500...

1. With the limited information below on monthly returns for a stock and the S&P 500 market index, what would you guess is this stock's CAPM beta? Month 1: Stock -7.4%, Market -2.0% Month 2: Stock -4.0%, Market -4.0% Month 3: Stock +2.2%, Market +0.5% Month 4: Stock +13.4%, Market +10.8% Group of answer choices: a) Beta is greater than one b) Beta is one c) Beta is between zero and one d) Beta is zero e) Beta is negative...

Given the monthly returns that follow: Month Portfolio Return S&P 500 Return Jan. 5.5.% 5.8% Feb....

Given the monthly returns that follow: Month Portfolio Return S&P 500 Return Jan. 5.5.% 5.8% Feb. -2.4 -3.3 March -1.8 -1.5 April 2.7 2.0 May 0.7 -0.1 June -0.9 -0.4 July 0.1 0.5 August 1.5 2.0 September -0.8 -0.6 October -3.2 -3.7 November 2.4 1.6 December 0.6 0.1 Calculate R2: Alpha: % Beta: Average return difference (with signs): % Average return difference (without signs) %

The expected return of the S&P 500 = 11% and the risk = 22%. The risk...

The expected return of the S&P 500 = 11% and the risk = 22%. The risk free rate = 5%. Assume you have a very, very highly risk averse person whose A = 1,000. How much of the person’s wealth would be in stocks and how much in T-Bills?

Assume you plot the monthly returns for a stock and also for the S&P 500. Using...

Assume you plot the monthly returns for a stock and also for the S&P 500. Using regression analysis, the straight line through these points that is developed by the analysis is referred to as the ______ which has a slope of _____ and an intercept of ______. A. characteristic line; alpha; beta B. security market line; alpha; gamma C. characteristic line; gamma; alpha D. security market line; beta; gamma E. characteristic line; beta; alpha

Use the following table to complete questions 1-3. S&P 500 returns and the risk-free rate of...

Use the following table to complete questions 1-3. S&P 500 returns and the risk-free rate of return can take on the following possible values with the given probabilities. State Pi S&P 500 Returns Risk-Free Rate Recession 0.25 -10% 8% Low Pullback 0.25 -5% 8% No Growth 0.25 0% 8% Low Growth 0.25 5% 8% 1. What is the expected value of S&P 500 returns? What is the expected value of risk-free returns? 2. What is the variance and standard deviation...

Calculate Adidas' and S&P 500 Index's average rate of returns and standard deviation, based on the...

Calculate Adidas' and S&P 500 Index's average rate of returns and standard deviation, based on the data below. DATA Month Adidas S&P 500 Stock Prices Index Jan-19 52.9 2368.45 Feb-19 67.16 2363.64 Mar-19 68.73 2362.72 Apr-19 65.41 2384.2 May-19 62.99 2411.8 Jun-19 70.1 2423.41 Jul-19 69.05 2470.3 Aug-19 62.81 2471.65 Sep-19 61.85 2519.36 Oct-19 64.99 2575.26 Nov-19 70.42 2647.58 Dec-19 72.55 2673.61

The risk-free rate, average returns, standard deviations, and betas for three funds and the S&P 500...

The risk-free rate, average returns, standard deviations, and betas for three funds and the S&P 500 are given below. FUND AVG STD DEV BETA A 18% 30% 1.05 B 25% 35% 1.3 C 20% 25% 1.2 S&P 500 15% 20% 1.0 Rf 5% If these portfolios are subcomponents that make up part of a well-diversified portfolio, then portfolio ______ is preferred. a. fund a b. fund b c. fund c d. s&p 500

Historically, the one-year returns follow approximately the normal distribution. The one-year return for the S&P 500...

Historically, the one-year returns follow approximately the normal distribution. The one-year return for the S&P 500 was +27% (that is, 0.27) and its standard deviation is 20% (that is, 0.2). What is the probability that a stock in the S&P 500 gained 30% or more last year? (a) 0.0668 (i.e., 6.68%) (b) 0.4404 (i.e., 44.04%) (c) 0.5596 (i.e., 55.96%) (d) 0.9332 (i.e., 93.32%) What is the probability that a stock in the S&P 500 lost 10% or more last year?...

The risk-free rate, S&P 500 index, and the average return, standard deviation, beta, and residual standard...

The risk-free rate, S&P 500 index, and the average return, standard deviation, beta, and residual standard deviation for three funds are given. Fund Avg. Return Std. Dev. Beta Residual Std. Dev. A B C S&P 500 Risk-free 18 25 22 12 4 15 30 20 10 0 1.3 1.4 1.2 1.0 0 1.5 2.5 3.0 1.Figure out the M2 measure for Fund A and B 2.Figure out the best fund based on the information ratio Use the following table. Your...

Subjects