Question #: Lebron James contains the monthly return data for Microsoft (MSFT), IBM (IBM), Apple (AAPL)...

Question #:

Lebron James contains the monthly return data for Microsoft (MSFT), IBM (IBM), Apple (AAPL) and Hewlett-Packard (HPQ) for the period 2007/1- 2016/12.

Month	MSFT	IBM	AAPL	HPQ
20070131	0.033490	0.020587	0.010490	0.050740
20070228	-0.083927	-0.059607	-0.013064	-0.090804
20070330	-0.010650	0.014203	0.098097	0.022109
20070430	0.074273	0.084341	0.074158	0.049826
20070531	0.028393	0.046864	0.214339	0.084718
20070629	-0.039756	-0.012664	0.007005	-0.022096
20070731	-0.016288	0.051306	0.079646	0.031600
20070831	-0.005519	0.058202	0.051002	0.072127
20070928	0.025409	0.009512	0.108247	0.010537
20071031	0.249491	-0.014261	0.237701	0.037959
20071130	-0.084216	-0.090768	-0.040695	-0.010062
20071231	0.059524	0.027762	0.087038	-0.011728
20080131	-0.084270	-0.009158	-0.316640	-0.133914
20080229	-0.162273	0.066754	-0.076389	0.092635
20080331	0.043386	0.011242	0.147816	-0.042495
20080430	0.004933	0.048289	0.212195	0.015112
20080530	-0.003156	0.076471	0.085082	0.015318
20080630	-0.028602	-0.084215	-0.112901	-0.058861
20080731	-0.065067	0.079727	-0.050705	0.013345
20080829	0.065319	-0.044929	0.066562	0.047321
20080930	-0.021986	-0.039185	-0.329558	-0.012788
20081031	-0.163357	-0.205113	-0.053405	-0.172145
20081128	-0.088670	-0.116919	-0.138675	-0.078370
20081231	-0.038576	0.031373	-0.078990	0.030896
20090130	-0.120370	0.088997	0.056005	-0.042436
20090227	-0.047953	0.009602	-0.009098	-0.164604
20090331	0.137461	0.052809	0.177024	0.107131
20090430	0.102885	0.065229	0.197013	0.122271
20090529	0.037512	0.035074	0.079313	-0.045303
20090630	0.137865	-0.017501	0.048745	0.127511
20090731	-0.010517	0.129381	0.147160	0.120310
20090831	0.053571	0.005681	0.029500	0.036721
20090930	0.043408	0.013215	0.101896	0.053464
20091030	0.078149	0.008361	0.016995	0.005295
20091130	0.065272	0.052152	0.060531	0.033713
20091231	0.036382	0.036011	0.054134	0.051569
20100129	-0.075459	-0.065011	-0.088591	-0.086197
20100226	0.022001	0.043468	0.065380	0.079031
20100331	0.021538	0.008572	0.148470	0.048041
20100430	0.042595	0.005848	0.111021	-0.022201
20100528	-0.150811	-0.023953	-0.016125	-0.114682
20100630	-0.108140	-0.014210	-0.020827	-0.057596
20100730	0.121686	0.039844	0.022741	0.063771
20100831	-0.085819	-0.035981	-0.055005	-0.164857
20100930	0.043682	0.089418	0.167215	0.096229
20101029	0.088812	0.070523	0.060723	-0.000713
20101130	-0.046784	-0.010376	0.033790	-0.002617
20101231	0.105018	0.037466	0.036670	0.005962
20110131	-0.006628	0.103843	0.051959	0.085273
20110228	-0.035528	0.003272	0.040935	-0.045086
20110331	-0.044771	0.007351	-0.013314	-0.059134
20110429	0.020874	0.046054	0.004656	-0.014645
20110531	-0.028935	-0.005276	-0.006569	-0.074065
20110630	0.039584	0.015509	-0.034960	-0.023007
20110729	0.053846	0.060041	0.163285	-0.033791
20110831	-0.023358	-0.050536	-0.014469	-0.259881
20110930	-0.064286	0.017218	-0.009121	-0.132924
20111031	0.069908	0.055813	0.061523	0.185301
20111130	-0.031919	0.022315	-0.055783	0.050357
20111230	0.014855	-0.021915	0.059655	-0.074061
20120131	0.137519	0.047422	0.127111	0.086180
20120229	0.081612	0.025337	0.188311	-0.095425
20120330	0.016226	0.060591	0.105284	-0.053734
20120430	-0.007441	-0.007525	-0.025969	0.039026
20120531	-0.081993	-0.064371	-0.010702	-0.084006
20120629	0.047962	0.013893	0.010853	-0.107496
20120731	-0.036613	0.002045	0.045822	-0.092989
20120831	0.052596	-0.001429	0.093539	-0.074561
20120928	-0.034393	0.064665	0.002803	0.018483
20121031	-0.040995	-0.062280	-0.107607	-0.188159
20121130	-0.059390	-0.018558	-0.012413	-0.062094
20121231	0.003558	0.007787	-0.090738	0.107159
20130131	0.027717	0.060141	-0.144094	0.158597
20130228	0.021129	-0.006845	-0.025116	0.219867
20130328	0.028957	0.062092	0.002855	0.190268
20130430	0.157140	-0.050445	0.000271	-0.135906
20130531	0.061329	0.031747	0.022596	0.185437
20130628	-0.010172	-0.081290	-0.118303	0.021507
20130731	-0.078304	0.020564	0.141225	0.035484
20130830	0.056219	-0.060603	0.083389	-0.130062
20130930	-0.003593	0.015965	-0.021481	-0.053930
20131031	0.063852	-0.032239	0.096386	0.161029
20131129	0.084875	0.007924	0.069673	0.122281
20131231	-0.018883	0.043911	0.008902	0.028344
20140131	0.011494	-0.058058	-0.107697	0.036455
20140228	0.019820	0.053430	0.057311	0.030345
20140331	0.069956	0.039531	0.019953	0.087858
20140430	-0.014394	0.020676	0.099396	0.021632
20140530	0.020297	-0.056039	0.078293	0.013309
20140630	0.018564	-0.016761	0.027662	0.010149
20140731	0.035012	0.057373	0.028731	0.057304
20140829	0.059082	0.009026	0.077092	0.067116
20140930	0.020471	-0.012845	-0.017073	-0.062368
20141031	0.012726	-0.133962	0.071960	0.011559
20141128	0.024920	-0.006873	0.105556	0.088629
20141231	-0.028446	-0.010668	-0.071891	0.031490
20150130	-0.130248	-0.044440	0.061424	-0.099676
20150227	0.093069	0.063466	0.100461	-0.035704
20150331	-0.072862	-0.008892	-0.031372	-0.101033
20150430	0.196409	0.067227	0.005786	0.058087
20150529	-0.030222	-0.001985	0.045146	0.013042
20150630	-0.057832	-0.041202	-0.037266	-0.096228
20150731	0.057758	-0.004119	-0.032888	0.016994
20150831	-0.061456	-0.079017	-0.066117	-0.080603
20150930	0.017004	-0.019744	-0.021816	-0.081041
20151030	0.189336	-0.033731	0.083409	0.052714
20151130	0.039324	0.004569	-0.005690	-0.047536
20151231	0.020791	-0.012911	-0.110228	-0.045933
20160129	-0.007030	-0.093228	-0.075242	-0.179899
20160229	-0.069886	0.060421	-0.001335	0.100927
20160331	0.085495	0.155842	0.127211	0.164079
20160429	-0.097049	-0.036382	-0.139921	-0.004058
20160531	0.069982	0.063040	0.071368	0.090465
20160630	-0.034528	-0.012749	-0.042660	-0.052765
20160729	0.107680	0.058242	0.090063	0.116335
20160831	0.020113	-0.002117	0.023606	0.025696
20160930	0.002436	-0.000189	0.065504	0.089353
20161031	0.040278	-0.032483	0.004334	-0.066967
20161130	0.012183	0.064611	-0.021578	0.062802
20161230	0.031198	0.023240	0.047955	-0.027747

