Finance and Accounting Portfolio Analysis - Assignment Example

Finance and Accounting Portfolio Analysis Project Assignment We need to choose two stocks from different industries and we have chosen Tesla MotorsInc. as Stock A and Netflix Inc. as Stock B, Nintendo Co Ltd as Stock C and the S&P 500 as benchmark. We have calculated the monthly price performance of the stocks for 60 months. But for the company Tesla Motors Inc we have taken data of 43 months as data before that period is not available as the company was not listed in stock exchange (Yahoo Finance, 2014a).   Stock A Month Price Index Monthly Price Performance 1 23.83 100 0.00% 2 19.94 83.67603861 -16.32% 3 19.48 81.7456987 -2.31% 4 20.41 85.64834243 4.77% 5 21.84 91.6491817 7.01% 6 35.33 148.2584977 61.77% 7 26.63 111.7498951 -24.62% 8 24.1 101.1330256 -9.50% 9 23.89 100.2517835 -0.87% 10 27.75 116.4498531 16.16% 11 27.6 115.8203945 -0.54% 12 30.14 126.4792279 9.20% 13 29.13 122.2408728 -3.35% 14 28.17 118.2123374 -3.30% 15 24.74 103.8187159 -12.18% 16 24.39 102.349979 -1.41% 17 29.37 123.2480067 20.42% 18 32.74 137.3898447 11.47% 19 28.56 119.8489299 -12.77% 20 29.07 121.9890894 1.79% 21 33.41 140.2014268 14.93% 22 37.24 156.2736047 11.46% 23 33.13 139.0264373 -11.04% 24 29.5 123.7935376 -10.96% 25 31.29 131.3050776 6.07% 26 27.42 115.0650441 -12.37% 27 28.52 119.6810743 4.01% 28 29.28 122.8703315 2.66% 29 28.13 118.0444817 -3.93% 30 33.82 141.9219471 20.23% 31 33.87 142.1317667 0.15% 32 37.51 157.4066303 10.75% 33 34.83 146.1603021 -7.14% 34 37.89 159.0012589 8.79% 35 53.99 226.5631557 42.49% 36 97.76 410.2391943 81.07% 37 107.36 450.5245489 9.82% 38 134.28 563.4913974 25.07% 39 169 709.1900965 25.86% 40 193.37 811.4561477 14.42% 41 159.94 671.1707931 -17.29% 42 127.28 534.1166597 -20.42% 43 150.43 631.2631137 18.19% (NASDAQ, 2014).   Stock B Month Price Index Monthly Price Performance 1 36.14 100 0 2 36.24 100.2767017 0.28% 3 42.92 118.7603763 18.43% 4 45.31 125.3735473 5.57% 5 39.42 109.0758163 -13.00% 6 41.34 114.3884892 4.87% 7 43.94 121.5827338 6.29% 8 43.63 120.7249585 -0.71% 9 46.17 127.7531821 5.82% 10 53.45 147.897067 15.77% 11 58.63 162.2302158 9.69% 12 55.09 152.4349751 -6.04% 13 62.25 172.2468179 13.00% 14 66.05 182.7614831 6.10% 15 73.74 204.039845 11.64% 16 98.9 273.6579967 34.12% 17 111.15 307.5539568 12.39% 18 108.65 300.6364139 -2.25% 19 102.55 283.7576093 -5.61% 20 125.52 347.3159934 22.40% 21 162.16 448.6995019 29.19% 22 173.57 480.2711677 7.04% 23 205.9 569.7288323 18.63% 24 175.7 486.1649142 -14.67% 25 214.08 592.3630327 21.84% 26 206.67 571.8594355 -3.46% 27 237.78 657.9413392 15.05% 28 232.67 643.8018816 -2.15% 29 270.8 749.3082457 16.39% 30 262.69 726.8677366 -2.99% 31 265.99 735.9988932 1.26% 32 235.01 650.2767017 -11.65% 33 113.27 313.4200332 -51.80% 34 82.08 227.1167681 -27.54% 35 64.53 178.555617 -21.38% 36 69.29 191.7266187 7.38% 37 120.2 332.5954621 73.47% 38 110.73 306.3918096 -7.88% 39 115.04 318.3176536 3.89% 40 80.14 221.7487548 -30.34% 41 63.44 175.5395683 -20.84% 42 68.49 189.513005 7.96% 43 56.85 157.3049253 -17.00% 44 59.72 165.2462645 5.05% 45 54.44 150.6364139 -8.84% 46 79.24 219.2584394 45.55% 47 81.71 226.0929718 3.12% 48 92.59 256.1981184 13.32% 49 165.24 457.2219148 78.46% 50 188.08 520.4205866 13.82% 51 189.28 523.7410072 0.64% 52 216.07 597.8693968 14.15% 53 226.25 626.0376314 4.71% 54 211.09 584.0896514 -6.70% 55 244.48 676.4803542 15.82% 56 283.91 785.5838406 16.13% 57 309.21 855.5893747 8.91% 58 322.48 892.3076923 4.29% 59 365.8 1012.174875 13.43% 60 368.17 1018.732706 0.65% (Yahoo Finance, 2014c).   Stock C Nintendo Co Ltd                   price Index Monthly Price Performance Month 36.4 100 0.00% 1 35.25 96.84065934 -3.16% 2 36.5 100.2747253 3.55% 3 33.65 92.44505495 -7.81% 4 33.68 92.52747253 0.09% 5 34.47 94.6978022 2.35% 6 33.12 90.98901099 -3.92% 7 33.55 92.17032967 1.30% 8 31.57 86.73076923 -5.90% 9 31.41 86.29120879 -0.51% 10 30.78 84.56043956 -2.01% 11 29.82 81.92307692 -3.12% 12 34.9 95.87912088 17.04% 13 33.87 93.04945055 -2.95% 14 41.65 114.4230769 22.97% 15 41.9 115.1098901 0.60% 16 36.45 100.1373626 -13.01% 17 37.27 102.3901099 2.25% 18 35.21 96.73076923 -5.53% 19 34.75 95.46703297 -1.31% 20 31.2 85.71428571 -10.22% 21 32.15 88.32417582 3.04% 22 34.1 93.68131868 6.07% 23 36.33 99.80769231 6.54% 24 34.1 93.68131868 -6.14% 25 36.65 100.6868132 7.48% 26 33.74 92.69230769 -7.94% 27 29.79 81.84065934 -11.71% 28 28.9 79.3956044 -2.99% 29 23.3 64.01098901 -19.38% 30 19.95 54.80769231 -14.38% 31 21.98 60.38461538 10.18% 32 18.15 49.86263736 -17.42% 33 18.9 51.92307692 4.13% 34 19.05 52.33516484 0.79% 35 16.94 46.53846154 -11.08% 36 16.94 46.53846154 0.00% 37 18.51 50.85164835 9.27% 38 18.96 52.08791209 2.43% 39 16.72 45.93406593 -11.81% 40 14.32 39.34065934 -14.35% 41 14.52 39.89010989 1.40% 42 13.8 37.91208791 -4.96% 43 13.93 38.26923077 0.94% 44 15.87 43.5989011 13.93% 45 16.15 44.36813187 1.76% 46 15.1 41.48351648 -6.50% 47 13.31 36.56593407 -11.85% 48 12.16 33.40659341 -8.64% 49 12.06 33.13186813 -0.82% 50 13.44 36.92307692 11.44% 51 13.75 37.77472527 2.31% 52 12.47 34.25824176 -9.31% 53 14.67 40.3021978 17.64% 54 15.8 43.40659341 7.70% 55 14.09 38.70879121 -10.82% 56 14.12 38.79120879 0.21% 57 14.01 38.48901099 -0.78% 58 16.08 44.17582418 14.78% 59 16.08 44.17582418 0.00% 60 (Yahoo Finance, 2014d).   Market Index Month Value Index of Index Monthly Performance 1 825.88 100 0 2 735.09 89.006878 -10.99% 3 797.87 96.608466 8.54% 4 872.81 105.68242 9.39% 5 919.14 111.2922 5.31% 6 919.32 111.31399 0.02% 7 987.48 119.56701 7.41% 8 1,020.62 123.5797 3.36% 9 1,057.08 127.99438 3.57% 10 1,036.19 125.46496 -1.98% 11 1,095.63 132.66213 5.74% 12 1,115.10 135.01962 1.78% 13 1,073.87 130.02736 -3.70% 14 1,104.49 133.73493 2.85% 15 1,169.43 141.59805 5.88% 16 1,186.69 143.68794 1.48% 17 1,089.41 131.90899 -8.20% 18 1,030.71 124.80142 -5.39% 19 1,101.60 133.385 6.88% 20 1,049.33 127.05599 -4.74% 21 1,141.20 138.17988 8.76% 22 1,183.26 143.27263 3.69% 23 1,180.55 142.9445 -0.23% 24 1,257.64 152.27878 6.53% 25 1,286.12 155.72722 2.26% 26 1,327.22 160.70373 3.20% 27 1,325.83 160.53543 -0.10% 28 1,363.61 165.10994 2.85% 29 1,345.20 162.88081 -1.35% 30 1,320.64 159.90701 -1.83% 31 1,292.28 156.4731 -2.15% 32 1,218.89 147.58682 -5.68% 33 1,131.42 136.99569 -7.18% 34 1,253.30 151.75328 10.77% 35 1,246.96 150.98562 -0.51% 36 1,257.60 152.27394 0.85% 37 1,312.41 158.9105 4.36% 38 1,365.68 165.36059 4.06% 39 1,408.47 170.54173 3.13% 40 1,397.91 169.26309 -0.75% 41 1,310.33 158.65864 -6.27% 42 1,362.16 164.93437 3.96% 43 1,379.32 167.01216 1.26% 44 1,406.58 170.31288 1.98% 45 1,440.67 174.4406 2.42% 46 1,412.16 170.98852 -1.98% 47 1,416.18 171.47527 0.28% 48 1,426.19 172.68732 0.71% 49 1,498.11 181.3956 5.04% 50 1,514.68 183.40195 1.11% 51 1,569.19 190.00218 3.60% 52 1,597.57 193.43851 1.81% 53 1,630.74 197.45484 2.08% 54 1,606.28 194.49315 -1.50% 55 1,685.73 204.11319 4.95% 56 1,632.97 197.72485 -3.13% 57 1,681.55 203.60706 2.97% 58 1,756.54 212.68707 4.46% 59 1,805.81 218.65283 2.80% 60 1,848.36 223.80491 2.36% (Yahoo Finance, 2014b) From the above table we can see that the monthly price performance of Stock A, Stock B, Stock C and S&P 500. It has been calculated by substituting the current month price minus previous month price and the whole amount is divided by previous month. The average return from stock A is 6.15%, Stock B is 5.96%, Stock C is -0.99% and S&P 500 is 1.47%. The standard deviation from returns from stock A is 20.12%, Stock B is 20.43%, Stock C is 9% and S&P 500 is 4%. The correlation between returns of Stock A&B is -0.0080, Stock B&S&P is -6.82%, Stock A&S&P is 16.04% and Stock C& S&P is 0.2128. Assignment 2 In the assignment 2 we have done pair wise combination of Stock A with B, Stock B with S&P, Stock A and S&P, Stock C and S&P Index. We have calculated expected return from the portfolio and standard deviation of portfolio by using 5% increment method and we have prepared 21 sets of data for the each of the combinations. We have also determined the optimal risky portfolio and minimum variance portfolio for each of the three combinations.   Combination of Stock A&B Weight (%) in Stock A Portfolio Expected Return Portfolio Std Dev 0% 5.96% 20.43% 5% 5.97% 19.43% 10% 5.98% 18.48% 15% 5.99% 17.60% 20% 6.00% 16.80% 25% 6.01% 16.09% 30% 6.02% 15.48% 35% 6.03% 14.98% 40% 6.04% 14.61% 45% 6.05% 14.37% 50% 6.05% 14.28% 55% 6.06% 14.33% 60% 6.07% 14.52% 65% 6.08% 14.86% 70% 6.09% 15.32% 75% 6.10% 15.89% 80% 6.11% 16.58% 85% 6.12% 17.35% 90% 6.13% 18.21% 95% 6.14% 19.13% 100% 6.15% 20.12%   Combination of Stock B& S&P Weight (%) in Stock B Portfolio Expected Return Portfolio Standard Deviation 0% 1.47% 4.39% 5% 1.70% 4.45% 10% 1.92% 4.73% 15% 2.14% 5.19% 20% 2.37% 5.80% 25% 2.59% 6.50% 30% 2.82% 7.28% 35% 3.04% 8.11% 40% 3.27% 8.98% 45% 3.49% 9.87% 50% 3.72% 10.79% 55% 3.94% 11.72% 60% 4.16% 12.66% 65% 4.39% 13.61% 70% 4.61% 14.57% 75% 4.84% 15.54% 80% 5.06% 16.51% 85% 5.29% 17.48% 90% 5.51% 18.46% 95% 5.74% 19.44% 100% 5.96% 20.43%   Combination of Stock S&P & A Weight (%) in S&P Index Portfolio Expected Return Portfolio Standard Deviation 0% 6.15% 20.12% 5% 5.91% 19.10% 10% 5.68% 18.09% 15% 5.45% 17.07% 20% 5.21% 16.06% 25% 4.98% 15.06% 30% 4.75% 14.06% 35% 4.51% 13.06% 40% 4.28% 12.08% 45% 4.04% 11.11% 50% 3.81% 10.15% 55% 3.58% 9.21% 60% 3.34% 8.30% 65% 3.11% 7.42% 70% 2.87% 6.58% 75% 2.64% 5.82% 80% 2.41% 5.16% 85% 2.17% 4.63% 90% 1.94% 4.31% 95% 1.71% 4.22% 100% 1.47% 4.39%   Combination of Stock C & S&P Weight (%) in Stock C Portfolio Expected Return Portfolio Standard Deviation 0% 1.47% 4.39% 5% 1.35% 4.28% 10% 1.23% 4.23% 15% 1.10% 4.21% 20% 0.98% 4.25% 25% 0.86% 4.34% 30% 0.73% 4.46% 35% 0.61% 4.63% 40% 0.49% 4.84% 45% 0.37% 5.07% 50% 0.24% 5.33% 55% 0.12% 5.62% 60% 0.00% 5.93% 65% -0.13% 6.25% 70% -0.25% 6.59% 75% -0.37% 6.94% 80% -0.49% 7.31% 85% -0.62% 7.68% 90% -0.74% 8.06% 95% -0.86% 8.44% 100% -0.99% 8.84% We have calculated the return from optimal risky portfolio and average return and standard deviation.   "Optimal" Risky Portfolio Stock A Stock B Period       1 -4.70% -16.32% 0.28% 2 12.21% -2.31% 18.43% 3 5.33% 4.77% 5.57% 4 -7.00% 7.01% -13.00% 5 21.94% 61.77% 4.87% 6 -2.98% -24.62% 6.29% 7 -3.34% -9.50% -0.71% 8 3.81% -0.87% 5.82% 9 15.88% 16.16% 15.77% 10 6.62% -0.54% 9.69% 11 -1.47% 9.20% -6.04% 12 8.09% -3.35% 13.00% 13 3.28% -3.30% 6.10% 14 4.50% -12.18% 11.64% 15 23.46% -1.41% 34.12% 16 14.80% 20.42% 12.39% 17 1.87% 11.47% -2.25% 18 -7.76% -12.77% -5.61% 19 16.21% 1.79% 22.40% 20 24.91% 14.93% 29.19% 21 8.36% 11.46% 7.04% 22 9.73% -11.04% 18.63% 23 -13.55% -10.96% -14.67% 24 17.11% 6.07% 21.84% 25 -6.13% -12.37% -3.46% 26 11.74% 4.01% 15.05% 27 -0.70% 2.66% -2.15% 28 10.29% -3.93% 16.39% 29 3.97% 20.23% -2.99% 30 0.92% 0.15% 1.26% 31 -4.93% 10.75% -11.65% 32 -38.40% -7.14% -51.80% 33 -16.64% 8.79% -27.54% 34 -2.22% 42.49% -21.38% 35 29.48% 81.07% 7.38% 36 54.38% 9.82% 73.47% 37 2.01% 25.07% -7.88% 38 10.48% 25.86% 3.89% 39 -16.91% 14.42% -30.34% 40 -19.77% -17.29% -20.84% 41 -0.55% -20.42% 7.96% 42 -6.44% 18.19% -17.00% S&P Index Stock C "Optimal" Risky Portfolio Period       1 -10.99% -3.16% -8.64% 2 8.54% 3.55% 7.04% 3 9.39% -7.81% 4.23% 4 5.31% 0.09% 3.74% 5 0.02% 2.35% 0.72% 6 7.41% -3.92% 4.01% 7 3.36% 1.30% 2.74% 8 3.57% -5.90% 0.73% 9 -1.98% -0.51% -1.54% 10 5.74% -2.01% 3.41% 11 1.78% -3.12% 0.31% 12 -3.70% 17.04% 2.52% 13 2.85% -2.95% 1.11% 14 5.88% 22.97% 11.01% 15 1.48% 0.60% 1.21% 16 -8.20% -13.01% -9.64% 17 -5.39% 2.25% -3.10% 18 6.88% -5.53% 3.16% 19 -4.74% -1.31% -3.71% 20 8.76% -10.22% 3.06% 21 3.69% 3.04% 3.49% 22 -0.23% 6.07% 1.66% 23 6.53% 6.54% 6.53% 24 2.26% -6.14% -0.26% 25 3.20% 7.48% 4.48% 26 -0.10% -7.94% -2.46% 27 2.85% -11.71% -1.52% 28 -1.35% -2.99% -1.84% 29 -1.83% -19.38% -7.09% 30 -2.15% -14.38% -5.82% 31 -5.68% 10.18% -0.92% 32 -7.18% -17.42% -10.25% 33 10.77% 4.13% 8.78% 34 -0.51% 0.79% -0.12% 35 0.85% -11.08% -2.73% 36 4.36% 0.00% 3.05% 37 4.06% 9.27% 5.62% 38 3.13% 2.43% 2.92% 39 -0.75% -11.81% -4.07% 40 -6.27% -14.35% -8.69% 41 3.96% 1.40% 3.19% 42 1.26% -4.96% -0.61% 43 1.98% 0.94% 1.67% 44 2.42% 13.93% 5.87% 45 -1.98% 1.76% -0.86% 46 0.28% -6.50% -1.75% 47 0.71% -11.85% -3.06% 48 5.04% -8.64% 0.94% 49 1.11% -0.82% 0.53% 50 3.60% 11.44% 5.95% 51 1.81% 2.31% 1.96% 52 2.08% -9.31% -1.34% 53 -1.50% 17.64% 4.24% 54 4.95% 7.70% 5.77% 55 -3.13% -10.82% -5.44% 56 2.97% 0.21% 2.15% 57 4.46% -0.78% 2.89% 58 2.80% 14.78% 6.40% 59 2.36% 0.00% 1.65% We have calculated the return by multiplying the monthly price performance of Stock A with 30% and multiplying the monthly price performance of Stock B with 70% as the point of Tangency takes place at Std Dev of 4.46%, 14.06% and Expected Return of -0.13%, 3.11% per month, which creates a portfolio mix of 70% S&P Index, 30% C. Similarly we have calculated the return by multiplying the monthly price performance of Stock C with 30% and multiplying the monthly price performance of S&P Index with 70% as the point of Tangency takes place at Std Dev of 15.48%, 7.28% and Expected Return of 6.08%, 4.39% per month, which occurs with a portfolio mix of 70% B, 30%. Average of the return from the two optimal risky portfolios are 4%, 0.73% and standard deviation is 15.11%, 4.46% (University of Missouri, No date). Assignment 3 Here we have plotted the monthly return from each stock against the return from the optimal risky portfolio in scatter diagram. We have also plotted a line of linear regression through these points. We have calculated the indication of systematic risk of a company which is Beta and indication of unsystematic risk of a company which is known as Alpha for each of the company. If beta is equal to 1 that means the company has less systematic risk, if Beta is greater than 1 that means the company has high systematic risk and if Beta is less than 1 then the company has no systematic risk in the market (Georgia State University, No date).   "Optimal" Risky Portfolio Stock A Stock B Alpha 0.40% 4.06% -1.74% Beta 1 0.522499354 1.204643134 "Optimal" Risky Portfolio Stock C S&P Index Alpha 0.40% -2.06% 0.88% Beta 1 1.465573955 0.800468305 From the above table we can see that the Tesla motors which is stock A has no risk as the Beta is less than one but Netflix which is stock B has higher systematic risk in the market as the Beta is greater than one, Stock C has the highest risk as the Beta is greater than one and it is also greater than other two stocks. References Yahoo Finance. (2014a). Tesla Motors, Inc. (TSLA). Retrieved from http://finance.yahoo.com/q/hp?s=TSLA&a=05&b=1&c=2009&d=11&e=31&f=2013&g=m. Yahoo Finance. (2014b). S&P 500 (^GSPC). Retrieved from http://finance.yahoo.com/q/hp?s=%5EGSPC&a=00&b=1&c=2009&d=11&e=31&f=2013&g=m. Yahoo Finance. (2014c). Netflix, Inc. (NFLX). Retrieved from http://finance.yahoo.com/q/hp?s=NFLX&a=00&b=1&c=2009&d=11&e=31&f=2013&g=m. Yahoo Finance. (2014d). Nintendo Co. Ltd. (NTDOY). Retrieved from http://finance.yahoo.com/q/hp?s=NTDOY+Historical+Prices. NASDAQ. (2014). Historical Stock Prices. Retrieved from http://www.nasdaq.com/symbol/tsla/historical. University of Missouri. (No data). Optimal Risky Portfolios. Retreived from http://business.missouri.edu/yanx/fin333/lectures/Riskyportfolio%20short.pdf. Georgia State University. (No data). CHAPTER 7: OPTIMAL RISKY PORTFOLIOS. Retrieved from http://www2.gsu.edu/~fncitt/files/SolCh07.pdf. Read More