Question

Please help I have no idea how to do this question.

Question #3 (40 points) Download monthly adjusted close price data for Apple and Google (Alphabet Inc.). for the period of Jan. 2010 through Jan. 2020 (use Finance Yahoo.com). Use Excel for this question. Stock returns are computed 1. Assume that you are constructing portfolios with Apple and Google such that WA+WG = 1. Create a column in Excel and apply a range of weights between 1 and +1 (with step size of 0.1) to Apple (WA) to construct a portfolio consisting of the two shares (remember that the sum of weights should be one, so you can easily calculate the weight for Google once you know the weight for Apple. Don't worry if it gets larger than 1!). Plot a scatter-plot graph showing average return and the stan- dard deviation of the portfolio under alternative values of 2. Now apply a constraint on weights: eliminate negative values of weights and change the weight only between 0 and 1 (with step size of 0.05) and re-plot the scatter plot of average return and variance of portfolio 3. Eliminating negative weights is called short sales constraints. Compare the results of parts (1) and (2) and discuss the effects of imposing short-sales constraints on a market. 4. Use the SP500 index as a proxy for the market portfolio. Create a returns scatterplot (the way we did in the class) and calculate Apple's beta. (Hint: you can either use the "SLOPE" function in excel or apply the procedures described in Question #2).

Download monthly adjusted close price data for Apple and Google (Alphabet Inc.). for the period of Jan.

2010 through Jan. 2020 (use Finance.Yahoo.com). Use Excel for this question. Stock returns are computed

as

rt+1 =

Pt+1 − Pt

Pt

1. Assume that you are constructing portfolios with Apple and Google such that

wA + wG = 1.

Create a column in Excel and apply a range of weights between -1 and +1 (with step size of 0.1) to

Apple (wA) to construct a portfolio consisting of the two shares (remember that the sum of weights

should be one; so you can easily calculate the weight for Google once you know the weight for Apple.

Don’t worry if it gets larger than 1!). Plot a scatter-plot graph showing average return and the stan-

dard deviation of the portfolio under alternative values of wA.

2. Now apply a constraint on weights: eliminate negative values of weights and change the weight only

between 0 and 1 (with step size of 0.05) and re-plot the scatter plot of average return and variance of

portfolio.

3. Eliminating negative weights is called short sales constraints. Compare the results of parts (1) and (2)

and discuss the effects of imposing short-sales constraints on a market.

4. Use the SP500 index as a proxy for the market portfolio. Create a returns scatterplot (the way we did

in the class) and calculate Apple’s beta.

Answer 1

As instructed, MS Excel has been used for this question.

Yahoo Finance was used to find data on the historical prices of Apple and Google, as on the Nasdaq Stock Exchange. The Adjusted Closing Prices of the months from January 2010 to January 2020 have been used. This data was pasted on Excel and the returns for each period was calculated using the given formula. Additionally, the average returns, the standard deviation of returns and the variance of returns were calculated using the =average function, the =stdev.p function and the =var.p function respectively. The following is the table output: (TABLE was deleted as the answer length was more than 65,000 characters. I have kept relevant data.)

Apple
Average returns		0.02423878
Standard Dev		0.07315595
Variance		0.00535179

Google
Average		0.01629237
Standard Dev		0.06570118
Variance		0.00431665

For Part 1 of the question, weights were assigned to the companies. These are percentages of the portfolio assigned to the companies.