Provide detailed solution on Excel for the following:

(a) Report the average return and sample covariance matrix of the four stocks based on the monthly data.

(b) If you put equal weights on each stock when forming a portfolio. What is the expected return and variance of the portfolio?

Expert Solution

Solution:

Part A ) The average return is 1.07%, 0.77%, 2.41% and 0.30% for Microsoft (MSFT), IBM (IBM), Apple (AAPL) and Hewlett-Packard (HPQ) respectively

The covariance matrix is given in excel sheet Part A

Part B ) Expected return = ?weight * return = 1.14%

Variance of the portfolio = 0.00154

jeff jeffy answered 2 years ago

Discuss Apple's (AAPL) and Microsoft (MSFT) beta and relate to the volatility of the stock and...

Discuss Apple's (AAPL) and Microsoft (MSFT) beta and relate to the volatility of the stock and its returns over a six month period.

I have Stock Return of two Companies MSFT(Microsoft) and APPL (Apple).It is mentioned in the given...

I have Stock Return of two Companies MSFT(Microsoft) and APPL (Apple).It is mentioned in the given Table.The Question is : ' ''We would also like to check whether both stocks (Apple and Microsoft) have the same return on average. Using the confidence interval approach, perform and present an appropriate hypothesis test at the 5% level of significance, and interpret your result. Which stock would you prefer and why? [Hint: Hypothesis test about two population means. Zero marks if you test...

