Question

MINICASE 1

You need to show your working notes in excel for credit. You must submit your work using excel files (with .xls or .xlsx for credit). This assignment will require you to analyze time series of monthly returns.

MONTHLY data for the period of 01/01/2015 – 08/31/2018

− S&P 500 Index (ticker: ^GSPC)

− General Electric Company (ticker: GE)

− Intel Corporation (ticker: INTC)

− Chevron Corporation (ticker: CVX)

− Apple Inc. (ticker: AAPL)

S&P 500 GE Intel 1/1/15 1994.9900 20.7484 29.455786.9578 109.2661 2/1/15 2104.5000 22.5723 29.6429 90.4775 119.8047 3/1/15 2067.8899 21.745 4/1/15 2085.5100 23.7351 29.22709 5 28.0777 89.9105 116.5042 5.1177 117.1784 23.9017 30.942088.2147 121.9816 5/1/15 2107.3899 6/1/15 2063.1101 23.2881 27.5120 83.4453 117.9311 7/1/15 2103.8401 23.070826.1825 76.5341 114.0480 8/1/15 1972.1801 22.107325.8117 70.0553 106.1690 9/1/15 1920.0300 22.2929 27.4852 69.0899 104.1750 10/1/15 2079.3601 25.792330.8775 79.6005 112.8642 11/1/15 2080.4099 26.701931.7074 79.9859 111.7308 12/1/15 2043.9399 27.781131.6369 79.7571 99.8405 1/1/16 1940.2400 26.1469 28.4870 76.6629 92.3282 2/1/16 1932.2300 26.182 3/1/16 2059.7400 28.7926 29.9698 85.6529 103.9394 4/1/16 2065.300027.8507 28.0521 91.7402 89.3961 5/1/16 2096.950027.3797 29.2658 90.6807 95.2325 6/1/16 2098.8601 28 7/1/16 2173.6001 8/1/16 2170.950028.5088 33.5366 91.2582 101.7994 9/1/16 2168.2700 27.0304 35.542094. 10/1/16 2126.1499 26.763232.8305 96.0432109.5279 11/1/16 2198.8101 28.2899 32.6704 102.2871 106.6146 12/1/16 2238,830129.0624 34.4071108.9971 112.3010 1/1/17 2278.8701 27.5205 34.9289 103.1167 117.6630 2/1/17 2363.6399 27.6225 34.3407 104.1816 132.8278 3/1/17 2362.720027.8334 34.4617 100.3827 139.8990 4/1/17 2384.2000 2 9 27.173773.9765 91.7117 28.5118 30.6492 95.114391.72 28.4176 32.574192.9821 99.9860 3653 109.0552 27.0769 34.5381 99.7563 139.8893 /1/17 2411.8000 25.5731 34.499996.7458148.7 6/1/17 2423.4099 25.2276 32.4749 98.5327 140.8285 7/1/17 2470.3000 24.3235 34.1401 103.1227 145.4341 8/1/17 2471.6499 23.3167 33.7551 101.6399 160.3658 22.9653 36.9268 112,0859 151.2965 4.1124 110.5501 165.9432 11/1/17 2584.8401 17.544843.4821113.5073168.7017 9/1/17 2519.3601 10/1/17 2575.2600 19.3386 4 12/1/17 2673.610116.73904.0269 120.5395166.72 1/1/18 2823.8101 15.724546.9583120.6936164.9535 2/1/18 2713.8301 13.7212 48.0801 107.7624 175.4854 3/1/18 2640.8701 13.2171 51.1463 110.8968 165.9723 /1/18 2648.050013.7956 50.6945121.6616163.4794 5/1/18 2705.270013.8054 54.2103 120.8740 184.8 6/1/18 2718.3701 13.3445 49.1005 124.0173 183.8217 7/1/18 2816.2900 13.4828 47.5103 123.860418 8/1/18 2901.5200 12.8002 47.8362 1 226.0458 9/1/18 2913.980011.168046.9943 121.0965224.9551 10/1/182711.7400 10.0861 46.5868 110.5694 218.0990 11/1/18| 2760.1699 12/1/18 2506.8501 7.559646.9300 108.7900 157.7400 8 116.1994 7.4897 49.0016 117.7888 177.9591

Calculating Returns:

Use the ‘Adj Close’ column to obtain returns for each period. Remember that the Adjusted Close column has already adjusted the prices for dividends and stock splits so you do not have to adjust for it again. Just use the adjusted close column to obtain the return R for each month t as: Rt = Adj Closet/Adj Closet-1 – 1

