Optimal Stock Portfolio Calculation

Subject:	Finance
Pages:	3
Words:	575
Reading time:	< 1 min

Ten stocks that have been chosen for the analysis are AAPL, SBUX, MSFT, FB, AMZN, TSLA, SHSAX, GGHCX, FHEAX, and ABT.

a. The optimal portfolio was calculated using the “Solver” function in Excel. Only one constraint was added, which is that the sum of the weights should be equal to one (see Table 1). The optimal portfolio weights are as follows: -1,8706 for APPL, -1,9302 for SBUX, 0,8503 for MSFT, 1,2108 for FB, -0,4883 for TSLA, -1,03 for AMZN, 2,1672 for SHSAX, 8,3686 for GGHCX, -6,4100 for FHEAX, and -0,1340 for ABT. It can be seen that the sum of all weights is equal to one. The risk-free rate is equal to 0,00078, which is the average of the T-bill rate over the past five years.

	AAPL	SBUX	MSFT	FB	TSLA	AMZN	SHSAX	GGHCX	FHEAX	ABT
Weights	-1,8706	-1,9302	0,8503	1,2108	-0,4883	-1,0318	2,1672	8,3686	-6,4100	0,1340
-1,8706	0,0199	0,00197	-0,0033	-0,005	0,0016817	0,0046	-0,006	-0,028	0,0113	-0,0004	AAPL
-1,9302	0,0020	0,0102	-0,0014	-0,0001	-0,0008	0,0017	-0,0029	-0,0114	0,0059	-0,0002	SBUX
0,8503	-0,003	-0,0014	0,0026	0,0015	-0,000362	-0,002	0,0016	0,0077	-0,003	0,00017	MSFT
1,2108	-0,005	-8E-05	0,0015	0,0063	-0,000598	-0,003	0,0024	0,0105	-0,006	0,0001	FB
-0,4883	0,0017	-0,0008	-0,0004	-6E-04	0,0031995	0,0006	-0,001	-0,005	3E-05	-9E-05	TSLA
-1,0318	0,0046	0,00174	-0,0022	-0,003	0,0005737	0,0067	-0,005	-0,022	0,0083	-0,0003	AMZN
2,1672	-0,006	-0,0029	0,0016	0,0024	-0,001311	-0,005	0,0115	0,0498	-0,017	0,00054	SHSAX
8,3686	-0,028	3,8E-05	4E-08	4E-11	-2,58E-14	7E-17	4E-19	1E-20	-1E-22	-3E-26	GGHCX
-6,4100	0,0113	0,00588	-0,0026	-0,006	3,416E-05	0,0083	-0,017	-0,074	0,073	-0,0008	FHEAX
0,1340	-4E-04	-0,0002	0,0002	0,0001	-9,26E-05	-3E-04	0,0005	0,0023	-8E-04	5,8E-05	ABT
1,0000	-0,003	0,01445	-0,004	-0,005	0,0023393	0,0108	-0,017	-0,07	0,0713	-0,0009

Table 1. Bordered Covariance Matrix for Target Returns

b. The minimum variance frontier is presented below. It does not look fine because some stock weights were negative, and some stock weights were much greater than 1. As a result, the calculated minimum variances were too low and even negative, so standard deviations could not have been calculated.

The minimum variance frontier graph for unreasonable weights

c. Portfolio weights do not seem reasonable as some of them are negative, and some of them are greater than 1. That is why this portfolio cannot be recommended to a client. Stock weights need to be recalculated by putting more constraints on their values.