investment finance

Getting Historical Stock Quotes: https://www.youtube.com/watch?v=ADZuXzXJsSw

This short video demonstrates one method of getting historical stock quotes by downloading data for FREE.  ("Free is GOOD!")  There are other sources, as well.

You will not need this process for this assignment as all of the data you should use is provided.  But some day you may wish to download this data for yourself, and this might be of interest to you.

The data for this particular project was developed using the NYSE Trades and Quotes ("TAQ") database.

The Optimal Portfolio Calculator

Attached Files:

·  Portfolio Optimum Combination Problem 6.9-6.11.xls (55 KB)

Excel File that we used in Chapter 6 I was asked to re-post this Excel file that was demonstrated in class.

Project #2

Attached Files:

·  projdata.xls (110 KB)

·  sample.xls (46.5 KB)

To Submit, Click on the above link,  the one that says "Project #2" and upload your file through the Assignment link.  (Please submit as a spreadsheet! Show your work!).

PROJECT #2

THIS ASSIGNMENT IS DUE on the last Day of Class.

This assignment is to have you develop an efficient portfolio, using real world data. For help, see especially Chapters 5, 6 and 7.

DOWNLOAD Instructions

Please note that I strongly recommend the use of a statistical package (including Lotus, Excel, or another spreadsheet/data package) due to the sheer volume of the data to be manipulated. Further, you may, nay, I encourage you to, work in groups.

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'sSecurity 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?

Helps and Hints:

· You may notice that the data does not correspond to this year and, in fact, several of these companies have been merged. I have used historical information so that you are not tempted to try to use current events in your analysis. DO NOT be concerned about whatever you may know of what has happened to these stocks, nor of what you may personally believe to be true about them. Pretend that you are making this decision as of the time these data were recorded.

· Pick any two stocks from the list. There is no "correct" pair. Fourteen stocks are provided so you have a choice of stocks which may be of interest to you. You may get more "interesting" results if you choose stocks which would not be highly correlated.

· You will need to calculate the DAILY HOLDING PERIOD RETURNS for the assets you select. Make sure that you are dealing on an annualized basis for returns and variances, since portfolio evaluation is based on RETURNS, not prices.

· Remember, the HPR = (P1/P0)-1. ~ from Chapter 5.

· Find the OPTIMAL portfolio of the two stocks. Remember the formula in Equation 6.10 in the text.

· In the optimal portfolio calculation, the risk free rate you use will be different, depending on whether you are putting less than 100% of your money in the optimal risky portfolio (2%), or if you are buying more than 100% on margin (7%).

· NOTE: In the real world, you would have to adjust for ex-dividend dates and the rate of return implied by dividends. For this exercise, ignore the effects of dividends.

There is a Market Index in the second column of the data file. Consider that to be a proxy for the market, itself.

The Stocks included are:

· AIG: Amer Insurance Group, a diversified insurance and financial services company.

· BA: Boeing Aircraft, a major domestic manufacturer of aircraft.

· C: Chrysler Corp., third largest US maker of automobiles (now part of Daimler/Chrysler.

· CAT: Caterpillar Corp., manufacturer of heavy machinery.

· COL: Columbia/HCA, owner and operator of for-profit hospitals and HMOs.

· COLL: Collins Industries, a small-cap manufacturer of specialty vehicles in the central and southern U.S.

· CPQ: Compaq Computer, market leader in Windows/Intel-Based PCs and servers.

· KM: K-Mart Corp., second largest discount store chain in the U.S.

· LLY: Eli Lilly and Company, maker of pharmaceuticals (including Prozac and insulin).

· MSFT: Microsoft Corp., maker of software for applications and operating systems for Intel-based PCs.

· SLB: Schlumberger, French-based firm which logs and services oil wells and drillers.

· TRV: Traveler's Corp., Hartford based diversified financial services (Now Travelers-Citigroup).

· WMT: WalMart Stores, world's largest retail store chain.

· XON: Exxon, an international petroleum products producer, refiner, distributor, and retailer (Now part of Exxon/Mobil).