Microsoft Word - finc_3330_final_project.docxFINC 3330: Final Assignment Due: May 22, 2020, 11:59...

Question

Microsoft Word - finc_3330_final_project.docx
FINC 3330: Final Assignment
Due: May 22, 2020, 11:59 P.M
Note: Use Blackboard to submit this project (as we have been doing).
DO NOT send me it via email. Please submit your project in a single EXCEL file. ONLY an EXCEL FILE will
e accepted.
Use Slides for chapters 6 and 7 to assist your work.
Do your best!
Part 1: Portfolio Return and Risk
Compute the portfolio returns and risk measures of a portfolio you create.
You can pick any two companies to download stock data for (daily or monthly). For the data you will
need to attain about 50 observations (prices and returns for each stock).
Task 1: Compute the respective average, standard deviation, and covariance of monthly or daily stock
eturns.
Covariance table will be in the form:
Var(stock1, stock1) Cov(stock1, stock2)
Cov(stock1, stock2) Var(stock2, stock2)
Note: Use STDEV.P in Excel for the standard deviation
Task 2: Using the obtained statistics fromQ1, calculate an equal weighted portfolio return and portfolio
variance for the first portfolio using the below equations:
Equal weighted portfolio return:
E(RP) = w1(avg(r1)) + w2(avg(r2));
where w is the weight of each stock in the portfolio. And avg(r1) is the mean return for stock 1 and
avg(r2) is the mean return for stock 2.
Portfolio variance:
σ2p = w12( σ2s1) + w22( σ2s2) + 2*w1 w2 * σs1 σs2
Task 3: Select two different stocks and repeat task 1 and 2
Again: you can pick any two companies (except the ones you used for part 1) to download stock data for
(daily or monthly). For the data you will need to attain about 50 observations (prices and returns for
each stock).
Task 4:
Now using a matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and portfolio
variances.
Use the formula: portfolio return: E(RP) = w * rT
XXXXXXXXXXportfolio variance: σP2 =w·Σ·wT
Task 5:
With either portfolio, create a table that shows the benefit of diversification using Data Table in Excel.
(Note that the table shows portfolio returns and portfolio standard deviation with respect to scenarios
of weights on one of the stocks – of your choosing – from the portfolio)
Task 6:
Using the table obtained from task 5, Plot expected returns against portfolio risk (standard deviations)
displaying efficient portfolios.
Task 7:
Using the first portfolio, find out optimal weights that minimizes the portfolio standard deviation
(Minimum Variance Portfolio).
Use the formula (from Slides 6 and 7): σP2 =w*Σ*wT subject to w*rT =E(RP)

finc3330finalproject-j2wwclkq.pdf

Tanmoy · Accepted Answer