The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and...

The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017. Ticker 6/30/2017 5/31/2017 4/30/2017 3/31/2017 2/28/2017 1/31/2017 ORCL 50.14 45.39 44.96 44.61 42.59 40.11 MSFT 68.93 69.84 68.46 65.86 63.98 64.65 NVDA 144.56 144.35 104.3 108.93 101.48 109.18 Create a formula to calculate the monthly rate of return during the first semester of 2017 and for each company. Format the results as percentages with two decimal...

1. The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and...

1. The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017. Ticker 6/30/2017 5/31/2017 4/30/2017 3/31/2017 2/28/2017 1/31/2017 ORCL 50.14 45.39 44.96 44.61 42.59 40.11 MSFT 68.93 69.84 68.46 65.86 63.98 64.65 NVDA 144.56 144.35 104.3 108.93 101.48 109.18 a) Enter the data, as shown, into a worksheet and format the table as shown. b) Create a formula to calculate the monthly rate of return during the first semester...

You estimate that the expected return of MSFT stock is 4%, and standard deviation of MSFT stock is 9%.The expected return of AAPL stock is 3%,and

You estimate that the expected return of MSFT stock is 4%, and standard deviation of MSFT stock is 9%.The expected return of AAPL stock is 3%,and standard deviation of MSFT stock is8%. If the correlation between AAP Lreturns and MSFT returns is 80%,what is the expected return and standard deviation of a portfolio with $4,000 invested in MSFT and $6,000 invested in AAPL?

Using CAPM, calculate the Expected Return for Apple and Microsoft. Get the relevant market related data,...

Using CAPM, calculate the Expected Return for Apple and Microsoft. Get the relevant market related data, such as the “risk-free rate” and “market return”, and describe which rate you are using from the financial markets Year Microsoft Apple Market Jan 2010 - Dec 2010 1 -0.34% 5.18% 14.93% Jan 2011 - Dec 2011 2 -0.37% 7.93% 2.06% Jan 2012 - Dec 2012 3 0.40% 5.15% 15.84% Jan 2013 - Dec 2013 4 2.81% 5.99% 32.21% Jan 2014 - Dec 2014...

Obtain monthly close prices of Microsoft (symbol: MSFT) adjusted for stock splits and dividends during December...

Obtain monthly close prices of Microsoft (symbol: MSFT) adjusted for stock splits and dividends during December 1989 – December 2019 from Yahoo Finance (finance.yahoo.com). Using Excel, compute monthly returns during January 1990 – December 2019. From these returns, compute arithmetic and geometric return averages over the entire period. Report your answers and confirm that arithmetic average is greater than geometric average. Guideline: 1. Yahoo Finance allows you to download the data in a spreadsheet. 2. At Yahoo Finance, go to...

The annual data on stock prices and dividends for Microsoft Corporation(MSFT) is as follows: Year Price...

The annual data on stock prices and dividends for Microsoft Corporation(MSFT) is as follows: Year Price (Beginning of the Year) Price (End of the Year) Dividends (End of the Year ) 2005 26.28 28.15 0.29 2006 28.15 30.86 0.33 2007 30.86 32.60 0.37 2008 32.60 17.10 0.41 2009 17.10 28.18 0.46 2010 28.18 27.73 0.55 2011 27.73 29.53 0.68 2012 29.53 27.45 0.83 2013 27.45 37.84 0.97 2014 37.84 46.45 1.15 What will be the IRR if the stock is...

Obtain monthly adjusted close prices of Microsoft (symbol: MSFT) and Exxon Mobil Corporation (symbol: XOM) during...

Obtain monthly adjusted close prices of Microsoft (symbol: MSFT) and Exxon Mobil Corporation (symbol: XOM) during December 1989 – December 2019 from Yahoo Finance. Using Excel, compute monthly returns of each company during January 1990 – December 2019. Also, compute and report the arithmetic average and the standard deviation of each company’s returns. Compute and report the correlation coefficient between these two stock returns. Next, form a portfolio that combines Microsoft and Exxon Mobil Corporation with equal weight. Compute monthly...

Obtain monthly adjusted close prices of S&P500 Index (symbol: ^GSPC) and Microsoft (symbol: MSFT) during December...

Obtain monthly adjusted close prices of S&P500 Index (symbol: ^GSPC) and Microsoft (symbol: MSFT) during December 2006 – December 2019 from Yahoo Finance. Then, using Excel, compute monthly returns during January 2007 – December 2019. From these returns, compute and report beta of Microsoft using the formula we learned in class. Suppose today’s Treasury bill rate is 2% per annum. Historically, market risk premium is estimated to be 8% per annum. What is today’s expected return per annum of Microsoft,...

Question