WA	WG	Sum of Weights	Average Return	Standard Deviation
-1	2	1	0.008345963	0.058246415
-0.9	1.9	1	0.009140604	0.058991891
-0.8	1.8	1	0.009935245	0.059737368
-0.7	1.7	1	0.010729886	0.060482845
-0.6	1.6	1	0.011524527	0.061228321
-0.5	1.5	1	0.012319168	0.061973798
-0.4	1.4	1	0.013113809	0.062719275
-0.3	1.3	1	0.01390845	0.063464751
-0.2	1.2	1	0.014703091	0.064210228
-0.1	1.1	1	0.015497732	0.064955705
0	1	1	0.016292373	0.065701181
0.1	0.9	1	0.017087013	0.066446658
0.2	0.8	1	0.017881654	0.067192135
0.3	0.7	1	0.018676295	0.067937612
0.4	0.6	1	0.019470936	0.068683088
0.5	0.5	1	0.020265577	0.069428565
0.6	0.4	1	0.021060218	0.070174042
0.7	0.3	1	0.021854859	0.070919518
0.8	0.2	1	0.0226495	0.071664995
0.9	0.1	1	0.023444141	0.072410472
1	0	1	0.024238782	0.073155948
1.1	-0.1	1	0.025033423	0.073901425
1.2	-0.2	1	0.025828064	0.074646902
1.3	-0.3	1	0.026622705	0.075392378
1.4	-0.4	1	0.027417346	0.076137855
1.5	-0.5	1	0.028211987	0.076883332
1.6	-0.6	1	0.029006628	0.077628809
1.7	-0.7	1	0.029801269	0.078374285
1.8	-0.8	1	0.03059591	0.079119762
1.9	-0.9	1	0.031390551	0.079865239
2	-1	1	0.032185191	0.080610715

The following scatter plot shows the average returns and standard deviation of the portfolio when short selling is used. The X-axis contains the weights of Apple in the portfolio and the Y-axis shows the average returns and the standard deviations.

Scatter Plot when Short Selling is allowed 0.09 D.DE 0.02 0.01 -0.5 1. 52 • Average Return Standard Deviation

It can be seen that the average returns and standard deviations have a linear relationship with the weight in the portfolio.

Part 2 of the question: The following table has the average returns and variance of the portfolio when no short selling is allowed:

No Short Selling
WA	WG	Sum of Weights	Average Return	Variance
0	1	1	0.016292373	0.004316645
0.05	0.95	1	0.016689693	0.004368403
0.1	0.9	1	0.017087013	0.00442016
0.15	0.85	1	0.017484334	0.004471917
0.2	0.8	1	0.017881654	0.004523675
0.25	0.75	1	0.018278975	0.004575432
0.3	0.7	1	0.018676295	0.00462719
0.35	0.65	1	0.019073616	0.004678947
0.4	0.6	1	0.019470936	0.004730704
0.45	0.55	1	0.019868257	0.004782462
0.5	0.5	1	0.020265577	0.004834219
0.55	0.45	1	0.020662898	0.004885976
0.6	0.4	1	0.021060218	0.004937734
0.65	0.35	1	0.021457539	0.004989491
0.7	0.3	1	0.021854859	0.005041249
0.75	0.25	1	0.02225218	0.005093006
0.8	0.2	1	0.0226495	0.005144763
0.85	0.15	1	0.023046821	0.005196521
0.9	0.1	1	0.023444141	0.005248278
0.95	0.05	1	0.023841462	0.005300035
1	0	1	0.024238782	0.005351793

The following is the scatter plot, with percentages of returns and variances on the Y-axis and weights of apple on the X-axis:

Scatter Plot of average portfolio returns and variance, without short selling 0.03 0.02 0.015 0.01 06 Average Return Variance

For Part 3 of the question:

There are several points of difference between the situation when short selling is allowed to when it is not allowed. As can be seen from both the scatter plots, there is a positive linear relationship between weights and returns. But, when short selling is allowed, the maximum average return of the portfolio is a little over 3%, whereas it is just below 2.5% when short selling is not allowed. Hence, short selling allows returns to only marginally increase. The significant difference between the 2 situations is that the curve of the variance of the returns has become flatter. This shows that the return adjusted for the risk has also increased when short selling is not allowed. Hence, the risk-adjusted returns rise when short selling is not allowed.

For part 4 of the question:

Beta is a measure of the volatility of the stock with respect to the market. It is calculated using historical data of the average returns of the stock and the average returns of the market portfolio, which is taken to be the S&P 500.

Correlation

0.504901135

The correlation of the market returns with that of apple is calculated using the =correl function on Excel. The standard deviation of returns is also computed. They come out to be:

	Apple	Market
Standard Deviation	0.073155948	0.035611626

Beta = (Standard deviation of apple/ standard deviation of the market)* Correlation between apple and market returns
= (0.073155948/0.035611626)*0.504901135
=

1.037204015

Apple beta is > 1. Apple can be said to have a relative risk a little bit higher than the market.