Solve for the following:

A. What is the average monthly return and standard deviation of returns for the 01/01/2015 – 8/31/2018 period for

(i) S&P 500

(ii) GE

(iii) Intel

(iv) Chevron

(v) Apple

(vi) Comment on your findings

B. Calculate the correlation between:

(i) GE and Intel

(ii) GE and Chevron

(iii) GE and Apple

(iv) Intel and Chevron

(v) Intel and Apple

(vi) Chevron and Apple

(vii) Comment on your findings

C. Calculate the stock betas for the 01/01/2015 – 8/31/2018 period for:

(i) GE

(ii) Intel

(iii) Chevron

(iv) Apple

(v) Comment on your findings.

D. If you were to form a portfolio that was equally invested in GE, Intel, and Chevron, what would be the average return and the standard deviation of returns for your portfolio?

E. If you were to add Apple to your portfolio so that the portfolio was equally invested in GE, Intel, Chevron, and Apple, what would be the new average return and standard deviation of returns for your portfolio? Is Apple a good addition to your portfolio? Why do you think so?

Answer 1

To begin with, use the above data to calculate the monthly returns as follows -

As mentioned in the question, the returns are calculated using the formula -

(Adj. Close at time t/ Adj. Close at time (t-1)) -1

For example, return of S&P 500 for the 1 month period from 1/1/15 to 2/1/15 is calculated as -

(2104.5000/1994.9900) -1 = 5.4893%

Using this formula in excel and the fill handle, we get the following result -

(A)

We need to find the average monthly returns and standard deviation for 5 Index/Companies.

For this, we shall simply take the average of the monthly return of that respective company. (Since the returns are already in monthly terms)

Use the AVERAGE function in Excel and select the array for that specific company. (Remember, take the average of the returns not price)

Similarly, to find the Standard deviation use the STDEV function on Excel and select the array of returns for that specific company.

You shall get the following result -

(vi) Comment on the result

• As seen above, GE is the only company to have an average negative return through this time period. (-0.954%)
• Also, Apple seems to generate the highest average monthly return (1.937%)
• Even though Apple has generated the highest average monthly return, it also has the highest standard deviation, with a high 6.97% deviation.
• The S&P 500 has the lowest standard deviation among the sample, at 2.9%

(B)

For the next question, we shall find the Correlation using the CORREL function on Excel.

For example, if we want to find the correlation between

GE & Intel, we will use the =CORREL(Array of returns of GE, Array of returns of Intel)

You should get the following result due to this -

(vii) Comment on the findings

• None of the companies seem to have a high correlation with one another.
• However, the lowest positive correlation is observed between GE and Apple, providing an indication for diversification, if they are take together in a portfolio.
• Also, a negative correlation between Chevron and Apple is an indication that together, the 2 companies in a portfolio shall reduce the risk (standard deviation) as they are negatively correlated. A negative correlation indicates that a change in one company share (let's say Chevron) would need to a change in the opposite direction for the other company (i.e. Apple).

(C)

Stock Beta's are calculated using returns of the market index, in this case the S&P 500.

Beta of security i = Covar (Ri,Rm) / Var (Rm)

Thus, we shall first find The Covariance (Covar) of the 4 securities with the market (S&P 500) and the Variance of the market.

After finding that, Beta of the security can be simply found by dividing the Covariance of the security and market with the market variance.

(v) Comment on findings -

• Chevron is the only Company that has a Beta close to 1 and can be considered to be moving very closely with market, i.e., S&P 500 Index.
• Apple seems to have a higher comparative beta, which shows a relatively higher change in expected share price of Apple, when the market changes.
• GE and Intel have beta's close to each other, which are lower than 1, thus showing a relatively lower change in expected share price, with respect to any changes in the market.

(D)

Expected return of the Portfolio can be calculated using the weights (equal, i.e. 25%) in this case and multiplying that with the returns of the stocks constituting the portfolio.

This results in an Expected portfolio return of 0.40%on a monthly basis.

Portfolio Standard deviation can be calculated using the following formula -

Taking square root of the above equation shall result in calculating the portfolio standard deviation.

This gives results in a portfolio standard deviation of 0.19% on a monthly basis.

(E)

Using the same logic as above, the Expected portfolio return shall be 0.78% on a monthly basis.

Logically speaking, adding Apple to our portfolio of stocks has increased our expected monthly returns, and as it also has a negative correlation with one of the stocks, which would reduce the risk (standard deviation) of the portfolio as a whole. Thus, Apple is a good addition to the portfolio.