Sheet1
			Coca Cola	Pepsi
		Date	KO Close	PEP Close	KO Returns	PEP Returns	Portfolio Return		KO	PEP	Mean	Variance	Standard Deviation
		12/2/19	51.971497	131.113968					0%	100%	0.06%	0.00053223	2.31%
		12/3/19	52.01984	130.708694	0.09%	-0.31%	-0.11%		2%	98%	0.06%	0.0005113429	2.26%
		12/4/19	52.493633	132.484177	0.91%	1.36%	1.13%		4%	96%	0.06%	0.00049126	2.22%
		12/5/19	52.396938	132.479294	-0.18%	-0.00%	-0.09%		6%	94%	0.06%	0.0004719814	2.17%
		12/6/19	52.619328	133.470413	0.42%	0.75%	0.59%		8%	92%	0.06%	0.0004535069	2.13%
		12/9/19	52.280907	133.276077	-0.64%	-0.15%	-0.39%		10%	90%	0.06%	0.0004358366	2.09%
		12/10/19	51.990837	132.411285	-0.55%	-0.65%	-0.60%		12%	88%	0.06%	0.0004189706	2.05%
		12/11/19	52.164883	133.217789	0.33%	0.61%	0.47%		14%	86%	0.06%	0.0004029087	2.01%
		12/12/19	52.348595	133.295502	0.35%	0.06%	0.21%		16%	84%	0.06%	0.0003876511	1.97%
		12/13/19	52.619328	134.033997	0.52%	0.55%	0.54%		18%	82%	0.06%	0.0003731977	1.93%
		12/16/19	52.619328	133.178894	0.00%	-0.64%	-0.32%		20%	80%	0.06%	0.0003595485	1.90%
		12/17/19	52.619328	132.314102	0.00%	-0.65%	-0.32%		22%	78%	0.05%	0.0003467035	1.86%
		12/18/19	52.126205	132.119766	-0.94%	-0.15%	-0.54%		24%	76%	0.05%	0.0003346627	1.83%
		12/19/19	52.522636	132.605606	0.76%	0.37%	0.56%		26%	74%	0.05%	0.0003234261	1.80%
		12/20/19	53.151134	134.014557	1.20%	1.06%	1.13%		28%	72%	0.05%	0.0003129937	1.77%
		12/23/19	53.093117	133.15947	-0.11%	-0.64%	-0.37%		30%	70%	0.05%	0.0003033655	1.74%
		12/24/19	52.899734	133.013718	-0.36%	-0.11%	-0.24%		32%	68%	0.05%	0.0002945415	1.72%
		12/26/19	53.199478	132.819397	0.57%	-0.15%	0.21%		34%	66%	0.05%	0.0002865218	1.69%
		12/27/19	53.518559	133.645325	0.60%	0.62%	0.61%		36%	64%	0.05%	0.0002793062	1.67%
		12/30/19	53.441204	132.935989	-0.14%	-0.53%	-0.34%		38%	62%	0.05%	0.0002728949	1.65%
		12/31/19	53.518559	132.799957	0.14%	-0.10%	0.02%		40%	60%	0.05%	0.0002672878	1.63%	Results
		1/2/20	53.170471	131.974045	-0.65%	-0.62%	-0.64%		42%	58%	0.05%	0.0002624848	1.62%
		1/3/20	52.880394	131.789413	-0.55%	-0.14%	-0.34%		44%	56%	0.05%	0.0002584861	1.61%	As per the minimum variance portfolio the optimal weight that minimizes the portfolio standard deviation (risk) and maximizes the return is at 0.06% where the standard deviation is 2.17%.                     At the same point of standard deviation (risk) of 2.17%, the minimum return that can be achieved is 0.03%. Hence,  the minimum variance of the portfolio is achieved at SD 2.17% with a return of 0.06%.            In this case, we can also observe that the maximum return of 0.06% can be achieved only when we invest 6% of our investment in Coca Cola (KO) and 94% in Pepsi (PEP). 
		1/6/20	52.861053	132.294678	-0.04%	0.38%	0.17%		46%	54%	0.05%	0.0002552916	1.60%
		1/7/20	52.45496	130.215286	-0.77%	-1.57%	-1.17%		48%	52%	0.05%	0.0002529013	1.59%
		1/8/20	52.551643	130.885742	0.18%	0.51%	0.35%		50%	50%	0.04%	0.0002513152	1.59%
		1/9/20	53.508888	130.97319	1.82%	0.07%	0.94%		52%	48%	0.04%	0.0002505333	1.58%
		1/10/20	53.6926	130.720535	0.34%	-0.19%	0.08%		54%	46%	0.04%	0.0002505557	1.58%
		1/13/20	54.272751	132.66391	1.08%	1.49%	1.28%		56%	44%	0.04%	0.0002513822	1.59%
		1/14/20	54.147049	132.974869	-0.23%	0.23%	0.00%		58%	42%	0.04%	0.0002530129	1.59%
		1/15/20	54.823887	135.258316	1.25%	1.72%	1.48%		60%	40%	0.04%	0.0002554479	1.60%
		1/16/20	54.939919	135.656693	0.21%	0.29%	0.25%		62%	38%	0.04%	0.0002586871	1.61%
		1/17/20	55.055946	137.259964	0.21%	1.18%	0.70%		64%	36%	0.04%	0.0002627304	1.62%
		1/21/20	55.278336	137.843002	0.40%	0.42%	0.41%		66%	34%	0.04%	0.000267578	1.64%
		1/22/20	55.703781	139.319946	0.77%	1.07%	0.92%		68%	32%	0.04%	0.0002732298	1.65%
		1/23/20	55.916504	139.582306	0.38%	0.19%	0.29%		70%	30%	0.04%	0.0002796858	1.67%
		1/24/20	55.771458	138.872986	-0.26%	-0.51%	-0.38%		72%	28%	0.04%	0.000286946	1.69%
		1/27/20	55.578079	138.115067	-0.35%	-0.55%	-0.45%		74%	26%	0.04%	0.0002950104	1.72%
		1/28/20	55.123631	138.40657	-0.82%	0.21%	-0.30%		76%	24%	0.04%	0.000303879	1.74%
		1/29/20	55.123631	137.71666	0.00%	-0.50%	-0.25%		78%	22%	0.03%	0.0003135518	1.77%
		1/30/20	56.912415	139.757202	3.25%	1.48%	2.36%		80%	20%	0.03%	0.0003240289	1.80%
		1/31/20	56.46764	137.998474	-0.78%	-1.26%	-1.02%		82%	18%	0.03%	0.0003353101	1.83%
		2/3/20	56.641682	138.474579	0.31%	0.35%	0.33%		84%	16%	0.03%	0.0003473956	1.86%
		2/4/20	56.893078	139.16449	0.44%	0.50%	0.47%		86%	14%	0.03%	0.0003602852	1.90%
		2/5/20	56.902748	140.213898	0.02%	0.75%	0.39%		88%	12%	0.03%	0.0003739791	1.93%
		2/6/20	57.03812	140.243057	0.24%	0.02%	0.13%		90%	10%	0.03%	0.0003884772	1.97%
		2/7/20	57.231495	141.253601	0.34%	0.72%	0.53%		92%	8%	0.03%	0.0004037795	2.01%
		2/10/20	57.772968	141.535385	0.95%	0.20%	0.57%		94%	6%	0.03%	0.0004198859	2.05%
		2/11/20	57.618263	141.943497	-0.27%	0.29%	0.01%		96%	4%	0.03%	0.0004367967	2.09%
		2/12/20	57.444218	141.943497	-0.30%	0.00%	-0.15%		98%	2%	0.03%	0.0004545116	2.13%
		2/13/20	57.6376	142.322449	0.34%	0.27%	0.30%		100%	0%	0.03%	0.0004730307	2.17%
		2/14/20	57.966354	142.827744	0.57%	0.36%	0.46%
		2/18/20	57.560249	141.477097	-0.70%	-0.95%	-0.82%
		2/19/20	57.792309	141.409042	0.40%	-0.05%	0.18%
		2/20/20	57.743965	141.049545	-0.08%	-0.25%	-0.17%
		2/21/20	58.1404	141.720016	0.69%	0.48%	0.58%
		2/24/20	56.70937	138.600906	-2.46%	-2.20%	-2.33%
		2/25/20	55.906826	137.104523	-1.42%	-1.08%	-1.25%
		2/26/20	55.694111	137.959579	-0.38%	0.62%	0.12%
		2/27/20	53.112457	132.508438	-4.64%	-3.

Microsoft Word - finc_3330_final_project.docx FINC 3330: Final Assignment Due: May 22, 2020, 11:59 P.M Note: Use Blackboard to submit this project (as we have been doing). DO NOT send me it via email....

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment