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...

Portfolio Construction


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 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 StockPrice% of portfolioBetaPortfolio B IBM$ 14510%1.232.70100% OSTK$ 9450%4.46 C$ 8010%1.92 KHC$ 4410%1.10 20%0.67 StockPrice% of portfolioBetaPortfolio B IBM$ 122.0030%1.231.20 WMT$ 145.834%0.48 AAPL$ 136.0151%1.27 KHC$ 34.0015%1.10 Example StockPrice% of portfolioBetaPortfolio B IBM$ 14510%1.232.70100% OSTK$ 9450%4.46 C$ 8010%1.92 KHC$ 4410%1.10 20%0.67 StockPrice% of portfolioBetaPortfolio B IBM$ 122.0030%1.231.20 WMT$ 145.834%0.48 AAPL$ 136.0151%1.27 KHC$ 34.0015%1.10 Homework Stock PriceBeta (5yr)Dividend Yield OYTRPortfolio WeightMarket Risk PremiumPortfolio Beta Dollar Gereral$206.760.510.82%0.06%10%7.70%1.211 Lowe's Inc$190.301.191.69%20% NVIDIA $703.671.580.09%30% Visa$230.501.120.56%40% Question 1 Single-index return Dollar Gereral3.96%Porfolio Return: Lowe's Inc9.15%9.31% NVIDIA 12.13% Visa8.62% Question 2 PriceReturn Dollar Gereral$206.76$8.18Portfolio Return Lowe's Inc$190.30$17.4237.85 NVIDIA $703.67$85.36 Visa$230.50$19.86 Question 3Risk adjusted return m2= Rp-Rm Risk Adjusted Return Dollar Gereral-3.74%1.61% Lowe's Inc1.45% NVIDIA 4.43% Visa0.92% Question 4 New Portfolios Stock PriceBeta (5yr)Dividend Yield OYTRPortfolio WeightMarket Risk PremiumPortfolio Beta Dollar Gereral$206.760.510.82%0.06%10%7.70%1.211 Lowe's Inc$190.301.191.69%20% NVIDIA $703.671.580.09%30% Visa$230.501.120.56%40% Stock New PercentsBetaStock New PercentsBeta Dollar Gereral29%1.011Dollar Gereral7%1.411 Lowe's Inc30%Lowe's Inc15% NVIDIA 10%NVIDIA 68% Visa31%Visa10% 1.010Calculated1.401Calculated 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 ChinaHSquare of Deviation From the Mean 20092.00%0.0005036818 20104.25%0.0020198604 2011-29.40%0.0850055676 201213.23%0.0181556376 20138.86%0.0082888018 20142.31%0.0006524376 2015-2.96%0.0007375104 Mean-0.24% Variance0.0192272495 Standard Deviation13.866% Lyxor MSIndiaSquare of Deviation From the Mean 20095.86%0.0009416131 201022.40%0.0384496073 2011-27.07%0.0891704916 20120.60%0.0004802359 2013-6.84%0.0092764416 201433.87%0.0965877602 2015-9.28%0.0145719388 Mean2.79% Variance0.0415796814 Standard Deviation20.391% Lyxor ChinaHLyxor MSIndiaProduct of Deviation From the Mean 20092.00%5.86%0.0006886751 20104.25%22.40%0.0088126522 2011-29.40%-27.07%0.087063128 201213.23%0.60%-0.0029527935 20138.86%-6.84%-0.0087687278 20142.31%33.87%0.0079383551 2015-2.96%-9.28%0.0032782551 Mean-0.24%2.79% Covariance0.01601 Correlation0.56623 Existing Portfolio: 60% in Lyxor ChinaH and 40% in Lyxor MSIndia Lyxor ChinaHLyxor MSIndiaExisting PortfolioSquare of Deviation From the Mean 20092.00%5.86%3.54%0.0006625476 20104.25%22.40%11.51%0.011109160 2011-29.40%-27.07%-28.47%0.0866595844 201213.23%0.60%8.18%0.0051955264 20138.86%-6.84%2.58%0.000259210 20142.31%33.87%14.93%0.019499330 2015-2.96%-9.28%-5.49%0.0041705764 Mean-0.24%2.79%0.97% Portfolio Variance0.0212593224 Portfolio Standard Deviation14.581% Alternative way of calculating standard deviation by using the formula w20.360.16 Variance0.01922724950.0415796814 Portfolio Variance0.0212593224 Portfolio Standard Deviation14.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 ChinaHLyxor MSIndiaLyxor USDJIANew PortfolioSquare of Deviation From the Mean 20092.00%5.86%5.56%4.23%0.000052252 20104.25%22.40%6.11%10.25%0.0045541354 2011-29.40%-27.07%7.94%-17.50%0.0441134121 201213.23%0.60%18.29%10.96%0.005559174 20138.86%-6.84%17.09%6.62%0.0009716061 20142.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 Variance0.012860798 Portfolio Standard Deviation11.341% Question 3: Based on your data analysis, should Susie diversify her portfolio or remain invested in China and India only? Lyxor USDJIASquare of Deviation From the Mean 20095.56%0.0013332995 20106.11%0.0009648318 20117.94%0.0001625445 201218.29%0.008242831 201317.09%0.0062135063 201414.20%0.002488973 2015-4.71%0.0193710816 Mean9.21% Variance0.0064628446 Standard Deviation8.039% Lyxor ChinaHLyxor USDJIAProduct of Deviation From the Mean 20092.00%5.56%-0.0008194869 20104.25%6.11%-0.0013960034 2011-29.40%7.94%0.0037171478 201213.23%18.29%0.0122333091 20138.86%17.09%0.0071765258 20142.31%14.20%0.0012743231 2015-2.96%-4.71%0.003779732 Mean-0.24%9.21% Covariance0.00433 Correlation0.38822 Lyxor MSIndiaLyxor USDJIAProduct of Deviation From the Mean 20095.86%5.56%-0.0011204696 201022.40%6.11%-0.0060907639 2011-27.07%7.94%0.0038071214 20120.60%18.29%-0.0019895988 2013-6.84%17.09%-0.0075920504 201433.87%14.20%0.0155049774 2015-9.28%-4.71%0.0168010183 Mean2.79%9.21% Covariance0.00322 Correlation0.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 WorldSquare of Deviation From the Mean 20097.69%0.000008493 20105.79%0.0004802359 2011-3.28%0.0126819773 201220.75%0.0163036416 201314.14%0.003792800 201415.06%0.0050106173 2015-4.28%0.0150342631 Mean7.98% Variance0.0088853381 Standard Deviation9.426% Lyxor ChinaHLyxor WorldProduct of Deviation From the MeanLyxor MSIndiaLyxor WorldProduct of Deviation From the MeanLyxor USDJIALyxor Worldproduct of deviation from mean 20092.00%7.69%-0.00006540520095.86%7.69%-0.00008942720095.56%7.69%0.0001064133 20104.25%5.79%-0.0009848906201022.40%5.79%-0.004297078420106.11%5.79%0.0006806959 2011-29.40%-3.28%0.03283349942011-27.07%-3.28%0.033628234520117.94%-3.28%0.0014357527 201213.23%20.75%0.01720473820120.60%20.75%-0.0027981412201218.29%20.75%0.0115925909 20138.86%14.14%0.00560693942013-6.84%14.14%-0.0059315841201317.09%14.14%0.004854543 20142.31%15.06%0.0018080694201433.87%15.06%0.0219991888201414.20%15.06%0.0035314715 2015-2.96%-4.28%0.00332985372015-9.28%-4.28%0.01480129592015-4.71%-4.28%0.0170654604 Mean-0.24%7.98%Mean2.79%7.98%Mean9.21%7.98% Covariance With Market0.00996Covariance With Market0.00955covariance with market0.00654 Beta of Lyxor ChinaH1.12044Beta of Lyxor MSIndia1.07504beta of Lyxor USDJIA0.73655 Required Return8.662%Required Return8.413%required return6.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? BetaRequired ReturnMean Historical Returns Lyxor ChinaH1.120448.662%-0.24% Lyxor MSIndia1.075048.413%2.79% Lyxor USDJIA0.736556.551%9.21% Existing Portfolio: 60% Lyxor ChinaH, 40% Lyxor MSIndia1.102288.563%0.97% New Portfolio: 40% Lyxor ChinaH, 30% Lyxor MSIndia, 30% Lyxor USDJIA0.991657.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 ObservationsSMA 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_dateGOLDAMGBD228NLBMAverage Daily ReturnsDaily SMA Volatility 2016-06-091258.3501440.1550.00653480610.0084062496 2016-06-101266.6000.0137219388 2016-06-131284.100-0.0036668658Confidence Interval 2016-06-141279.4000.002030140599% 2016-06-151282.0000.0193130761 2016-06-161307.000-0.0173648971Daily Var 99%1.9556% 2016-06-171284.500-0.0009736151Holding Period ( days ) 2016-06-201283.250-0.001911039810 2016-06-211280.800-0.0124127605 2016-06-221265.0000.000592709710 day 99% conf Var6.1841% 2016-06-231265.7500.0372969264 2016-06-241313.8500.0081487688 2016-06-271324.600-0.0095578365 2016-06-281312.0000.0043730462 2016-06-291317.750-0.000569314 2016-06-301317.0000.0111374441 2016-07-011331.7500.0126843708 2016-07-041348.750-0.0029701154 2016-07-051344.7500.0186026177 2016-07-061370.000-0.0021190319 2016-07-071367.100-0.0080787748 2016-07-081356.1000.0015841733 2016-07-111358.250-0.0039836282 2016-07-121352.850-0.0093573146 2016-07-131340.250-0.0109155419 2016-07-141325.7000.0036141896 2016-07-151330.500-0.0032748039 2016-07-181326.1500.0045517035 2016-07-191332.200-0.0049665239 2016-07-201325.600-0.0027194457 2016-07-211322.0000.0009073039 2016-07-221323.200-0.0062163797 2016-07-251315.0000.0047415926 2016-07-261321.250-0.0003406446 2016-07-271320.8000.0154016793 2016-07-281341.300-0.0065824159 2016-07-291332.5000.0121955005 2016-08-011348.8500.006871102 2016-08-021358.1500.004591292 2016-08-031364.400-0.0097586899 2016-08-041351.1500.0084385577 2016-08-051362.600-0.0242156971 2016-08-081330.0000.0021780774 2016-08-091332.9000.0141170051 2016-08-101351.850-0.0054146401 2016-08-111344.550-0.0058554946 2016-08-121336.7000.0018685308 2016-08-151339.2000.0073652827 2016-08-161349.100-0.0047179537 2016-08-171342.7500.0032343839 2016-08-181347.100-0.0001856011 2016-08-191346.850-0.0093617229 2016-08-221334.3000.0031427742 2016-08-231338.500-0.0008969281 2016-08-241337.300-0.0096176262 2016-08-251324.5000.0003019552 2016-08-261324.9000 2016-08-291324.900-0.0045768401 2016-08-301318.850-0.0033418176 2016-08-311314.450-0.0066790318 2016-09-011305.7000.0044322249 2016-09-021311.5000.0127284088 2016-09-051328.3000.0013166064 2016-09-061330.0500.0139617021 2016-09-071348.750-0.0005562251 2016-09-081348.000-0.0092039477 2016-09-091335.650-0.0061205906 2016-09-121327.5000.0007530121 2016-09-131328.500-0.0039974409 2016-09-141323.200-0.002345554 2016-09-151320.100-0.0044413306 2016-09-161314.2500.000608527 2016-09-191315.0500.0002661142 2016-09-201315.4000.0031878585 2016-09-211319.6000.0096906925 2016-09-221332.4500.0025858691 2016-09-231335.9000.0002993788 2016-09-261336.300-0.0003368074 2016-09-271335.850-0.0083062887 2016-09-281324.800-0.0029860359 2016-09-291320.8500.0053232783 2016-09-301327.900-0.006990261 2016-10-031318.650-0.0072304143 2016-10-041309.150-0.0272165145 2016-10-051274.000-0.0066942561 2016-10-061265.500-0.0083317284 2016-10-071255.0000.0056414277 2016-10-101262.100-0.0045265116 2016-10-111256.400-0.0005573027 2016-10-121255.7000.0018299723 2016-10-131258.000-0.0014716706 2016-10-141256.150-0.0027502658 2016-10-171252.7000.0071191662 2016-10-181261.6500.0063996426 2016-10-191269.750-0.00043325 2016-10-201269.200-0.0041450427 2016-10-211263.9500.0024101634 2016-10-241267.0000.0018136661 2016-10-251269.3000.0036174937 2016-10-261273.900-0.0036174937 2016-10-271269.300-0.0026822358 2016-10-281265.9000.006535199 2016-10-311274.2000.0079731525 2016-11-011284.4000.0088751673 2016-11-021295.850-0.0022017507 2016-11-031293.0000.0067060027 2016-11-041301.700-0.0115125861 2016-11-071286.800-0.0021783111 2016-11-081284.0000.0158779485 2016-11-091304.550-0.0182951979 2016-11-101280.900-0.0199095891 2016-11-111255.650-0.0266736282 2016-11-141222.6000.0051397217 2016-11-151228.900-0.0026073509 2016-11-161225.7000.0051267557 2016-11-171232.000-0.0212468515 2016-11-181206.1000.0073109101 2016-11-211214.9500.0021377192 2016-11-221217.550-0.0035379336 2016-11-231213.250-0.0216630009 2016-11-241187.2500.0002105485 2016-11-251187.5000.0013464615 2016-11-281189.100-0.0015148968 2016-11-291187.3000.0000842212 2016-11-301187.400-0.0158312412 2016-12-011168.7500.0024782101 2016-12-021171.650-0.0057777653 2016-12-051164.9000.0053509256 2016-12-061171.1500.0000853825 2016-12-071171.2500.0029838044 2016-12-081174.750-0.0049922234 2016-12-091168.900-0.0124824072 2016-12-121154.4000.0025521805 2016-12-131157.3500.0031057265 2016-12-141160.950-0.0248552082 2016-12-151132.4500.0021170563 2016-12-161134.8500.0024202965 2016-12-191137.600-0.0042724755 2016-12-201132.7500.0014555722 2016-12-211134.400-0.0033996368 2016-12-221130.5500.0003979572 2016-12-231131.0000 2016-12-261131.0000 2016-12-271131.0000.007706743 2016-12-281139.7500.0061665149 2016-12-291146.8000.0106683869 2016-12-301159.1000 2017-01-021159.100-0.0090565022 2017-01-031148.6500.0149059812 2017-01-041165.9000.0061138736 2017-01-051173.0500.0042108907 2017-01-061178.000-0.0016142054 2017-01-091176.1000.0060187525 2017-01-101183.2000.0036697289 2017-01-111187.5500.0159555636 2017-01-121206.650-0.0085726701 2017-01-131196.3500.0053353467 2017-01-161202.7500.0121889744 2017-01-171217.500-0.0041152321 2017-01-181212.500-0.0075750098 2017-01-191203.350-0.0035380586 2017-01-201199.1000.0121434652 2017-01-231213.750-0.0003708205 2017-01-241213.300-0.0081099418 2017-01-251203.500-0.0099789977 2017-01-261191.550-0.0061875395 2017-01-271184.2000.0047598076 2017-01-301189.8500.0074938077 2017-01-311198.8000.0092993031 2017-02-011210.0000.0115446733 2017-02-021224.050-0.0090271837 2017-02-031213.0500.0072282544 2017-02-061221.8500.0074607436 2017-02-071231.0000.0037298349 2017-02-081235.6000.0049649929 2017-02-091241.750-0.0129687735 2017-02-101225.7500.0029733439 2017-02-131229.4000.0002033306 2017-02-141229.650-0.0036662906 2017-02-151225.1500.0094236856 2017-02-161236.7500.0037528039 2017-02-171241.400-0.0048854443 2017-02-201235.350-0.0053976308 2017-02-211228.7000