Excel project
Lesia
INVPro21SPBKC.xlsxRead Me First!
Project A completed assignment with good effort will receive full credit. Incomplete attempts will lead to point deductions. (100points)
Question 1
| Monthly Returns | |||||||||||||
| Date | GE | HD | PG | ||||||||||
| 8/1/97 | -0.1137715878 | -0.0541395148 | -0.133441038 | ||||||||||
| 9/2/97 | 0.0881428493 | 0.1005412292 | 0.0368967003 | GE | HD | PG | |||||||
| 10/1/97 | -0.0521760955 | 0.067252227 | -0.0122657925 | Mean | |||||||||
| 11/3/97 | 0.1336738326 | 0.0057904041 | 0.1138574594 | Variance | |||||||||
| 12/1/97 | -0.0024956339 | 0.0495596909 | 0.0464198905 | Standard Deviation | |||||||||
| 1/2/98 | 0.0549348318 | 0.0271019298 | -0.0151334607 | ||||||||||
| 2/2/98 | 0.0033057881 | 0.0546299675 | 0.0796907561 | ||||||||||
| 3/2/98 | 0.1067739435 | 0.0575521478 | -0.005931383 | Covariance between GE and HD: | |||||||||
| 4/1/98 | -0.011506626 | 0.0301341068 | -0.023213537 | Covariance between HD and PG: | |||||||||
| 5/1/98 | -0.0216646255 | 0.1200480423 | 0.0203766557 | Covariance between PG and GE: | |||||||||
| 6/1/98 | 0.0859888073 | 0.056381111 | 0.0823128562 | ||||||||||
| 7/1/98 | -0.0125380813 | 0.0081317002 | -0.1342044156 | ||||||||||
| 8/3/98 | -0.1113932376 | -0.0937302485 | -0.0368775598 | Three-Asset Portfolio | A | B | C | ||||||
| 9/1/98 | -0.0018214941 | 0.0361861065 | -0.0727476388 | Weight in GE | 0.2500 | 0.333 | 0.6 | ||||||
| 10/1/98 | 0.0951444248 | 0.0979804084 | 0.2240112882 | Weight in HD | 0.3333 | 0.333 | 0.3 | ||||||
| 11/2/98 | 0.0322148143 | 0.1326513759 | -0.0120823057 | Weight in PG | 0.4167 | 0.333 | 0.1 | ||||||
| 12/1/98 | 0.1243602525 | 0.2075177226 | 0.0412757003 | Total Weights | 1 | 1 | 1 | ||||||
| 1/4/99 | 0.027954301 | -0.0111318404 | -0.0015564205 | ||||||||||
| 2/1/99 | -0.0447494073 | -0.0136859777 | -0.0151716224 | Portfolio mean | |||||||||
| 3/1/99 | 0.1016780681 | 0.0425821306 | 0.0901692744 | Portfolio Variance | |||||||||
| 4/1/99 | -0.0486925127 | -0.0409622598 | -0.0400495729 | Portfolio Standard Deviation | |||||||||
| 5/3/99 | -0.035481983 | -0.0548918276 | -0.0047744782 | ||||||||||
| 6/1/99 | 0.1054313347 | 0.1312132333 | -0.0578105749 | Sharpe Ratio | |||||||||
| 7/1/99 | -0.0330447948 | -0.0097953816 | 0.0307473965 | Risk-Free Rate | 0.21% | 0.21% | 0.21% | ||||||
| 8/2/99 | 0.0298529631 | -0.0362367798 | 0.0916804118 | ||||||||||
| 9/1/99 | 0.0571584138 | 0.1094609693 | -0.057088967 | ||||||||||
| 10/1/99 | 0.1334936504 | 0.0988651525 | 0.1153355987 | ||||||||||
| 11/1/99 | -0.0404367759 | 0.0450798105 | 0.0294138852 | ||||||||||
| 12/1/99 | 0.1758817921 | 0.2640971642 | 0.0143887375 | ||||||||||
| 1/3/00 | -0.143935383 | -0.1938997913 | -0.0780191701 | ||||||||||
| 2/1/00 | -0.0120628724 | 0.0152215661 | -0.1398080476 | ||||||||||
| 3/1/00 | 0.1647279226 | 0.1155840928 | -0.4372480149 | ||||||||||
| 4/3/00 | 0.0102401623 | -0.132321672 | 0.0562685818 | ||||||||||
| 5/1/00 | 0.0040668831 | -0.1424300159 | 0.1069889911 | ||||||||||
| 6/1/00 | 0.0069655377 | 0.0199183896 | -0.1587521383 | ||||||||||
| 7/3/00 | -0.0224168789 | 0.035586448 | 0.0105692041 | ||||||||||
| 8/1/00 | 0.1261190566 | -0.0731536522 | 0.0815035829 | ||||||||||
| 9/1/00 | -0.0117767859 | 0.0966649059 | 0.0801860501 | ||||||||||
| 10/2/00 | -0.0532732374 | -0.2079036333 | 0.0687864518 | ||||||||||
| 11/1/00 | -0.1007753235 | -0.0919080135 | 0.0470453637 | ||||||||||
| 12/1/00 | -0.0299901557 | 0.1536441041 | 0.046687114 | ||||||||||
| 1/2/01 | -0.0416100126 | 0.0532791604 | -0.083190933 | ||||||||||
| 2/1/01 | 0.0111988954 | -0.1257693873 | -0.0189259859 | ||||||||||
| 3/1/01 | -0.1016832669 | 0.0149740756 | -0.118836051 | ||||||||||
| 4/2/01 | 0.1479271348 | 0.0888262595 | -0.0352606579 | ||||||||||
| 5/1/01 | 0.0096200838 | 0.0454170421 | 0.0672030829 | ||||||||||
| 6/1/01 | 0 | -0.0417971287 | -0.0067340322 | ||||||||||
| 7/2/01 | -0.1158402138 | 0.0642844238 | 0.1125536441 | ||||||||||
| 8/1/01 | -0.061515236 | -0.0910625429 | 0.0432214015 | ||||||||||
| 9/4/01 | -0.0902643433 | -0.1802360503 | -0.0184280399 | ||||||||||
| 10/1/01 | -0.0214994054 | -0.0036176469 | 0.018732315 | ||||||||||
| 11/1/01 | 0.0559325643 | 0.2001890426 | 0.0486887494 | ||||||||||
| 12/3/01 | 0.0446225601 | 0.0892339167 | 0.0215000399 | ||||||||||
| 1/2/02 | -0.0759326684 | -0.0181133849 | 0.0364790552 | ||||||||||
| 2/1/02 | 0.0403354403 | -0.0019146905 | 0.0373047416 | ||||||||||
| 3/1/02 | -0.0291725336 | -0.0269790832 | 0.0605626351 | ||||||||||
| 4/1/02 | -0.1700018276 | -0.047259942 | 0.0061797255 | ||||||||||
| 5/1/02 | -0.0128749249 | -0.1063804036 | -0.0079169142 | ||||||||||
| 6/3/02 | -0.0634284378 | -0.1254012665 | 0.0049554115 | ||||||||||
| 7/1/02 | 0.1029908463 | -0.1735687657 | 0.0014818476 | ||||||||||
| 8/1/02 | -0.0658204813 | 0.0642804899 | -0.0037087445 | ||||||||||
| 9/3/02 | -0.1944818535 | -0.2308343401 | 0.0081411576 | ||||||||||
| 10/1/02 | 0.0241744954 | 0.1011664423 | -0.0059142607 | ||||||||||
| 11/1/02 | 0.0713035542 | -0.0898531903 | -0.0452497261 | ||||||||||
| 12/2/02 | -0.1001055451 | -0.0921011919 | 0.0164148338 | ||||||||||
| 1/2/03 | -0.0509027838 | -0.1392114331 | 0.000508647 | ||||||||||
| 2/3/03 | 0.046253775 | 0.1151353705 | -0.0444517626 | ||||||||||
| 3/3/03 | 0.0588138721 | 0.0406775798 | 0.0843274666 | ||||||||||
| 4/1/03 | 0.1438035844 | 0.1441978731 | 0.0135891438 | ||||||||||
| 5/1/03 | -0.0258252934 | 0.1440256472 | 0.0216959279 | ||||||||||
| 6/2/03 | 0.005840001 | 0.0208910056 | -0.0291956606 | ||||||||||
| 7/1/03 | -0.0081855846 | -0.0596311587 | -0.0097609365 | ||||||||||
| 8/1/03 | 0.038769008 | 0.0302599979 | -0.0066429079 | ||||||||||
| 9/2/03 | 0.0142058464 | -0.0075620942 | 0.0615099951 | ||||||||||
| 10/1/03 | -0.0270896926 | 0.1518850317 | 0.0618800462 | ||||||||||
| 11/3/03 | -0.0118934838 | -0.0085421932 | -0.0209466373 | ||||||||||
| 12/1/03 | 0.0843383106 | -0.0331425683 | 0.0371790032 | ||||||||||
| 1/2/04 | 0.0820327038 | -0.0005913661 | 0.0163955552 | ||||||||||
| 2/2/04 | -0.0274988339 | 0.0233860409 | 0.0140507712 | ||||||||||
| 3/1/04 | -0.0634418212 | 0.0304476501 | 0.0228522554 | ||||||||||
| 4/1/04 | -0.0187866797 | -0.059766666 | 0.0135451379 | ||||||||||
| 5/3/04 | 0.0382804744 | 0.0203217124 | 0.0188966339 | ||||||||||
| 6/1/04 | 0.0462966268 | -0.0176475168 | 0.0098040001 | ||||||||||
| 7/1/04 | 0.0258036149 | -0.043049994 | -0.0383859397 | ||||||||||
| 8/2/04 | -0.0138114658 | 0.0831833241 | 0.0708295185 | ||||||||||
| 9/1/04 | 0.0296873865 | 0.069638805 | -0.0336149963 | ||||||||||
| 10/1/04 | 0.0159442153 | 0.0470033292 | -0.0510979628 | ||||||||||
| 11/1/04 | 0.0357347328 | 0.0181000416 | 0.0438437402 | ||||||||||
| 12/1/04 | 0.03744634 | 0.0233914395 | 0.0294709192 | ||||||||||
| 1/3/05 | -0.0100473658 | -0.0351695484 | -0.0296677115 | ||||||||||
| 2/1/05 | -0.0197966873 | -0.0304566087 | -0.0025618302 | ||||||||||
| 3/1/05 | 0.0239460709 | -0.0430116736 | -0.0017774271 | ||||||||||
| 4/1/05 | 0.0038376431 | -0.0781228661 | 0.0267202635 | ||||||||||
| 5/2/05 | 0.0079237392 | 0.1067382696 | 0.0183071474 | ||||||||||
| 6/1/05 | -0.0454487783 | -0.0090042981 | -0.044632154 | ||||||||||
| 7/1/05 | -0.0042918521 | 0.1118834884 | 0.0583322839 | ||||||||||
| 8/1/05 | -0.0261452801 | -0.0735226206 | -0.0026129168 | ||||||||||
| 9/1/05 | 0.0084786189 | -0.0558039507 | 0.0691299081 | ||||||||||
| 10/3/05 | 0.0071662559 | 0.0733148663 | -0.0550268724 | ||||||||||
| 11/1/05 | 0.0517278476 | 0.0204190361 | 0.0211493754 | ||||||||||
| 12/1/05 | -0.0115642504 | -0.0315499378 | 0.0120148491 | ||||||||||
| 1/3/06 | -0.0678644555 | 0.0015251655 | 0.0279480357 | ||||||||||
| 2/1/06 | 0.011109462 | 0.0388591855 | 0.0117182109 | ||||||||||
| 3/1/06 | 0.0564567087 | 0.007060285 | -0.0391316303 | ||||||||||
| 4/3/06 | -0.0056843833 | -0.0576722885 | 0.0155562621 | ||||||||||
| 5/1/06 | -0.0093444538 | -0.0462875158 | -0.0704849506 | ||||||||||
| 6/1/06 | -0.0313775357 | -0.0590444443 | 0.0245411089 | ||||||||||
| 7/3/06 | -0.0081581877 | -0.0307359654 | 0.0163938098 | ||||||||||
| 8/1/06 | 0.0410488119 | -0.012146498 | 0.0965412522 | ||||||||||
| 9/1/06 | 0.0429123392 | 0.0604317392 | 0.0013114756 | ||||||||||
| 10/2/06 | -0.0055192587 | 0.0287630448 | 0.0274698158 | ||||||||||
| 11/1/06 | 0.0049397166 | 0.0231753838 | -0.0094483848 | ||||||||||
| 12/1/06 | 0.0607269184 | 0.0559441565 | 0.0232198727 | ||||||||||
| 1/3/07 | -0.0318718955 | 0.0142555872 | 0.0140495599 | ||||||||||
| 2/1/07 | -0.0242280627 | -0.0284618129 | -0.0214657674 | ||||||||||
| 3/1/07 | 0.0128997059 | -0.0690111212 | -0.0052401867 | ||||||||||
| 4/2/07 | 0.0415622502 | 0.0304646824 | 0.0249993366 | ||||||||||
| 5/1/07 | 0.0192131468 | 0.0259482518 | -0.0132866355 | ||||||||||
| 6/1/07 | 0.0256661794 | 0.0179491998 | -0.0378432202 | ||||||||||
| 7/2/07 | 0.0067690964 | 0.0121244223 | 0.0078138115 |
Portfolio Mathematics 1) To the left are the returns for three stocks. Calculate the mean, variance, standard deviation for each return series in the appropriates spaces. 2) Find the covariance between stocks. 3) Find the mean, variance, and standard deviation for the return of three portfolios A,B, and C. 4) ) Given the risk free rate of 0.21%, figure out the Sharpe ratio and find the best portfolio.
Question2
| CALCULATING PORTFOLIO RETURNS AND THEIR STATISTICS FROM THE FORMULAS | ||||||||
| Stock A | Stock B | Stock C | Stock D | |||||
| Mean | 8.00% | 11.00% | Mean | 10.00% | 12.00% | |||
| Variance | 1.71% | 2.67% | Variance | 2.00% | 3.00% | |||
| Standard deviation | 13.08% | 16.34% | Standard deviation | 14.14% | 17.32% | |||
| Correlation | 0.0936 | Covariance | -0.0020 | |||||
| Proportion of stocok A in portfolio | Portfolio Variance | Portfolio standard deviation | Portfolio mean | Proportion of stocok A in portfolio | Portfolio Variance | Portfolio standard deviation | Portfolio mean | |
| 0% | 0% | |||||||
| 10% | 10% | |||||||
| 20% | 20% | |||||||
| 30% | 30% | |||||||
| 40% | 40% | |||||||
| 50% | 50% | |||||||
| 60% | 60% | |||||||
| 70% | 70% | |||||||
| 80% | 80% | |||||||
| 90% | 90% | |||||||
| 100% | 100% | |||||||
Portfolio Mean and Standard Deviation
Portfolio mean E(rp) 0.16338984890563837 0.1520740276863676 0.14190069943787215 0.13313203422924555 0.12606149296249491 0.12098716010678888 0.11816648106097953 0.11776150905461583 0.11979674543226272 0.12415224061756119 0.1305960524572459 0.11712562749149602 0.11140825798511036 0.10569088847872472 9.9973518972339054E-2 9.4256149465953412E-2 8.8538779959567757E-2 8.2821410453182115E-2 7.7104040946796459E-2 7.1386671440410804E-2 6.5669301934025148E-2 5.99519324276395E-2Portfolio standard deviation sp
Portfolio expected return E(rp)
1) Fill out the table for the mean, variance, and standard deviation for each portfolio 2) Draw a plot for the relationship between risk and return for each portfolio, as shown below. 3) Figure out the minimum-variance portfolio for each investment opportunity set.
Question 3
| Monthly Returns | |||
| Date | Market Return | Google Return | Risk-Free Rate |
| 20050131 | -0.026557 | 0.014679 | 0.0016 |
| 20050228 | 0.022695 | -0.039004 | 0.0016 |
| 20050331 | -0.01694 | -0.039789 | 0.0021 |
| 20050429 | -0.025182 | 0.218769 | 0.0021 |
| 20050531 | 0.037916 | 0.260318 | 0.0024 |
| 20050630 | 0.011532 | 0.060879 | 0.0023 |
| 20050729 | 0.043332 | -0.021724 | 0.0024 |
| 20050831 | -0.005942 | -0.006116 | 0.003 |
| 20050930 | 0.010581 | 0.106503 | 0.0029 |
| 20051031 | -0.020786 | 0.175946 | 0.0027 |
| 20051130 | 0.040407 | 0.088065 | 0.0031 |
| 20051230 | 0.003477 | 0.024567 | 0.0032 |
| 20060131 | 0.040113 | 0.042906 | 0.0035 |
| 20060228 | -0.001644 | -0.161883 | 0.0034 |
| 20060331 | 0.019058 | 0.075506 | 0.0037 |
| 20060428 | 0.012969 | 0.071641 | 0.0036 |
| 20060531 | -0.031042 | -0.110351 | 0.0043 |
| 20060630 | -0.000395 | 0.127777 | 0.004 |
| 20060731 | -0.001899 | -0.078053 | 0.004 |
| 20060831 | 0.025083 | -0.020874 | 0.0042 |
| 20060929 | 0.019452 | 0.061739 | 0.0041 |
| 20061031 | 0.037086 | 0.185345 | 0.0041 |
| 20061130 | 0.023717 | 0.017675 | 0.0042 |
| 20061229 | 0.010822 | -0.050185 | 0.004 |
| 20070131 | 0.019439 | 0.089081 | 0.0044 |
| 20070228 | -0.013999 | -0.103789 | 0.0038 |
| 20070330 | 0.012947 | 0.019379 | 0.0043 |
| 20070430 | 0.039891 | 0.028855 | 0.0044 |
| 20070531 | 0.038898 | 0.056279 | 0.0041 |
| 20070629 | -0.014759 | 0.04979 | 0.004 |
| 20070731 | -0.031753 | -0.024297 | 0.004 |
| 20070831 | 0.011624 | 0.010294 | 0.0042 |
| 20070928 | 0.040903 | 0.100961 | 0.0032 |
| 20071031 | 0.025839 | 0.24632 | 0.0032 |
| 20071130 | -0.049279 | -0.019802 | 0.0034 |
| 20071231 | -0.004343 | -0.002193 | 0.0027 |
| 20080131 | -0.062306 | -0.183924 | 0.0021 |
| 20080229 | -0.02204 | -0.165019 | 0.0013 |
| 20080331 | -0.010471 | -0.065177 | 0.0017 |
| 20080430 | 0.051152 | 0.303812 | 0.0017 |
| 20080530 | 0.023825 | 0.020042 | 0.0017 |
| 20080630 | -0.078627 | -0.101366 | 0.0017 |
| 20080731 | -0.013157 | -0.100053 | 0.0015 |
| 20080829 | 0.011044 | -0.022079 | 0.0012 |
| 20080930 | -0.09806 | -0.135488 | 0.0015 |
| 20081031 | -0.184721 | -0.102766 | 0.0008 |
| 20081128 | -0.085201 | -0.184773 | 0.0002 |
| 20081231 | 0.021471 | 0.050143 | 0.0009 |
| 20090130 | -0.077444 | 0.100374 | 0 |
| 20090227 | -0.100963 | -0.001595 | 0.0001 |
| 20090331 | 0.087698 | 0.029794 | 0.0001 |
| 20090430 | 0.110553 | 0.137649 | 0.0001 |
| 20090529 | 0.067336 | 0.053691 | 0 |
| 20090630 | -0.002779 | 0.01045 | 0 |
| 20090731 | 0.082454 | 0.050903 | 0.0001 |
| 20090831 | 0.031887 | 0.042027 | 0.0001 |
| 20090930 | 0.045244 | 0.074036 | 0 |
| 20091030 | -0.028415 | 0.081214 | 0 |
| 20091130 | 0.057381 | 0.087443 | 0 |
| 20091231 | 0.029165 | 0.06343 | 0 |
The CAPM Method for the expected return I have given you monthly returns for the stock market, Google, and the three-month treasury (risk-free rate). 1) Run the regression to estimate the beta of Google and report the ouput in a new worksheet. 2)Draw the security characteristic line equation (SCL) using the regression output and write the SCL. 3) Suppose that the risk free rate is 0.15% and the market expected return is 0.50%. Using the beta from Question 1), figure out the expected return for Google based on the CAPM framework.
