Portfolio ConstructionFinal Project FN 315 – Summer 2021 Due Date: Friday 2nd July Portfolio...

Question

Portfolio ConstructionFinal Project  FN 315 – Summer 2021 Due Date: Friday 2nd July  Portfolio Construction  The Case My wife and I are 60 years old and plan to retire in 5 years. At present, we have assets of $750,000 in stocks and bonds and retirement savings of $600,000.  We own our house and car and have no liabilities.  The clients are looking to grow their retirement savings by assuming risk more significant than the market β. However, they want to take a more conservative position on their current cash and fixed income portfolio.  My wife and I would like you to construct two new portfolios meeting our risk profiles.  Assignment · Construct the two portfolios per the clients' request showing the β of each. There are no more than ten stocks/ Bonds in each portfolio but must have four stocks and one bond. · Explain to the client how the CAL works and how the client can increase and decrease risk and the CAL. · Explain how the portfolio compares to the SML showing the Sharpe ratio. Of each portfolio. · Based on five years of daily prices (15th June 2016 to 15th June 2021), calculate the ten-day 99% Var for each portfolio and explain the results to the client Please use PowerPoint to explain your results and an excel spreadsheet to show me your workings.  Note that there is no right or wrong portfolio; it's how you make the case.   Sheet1 			Stock	Price	% of portfolio	Beta	Portfolio B 			IBM	$   145	10%	1.23	2.70		100% 			OSTK	$   94	50%	4.46 			C	$   80	10%	1.92 			KHC	$   44	10%	1.10 					20%	0.67 			Stock	Price	% of portfolio	Beta	Portfolio B 			IBM	$   122.00	30%	1.23	1.20 			WMT	$   145.83	4%	0.48 			AAPL	$   136.01	51%	1.27 			KHC	$   34.00	15%	1.10   Example 			Stock	Price	% of portfolio	Beta	Portfolio B 			IBM	$   145	10%	1.23	2.70		100% 			OSTK	$   94	50%	4.46 			C	$   80	10%	1.92 			KHC	$   44	10%	1.10 					20%	0.67 			Stock	Price	% of portfolio	Beta	Portfolio B 			IBM	$   122.00	30%	1.23	1.20 			WMT	$   145.83	4%	0.48 			AAPL	$   136.01	51%	1.27 			KHC	$   34.00	15%	1.10 Homework 		Stock 	Price	Beta (5yr)	Dividend Yield 	OYTR	Portfolio Weight	Market Risk Premium		Portfolio Beta  		Dollar Gereral	$206.76	0.51	0.82%	0.06%	10%	7.70%		1.211 		Lowe's Inc	$190.30	1.19	1.69%		20% 		NVIDIA 	$703.67	1.58	0.09%		30% 		Visa	$230.50	1.12	0.56%		40% 		Question 1 			Single-index return 		Dollar Gereral	3.96%			Porfolio Return: 		Lowe's Inc	9.15%			9.31% 		NVIDIA 	12.13% 		Visa	8.62% 		Question 2 			Price	Return 		Dollar Gereral	$206.76	$8.18		Portfolio Return 		Lowe's Inc	$190.30	$17.42		37.85 		NVIDIA 	$703.67	$85.36 		Visa	$230.50	$19.86 		Question 3	Risk adjusted return 		m2= Rp-Rm 						Risk Adjusted Return 		Dollar Gereral	-3.74%			1.61% 		Lowe's Inc	1.45% 		NVIDIA 	4.43% 		Visa	0.92% 		Question 4 		New Portfolios 		Stock 	Price	Beta (5yr)	Dividend Yield 	OYTR	Portfolio Weight	Market Risk Premium		Portfolio Beta  		Dollar Gereral	$206.76	0.51	0.82%	0.06%	10%	7.70%		1.211 		Lowe's Inc	$190.30	1.19	1.69%		20% 		NVIDIA 	$703.67	1.58	0.09%		30% 		Visa	$230.50	1.12	0.56%		40% 		Stock 	New Percents	Beta		Stock 	New Percents	Beta  		Dollar Gereral	29%	1.011		Dollar Gereral	7%	1.411 		Lowe's Inc	30%			Lowe's Inc	15% 		NVIDIA 	10%			NVIDIA 	68% 		Visa	31%			Visa	10% 			1.010	Calculated			1.401	Calculated   Sheet1 	Question 1: Using the annual return data provided in Exhibit 1 of the case for Lyxor ChinaH and Lyxor MSIndia, calculate their mean returns, standard deviations, covariance and correlation. With these numbers, compute the standard deviation and return for Susie’s entire portfolio. 		Lyxor ChinaH	Square of Deviation From the Mean 	2009	2.00%	0.0005036818 	2010	4.25%	0.0020198604 	2011	-29.40%	0.0850055676 	2012	13.23%	0.0181556376 	2013	8.86%	0.0082888018 	2014	2.31%	0.0006524376 	2015	-2.96%	0.0007375104 	Mean	-0.24% 	Variance		0.0192272495 	Standard Deviation		13.866% 		Lyxor MSIndia	Square of Deviation From the Mean 	2009	5.86%	0.0009416131 	2010	22.40%	0.0384496073 	2011	-27.07%	0.0891704916 	2012	0.60%	0.0004802359 	2013	-6.84%	0.0092764416 	2014	33.87%	0.0965877602 	2015	-9.28%	0.0145719388 	Mean	2.79% 	Variance		0.0415796814 	Standard Deviation		20.391% 		Lyxor ChinaH	Lyxor MSIndia	Product of Deviation From the Mean 	2009	2.00%	5.86%	0.0006886751 	2010	4.25%	22.40%	0.0088126522 	2011	-29.40%	-27.07%	0.087063128 	2012	13.23%	0.60%	-0.0029527935 	2013	8.86%	-6.84%	-0.0087687278 	2014	2.31%	33.87%	0.0079383551 	2015	-2.96%	-9.28%	0.0032782551 	Mean	-0.24%	2.79% 	Covariance			0.01601 	Correlation			0.56623 	Existing Portfolio: 60% in Lyxor ChinaH and 40% in Lyxor MSIndia 		Lyxor ChinaH	Lyxor MSIndia	Existing Portfolio	Square of Deviation From the Mean 	2009	2.00%	5.86%	3.54%	0.0006625476 	2010	4.25%	22.40%	11.51%	0.011109160 	2011	-29.40%	-27.07%	-28.47%	0.0866595844 	2012	13.23%	0.60%	8.18%	0.0051955264 	2013	8.86%	-6.84%	2.58%	0.000259210 	2014	2.31%	33.87%	14.93%	0.019499330 	2015	-2.96%	-9.28%	-5.49%	0.0041705764 	Mean	-0.24%	2.79%	0.97% 	Portfolio Variance				0.0212593224 	Portfolio Standard Deviation				14.581% 	Alternative way of calculating standard deviation by using the formula 	w2	0.36	0.16 	Variance	0.0192272495	0.0415796814 	Portfolio Variance				0.0212593224 	Portfolio Standard Deviation				14.581% 	Question 2: After adding Lyxor USDJIA, what was the portfolio's standard deviation and return? How does the new portfolio compare with the calculation in Question 1? 		Lyxor ChinaH	Lyxor MSIndia	Lyxor USDJIA	New Portfolio	Square of Deviation From the Mean 	2009	2.00%	5.86%	5.56%	4.23%	0.000052252 	2010	4.25%	22.40%	6.11%	10.25%	0.0045541354 	2011	-29.40%	-27.07%	7.94%	-17.50%	0.0441134121 	2012	13.23%	0.60%	18.29%	10.96%	0.005559174 	2013	8.86%	-6.84%	17.09%	6.62%	0.0009716061 	2014	2.31%	33.87%	14.20%	15.35%	0.0140232347 	2015	-2.96%	-9.28%	-4.71%	-5.38%	0.0078909737 	Mean	-0.24%	2.79%	9.21%	3.503% 	Portfolio Variance					0.012860798 	Portfolio Standard Deviation					11.341% 	Question 3: Based on your data analysis, should Susie diversify her portfolio or remain invested in China and India only? 		Lyxor USDJIA	Square of Deviation From the Mean 	2009	5.56%	0.0013332995 	2010	6.11%	0.0009648318 	2011	7.94%	0.0001625445 	2012	18.29%	0.008242831 	2013	17.09%	0.0062135063 	2014	14.20%	0.002488973 	2015	-4.71%	0.0193710816 	Mean	9.21% 	Variance		0.0064628446 	Standard Deviation		8.039% 		Lyxor ChinaH	Lyxor USDJIA	Product of Deviation From the Mean 	2009	2.00%	5.56%	-0.0008194869 	2010	4.25%	6.11%	-0.0013960034 	2011	-29.40%	7.94%	0.0037171478 	2012	13.23%	18.29%	0.0122333091 	2013	8.86%	17.09%	0.0071765258 	2014	2.31%	14.20%	0.0012743231 	2015	-2.96%	-4.71%	0.003779732 	Mean	-0.24%	9.21% 	Covariance			0.00433 	Correlation			0.38822 		Lyxor MSIndia	Lyxor USDJIA	Product of Deviation From the Mean 	2009	5.86%	5.56%	-0.0011204696 	2010	22.40%	6.11%	-0.0060907639 	2011	-27.07%	7.94%	0.0038071214 	2012	0.60%	18.29%	-0.0019895988 	2013	-6.84%	17.09%	-0.0075920504 	2014	33.87%	14.20%	0.0155049774 	2015	-9.28%	-4.71%	0.0168010183 	Mean	2.79%	9.21% 	Covariance			0.00322 	Correlation			0.19643 	Question 4: Calculate the betas of Lyxor ChinaH, Lyxor MSIndia, and Lyxor USDJIA. To calculate the covariance with the market proxy, use the Lyxor World return data shown in Exhibit 1 in the case. Assuming a risk-free rate of 2.5 per cent and a market risk premium of 5.5 per cent, what are the required returns for each of the three ETFs? 		Lyxor World	Square of Deviation From the Mean 	2009	7.69%	0.000008493 	2010	5.79%	0.0004802359 	2011	-3.28%	0.0126819773 	2012	20.75%	0.0163036416 	2013	14.14%	0.003792800 	2014	15.06%	0.0050106173 	2015	-4.28%	0.0150342631 	Mean	7.98% 	Variance		0.0088853381 	Standard Deviation		9.426% 		Lyxor ChinaH	Lyxor World	Product of Deviation From the Mean			Lyxor MSIndia	Lyxor World	Product of Deviation From the Mean			Lyxor USDJIA	Lyxor World	product of deviation from mean 	2009	2.00%	7.69%	-0.000065405		2009	5.86%	7.69%	-0.000089427		2009	5.56%	7.69%	0.0001064133 	2010	4.25%	5.79%	-0.0009848906		2010	22.40%	5.79%	-0.0042970784		2010	6.11%	5.79%	0.0006806959 	2011	-29.40%	-3.28%	0.0328334994		2011	-27.07%	-3.28%	0.0336282345		2011	7.94%	-3.28%	0.0014357527 	2012	13.23%	20.75%	0.017204738		2012	0.60%	20.75%	-0.0027981412		2012	18.29%	20.75%	0.0115925909 	2013	8.86%	14.14%	0.0056069394		2013	-6.84%	14.14%	-0.0059315841		2013	17.09%	14.14%	0.004854543 	2014	2.31%	15.06%	0.0018080694		2014	33.87%	15.06%	0.0219991888		2014	14.20%	15.06%	0.0035314715 	2015	-2.96%	-4.28%	0.0033298537		2015	-9.28%	-4.28%	0.0148012959		2015	-4.71%	-4.28%	0.0170654604 	Mean	-0.24%	7.98%			Mean	2.79%	7.98%			Mean	9.21%	7.98% 	Covariance With Market			0.00996		Covariance With Market			0.00955		covariance with market			0.00654 	Beta of Lyxor ChinaH			1.12044		Beta of Lyxor MSIndia			1.07504		beta of Lyxor USDJIA			0.73655 	Required Return			8.662%		Required Return			8.413%		required return			6.551% 	Question 5: Calculate the existing portfolio’s beta and the new portfolio’s beta. Assuming a risk-free rate of 2.5 per cent and a market risk premium of 5.5 per cent, what are their required returns? 		Beta	Required Return	Mean Historical Returns 	Lyxor ChinaH	1.12044	8.662%	-0.24% 	Lyxor MSIndia	1.07504	8.413%	2.79% 	Lyxor USDJIA	0.73655	6.551%	9.21% 	Existing Portfolio:     60% Lyxor ChinaH, 40% Lyxor MSIndia	1.10228	8.563%	0.97% 	New Portfolio:        40% Lyxor ChinaH, 30% Lyxor MSIndia, 30% Lyxor USDJIA	0.99165	7.954%	3.503% 	The historical returns calculation for Lyxor USDJIA was higher than its required return after the CAPM calculation. This means that the Lyxor USDJIA ETF falls above the SML line, and is underpriced. Therefore, Susie should purchase this ETF.   Daily Prices London 10 am Fix 	FRED Graph Observations				SMA Daily Vol formula is per below 	Federal Reserve Economic Data 	Link: https://fred.stlouisfed.org 	Help: https://fredhelp.stlouisfed.org 	Economic Research Division 	Federal Reserve Bank of St. Louis 	5 Years 	Frequency: Daily 	observation_date	GOLDAMGBD228NLBM	Average	 Daily Returns	Daily SMA Volatility 	2016-06-09	1258.350	1440.155	0.0065348061	0.0084062496 	2016-06-10	1266.600		0.0137219388 	2016-06-13	1284.100		-0.0036668658	Confidence Interval 	2016-06-14	1279.400		0.0020301405	99% 	2016-06-15	1282.000		0.0193130761 	2016-06-16	1307.000		-0.0173648971	Daily Var 99%	1.9556% 	2016-06-17	1284.500		-0.0009736151	Holding Period ( days ) 	2016-06-20	1283.250		-0.0019110398	10 	2016-06-21	1280.800		-0.0124127605 	2016-06-22	1265.000		0.0005927097	10 day 99% conf Var	6.1841% 	2016-06-23	1265.750		0.0372969264 	2016-06-24	1313.850		0.0081487688 	2016-06-27	1324.600		-0.0095578365 	2016-06-28	1312.000		0.0043730462 	2016-06-29	1317.750		-0.000569314 	2016-06-30	1317.000		0.0111374441 	2016-07-01	1331.750		0.0126843708 	2016-07-04	1348.750		-0.0029701154 	2016-07-05	1344.750		0.0186026177 	2016-07-06	1370.000		-0.0021190319 	2016-07-07	1367.100		-0.0080787748 	2016-07-08	1356.100		0.0015841733 	2016-07-11	1358.250		-0.0039836282 	2016-07-12	1352.850		-0.0093573146 	2016-07-13	1340.250		-0.0109155419 	2016-07-14	1325.700		0.0036141896 	2016-07-15	1330.500		-0.0032748039 	2016-07-18	1326.150		0.0045517035 	2016-07-19	1332.200		-0.0049665239 	2016-07-20	1325.600		-0.0027194457 	2016-07-21	1322.000		0.0009073039 	2016-07-22	1323.200		-0.0062163797 	2016-07-25	1315.000		0.0047415926 	2016-07-26	1321.250		-0.0003406446 	2016-07-27	1320.800		0.0154016793 	2016-07-28	1341.300		-0.0065824159 	2016-07-29	1332.500		0.0121955005 	2016-08-01	1348.850		0.006871102 	2016-08-02	1358.150		0.004591292 	2016-08-03	1364.400		-0.0097586899 	2016-08-04	1351.150		0.0084385577 	2016-08-05	1362.600		-0.0242156971 	2016-08-08	1330.000		0.0021780774 	2016-08-09	1332.900		0.0141170051 	2016-08-10	1351.850		-0.0054146401 	2016-08-11	1344.550		-0.0058554946 	2016-08-12	1336.700		0.0018685308 	2016-08-15	1339.200		0.0073652827 	2016-08-16	1349.100		-0.0047179537 	2016-08-17	1342.750		0.0032343839 	2016-08-18	1347.100		-0.0001856011 	2016-08-19	1346.850		-0.0093617229 	2016-08-22	1334.300		0.0031427742 	2016-08-23	1338.500		-0.0008969281 	2016-08-24	1337.300		-0.0096176262 	2016-08-25	1324.500		0.0003019552 	2016-08-26	1324.900		0 	2016-08-29	1324.900		-0.0045768401 	2016-08-30	1318.850		-0.0033418176 	2016-08-31	1314.450		-0.0066790318 	2016-09-01	1305.700		0.0044322249 	2016-09-02	1311.500		0.0127284088 	2016-09-05	1328.300		0.0013166064 	2016-09-06	1330.050		0.0139617021 	2016-09-07	1348.750		-0.0005562251 	2016-09-08	1348.000		-0.0092039477 	2016-09-09	1335.650		-0.0061205906 	2016-09-12	1327.500		0.0007530121 	2016-09-13	1328.500		-0.0039974409 	2016-09-14	1323.200		-0.002345554 	2016-09-15	1320.100		-0.0044413306 	2016-09-16	1314.250		0.000608527 	2016-09-19	1315.050		0.0002661142 	2016-09-20	1315.400		0.0031878585 	2016-09-21	1319.600		0.0096906925 	2016-09-22	1332.450		0.0025858691 	2016-09-23	1335.900		0.0002993788 	2016-09-26	1336.300		-0.0003368074 	2016-09-27	1335.850		-0.0083062887 	2016-09-28	1324.800		-0.0029860359 	2016-09-29	1320.850		0.0053232783 	2016-09-30	1327.900		-0.006990261 	2016-10-03	1318.650		-0.0072304143 	2016-10-04	1309.150		-0.0272165145 	2016-10-05	1274.000		-0.0066942561 	2016-10-06	1265.500		-0.0083317284 	2016-10-07	1255.000		0.0056414277 	2016-10-10	1262.100		-0.0045265116 	2016-10-11	1256.400		-0.0005573027 	2016-10-12	1255.700		0.0018299723 	2016-10-13	1258.000		-0.0014716706 	2016-10-14	1256.150		-0.0027502658 	2016-10-17	1252.700		0.0071191662 	2016-10-18	1261.650		0.0063996426 	2016-10-19	1269.750		-0.00043325 	2016-10-20	1269.200		-0.0041450427 	2016-10-21	1263.950		0.0024101634 	2016-10-24	1267.000		0.0018136661 	2016-10-25	1269.300		0.0036174937 	2016-10-26	1273.900		-0.0036174937 	2016-10-27	1269.300		-0.0026822358 	2016-10-28	1265.900		0.006535199 	2016-10-31	1274.200		0.0079731525 	2016-11-01	1284.400		0.0088751673 	2016-11-02	1295.850		-0.0022017507 	2016-11-03	1293.000		0.0067060027 	2016-11-04	1301.700		-0.0115125861 	2016-11-07	1286.800		-0.0021783111 	2016-11-08	1284.000		0.0158779485 	2016-11-09	1304.550		-0.0182951979 	2016-11-10	1280.900		-0.0199095891 	2016-11-11	1255.650		-0.0266736282 	2016-11-14	1222.600		0.0051397217 	2016-11-15	1228.900		-0.0026073509 	2016-11-16	1225.700		0.0051267557 	2016-11-17	1232.000		-0.0212468515 	2016-11-18	1206.100		0.0073109101 	2016-11-21	1214.950		0.0021377192 	2016-11-22	1217.550		-0.0035379336 	2016-11-23	1213.250		-0.0216630009 	2016-11-24	1187.250		0.0002105485 	2016-11-25	1187.500		0.0013464615 	2016-11-28	1189.100		-0.0015148968 	2016-11-29	1187.300		0.0000842212 	2016-11-30	1187.400		-0.0158312412 	2016-12-01	1168.750		0.0024782101 	2016-12-02	1171.650		-0.0057777653 	2016-12-05	1164.900		0.0053509256 	2016-12-06	1171.150		0.0000853825 	2016-12-07	1171.250		0.0029838044 	2016-12-08	1174.750		-0.0049922234 	2016-12-09	1168.900		-0.0124824072 	2016-12-12	1154.400		0.0025521805 	2016-12-13	1157.350		0.0031057265 	2016-12-14	1160.950		-0.0248552082 	2016-12-15	1132.450		0.0021170563 	2016-12-16	1134.850		0.0024202965 	2016-12-19	1137.600		-0.0042724755 	2016-12-20	1132.750		0.0014555722 	2016-12-21	1134.400		-0.0033996368 	2016-12-22	1130.550		0.0003979572 	2016-12-23	1131.000		0 	2016-12-26	1131.000		0 	2016-12-27	1131.000		0.007706743 	2016-12-28	1139.750		0.0061665149 	2016-12-29	1146.800		0.0106683869 	2016-12-30	1159.100		0 	2017-01-02	1159.100		-0.0090565022 	2017-01-03	1148.650		0.0149059812 	2017-01-04	1165.900		0.0061138736 	2017-01-05	1173.050		0.0042108907 	2017-01-06	1178.000		-0.0016142054 	2017-01-09	1176.100		0.0060187525 	2017-01-10	1183.200		0.0036697289 	2017-01-11	1187.550		0.0159555636 	2017-01-12	1206.650		-0.0085726701 	2017-01-13	1196.350		0.0053353467 	2017-01-16	1202.750		0.0121889744 	2017-01-17	1217.500		-0.0041152321 	2017-01-18	1212.500		-0.0075750098 	2017-01-19	1203.350		-0.0035380586 	2017-01-20	1199.100		0.0121434652 	2017-01-23	1213.750		-0.0003708205 	2017-01-24	1213.300		-0.0081099418 	2017-01-25	1203.500		-0.0099789977 	2017-01-26	1191.550		-0.0061875395 	2017-01-27	1184.200		0.0047598076 	2017-01-30	1189.850		0.0074938077 	2017-01-31	1198.800		0.0092993031 	2017-02-01	1210.000		0.0115446733 	2017-02-02	1224.050		-0.0090271837 	2017-02-03	1213.050		0.0072282544 	2017-02-06	1221.850		0.0074607436 	2017-02-07	1231.000		0.0037298349 	2017-02-08	1235.600		0.0049649929 	2017-02-09	1241.750		-0.0129687735 	2017-02-10	1225.750		0.0029733439 	2017-02-13	1229.400		0.0002033306 	2017-02-14	1229.650		-0.0036662906 	2017-02-15	1225.150		0.0094236856 	2017-02-16	1236.750		0.0037528039 	2017-02-17	1241.400		-0.0048854443 	2017-02-20	1235.350		-0.0053976308 	2017-02-21	1228.700		0

Akhilesh · Accepted Answer

PowerPoint Presentation
Average life expectancy of is few years lower at 70.6 years for men and 75.0 years for women. 
Source :  www.worlddata.info
Hence we women can take slightly higher risk while investing in financial markets as compared to male. Basis this logic we have arrived at below allocation:
	Current Age	60
	Retirement	After 5 yrs
	Stocks Value	                       750,000 
	Bonds value	                       600,000 
	Total Net worth	                    1,350,000 
	Name	Alloc. For male	Alloc. For Female
	Marvell Technology Inc	20%	10%
	Enphase Energy Inc	20%	10%
	Tesla	20%	30%
	Microchip Technology Inc.

Final Project FN 315 – Summer 2021 Due Date: Friday 2nd July Portfolio Construction The Case My wife and I are 60 years old and plan to retire in 5 years. At present, we have assets of $750,000 in...

Answer To: Final Project FN 315 – Summer 2021 Due Date: Friday 2nd July Portfolio Construction The Case My wife...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment

Sun	Mon	Tue	Wed	Thu	Fri	Sat
30	31	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	1	2	3