90.2K

Verified Solution

Link Copied!

The data files consist of:

projdata.xls approx 114K Data loaded into MS-Excel spreadsheet file.

This file contains the closing prices of each of fourteen different stock issues for every trading date in a continuous period. You can download them directly from here by RIGHT-clicking on the filename. DOWNLOAD HELP: For best results, RIGHT-click on the link, a small pop-up menu should appear. Thenm, use your browser's function to "Save target as" or "Save file as." Enter a filename and location. You may wish to save it on a flash drive

There is also a help file:

sample.xls approx 31K An Excel example, using two weeks (only) of data.

which may help. I have created an example of using Excel or Lotus to calculate the holding period returns, annualized returns, standard deviations, and correlation or covariance of the returns for two sample assets over ten days (only), as well as the beta of the asset. NOTE that you have many more days worth of data in your sample for each stock. This is a highly abbreviated set of data just to demonstrate the process.

THE ASSIGNMENT:

Assume that risk free (money market fund) pays 2.0% and that the margin borrowing rate (should you need to borrow) is 7%. Further, the Market Expected Return is 12%.

Your task is to:

Part I

Select any two of these stocks, and calculate the most efficient portfolio (on a mean/variance basis) that you can create by using JUST THOSE TWO ASSETS .

a) Select two stocks - you may DELETE the rest. Delete the columns in the data file so you don't get confused.

b) What is (i.e., calculate) the BETA of each stock?

c) Use the One Factor Index Model (CAPM) to estimate the expected return of each. That is, use CAPM's Security Market Line equation to calculate the required rate of return, and assume that the Market is in equilibrium.

d) What is the weight of each (of your two) stock in the optimal portfolio?

e) What are the characteristics in expected return and in standard deviation of the optimal portfolio (of your two stocks)?

Part II

You have a client who wishes to have a portfolio with a rate of return of (expected) 10%. What mix of two of your two stocks and of money market funds (paying 2%) or margin borrowing (cost = 7%) would you recommend?

DATE Mkt Index ABC XYZ 1000.00 53.6875 54 125 1 1002.03 52.59375 55.4375 10 11.59 53.625 55.4375 3 1028.12 54.53 125 56.3125 1034.20 54.125 57.375 5 1034.78 55.4375 56 125 1016.23 55.4375 54 125 7 1036.52 56.3125 55.4375 8 1046.38 57.375 55.4375 56.125 56.3125 1042.90 10 1045.80 55.375 57.375 Daily Mean Annualized 254 Approx trading days in a year Variance Std Dev Corr Coeff w/MKT rho Corr Coeff w/ABC Corr Coeff w/XYZ Expected Market Return 25.00 Beta 5.006 CAPM Risk Free Rate Daily Returns (Holding Period 1 da Mkt Index ABC XYZ 0.0 (02029 0.020373 0.024249 0.009546 016332 0.016900 015784 0.005920 0.007450 018868 0.000561 0.024249 0.021786 0.000000 0.035635 017927 019966 0.015 784 024249 0.018868 0.009508 0.000000 0.021786 003324 015784 0.013363 002779 0.018868 0.003244 004539 006038 1.152903 0.823889 1.5336 66 0.025921 0.073197 0.0956 62 16.10% 27.05% 30.93% 0.451286 0.6403408 0.4512856 DATE Mkt Index ABC XYZ 1000.00 53.6875 54 125 1 1002.03 52.59375 55.4375 10 11.59 53.625 55.4375 3 1028.12 54.53 125 56.3125 1034.20 54.125 57.375 5 1034.78 55.4375 56 125 1016.23 55.4375 54 125 7 1036.52 56.3125 55.4375 8 1046.38 57.375 55.4375 56.125 56.3125 1042.90 10 1045.80 55.375 57.375 Daily Mean Annualized 254 Approx trading days in a year Variance Std Dev Corr Coeff w/MKT rho Corr Coeff w/ABC Corr Coeff w/XYZ Expected Market Return 25.00 Beta 5.006 CAPM Risk Free Rate Daily Returns (Holding Period 1 da Mkt Index ABC XYZ 0.0 (02029 0.020373 0.024249 0.009546 016332 0.016900 015784 0.005920 0.007450 018868 0.000561 0.024249 0.021786 0.000000 0.035635 017927 019966 0.015 784 024249 0.018868 0.009508 0.000000 0.021786 003324 015784 0.013363 002779 0.018868 0.003244 004539 006038 1.152903 0.823889 1.5336 66 0.025921 0.073197 0.0956 62 16.10% 27.05% 30.93% 0.451286 0.6403408 0.4512856

Answer & Explanation Solved by verified expert

See Answer