56:054 Engineering Economy

Project II - Investment Risk 

This page was last updated on April 25, 2005. Check back for further updates.

Latest Updates:

1. To reduce printing, you need only show a) the middle portion of the spreadsheet  and b) the portion where the calculations on resultant risk are performed.
2. Be especially carefully of using the automatic percentage formatting in Excel. The numbers will appear as percentages but will be stored as decimals (while appearing as 20%, the number is stored as 0.2 using automatic percentage formatting).

Objective 

The objective is to gain experience evaluating a potential for reducing risk by a careful selection of investments.

In this project your task, should you choose to accept it (your grade will self destruct if you don't),  is concerned with an evaluation of the risk associated with investing in different assets (the analysis is similar to that for a company investing in different projects).

An excellent report is expected with evidence of considerable digging for information and use of initiative.

Scope

You will need to:

Content of Report

The report should contain the following sections (remember the page limitations above):

Sections

The sections to include are:

1. Produce an Excel Spreadsheet showing the effect of combining stocks and bonds in different weightings. Assume that the expected return from stocks is 10.5% with a standard deviation of 20.0, while the expected return from bonds is 5.5% with a standard deviation of 8.7. The correlation coefficient between these two asset classes is -0.06.
2. Show in a table the expected return and the standard deviation of different weightings of stocks and bonds from (0.0, 1.0) to (1.0, 0.0) in 0.1 increments (e.g., 0.0 in stocks and 1.0 in bonds; 0.1 in stocks and 0.9 in bonds etc.).
3. Draw a graph showing the risk (s.d.) on the horizontal axis and return on the vertical axis. Label axes etc.
4. Discuss why bonds are usually less risky than stocks and what factors will increase the risk of bonds. You should include a short description of the troubles surrounding "Bowie Bonds".
5. What conclusions can you reach about investing in bonds and stocks? What could make your analysis inaccurate for the period 2005 - 2010?

Select two stocks that you think will have relatively low correlation. Download from Yahoo the historical price of each stock for the past ten years (i.e. from April 1995) from:

http://chart.yahoo.com/t?s=***&g=m
where *** is the symbol for the stock that you select. Use monthly data and use download spreadsheet data. Note that the downloaded data is adjusted for stock splits and dividends. Use the adjusted closing price per month.

Paste both sets of data into a spreadsheet with columns for the increase in price in each month and the % increase each month. Use the data to calculate the mean increase of each stock per month (using the function AVERAGE) and the standard deviation of the return each month (using the function STDEV). You will also need to calculate the correlation coefficient between the sets of % increases per month (using the function CORREL).

You should include

1. A brief description of  the two companies and WHY you selected these two companies (that is, why you think that they would have low correlation).
2. An Excel spreadsheet in an appendix containing the data on the monthly stock prices, the increase per month and the % change per month. To reduce printing, you need only show a) the middle portion of the spreadsheet  and b) the portion where the calculations on resultant risk are performed.
3. A calculation of  the mean increase of each stock per month (using the function AVERAGE), the standard deviation of the return each month (using the function STDEV) and the correlation coefficient between the two sets of % increases per month (using the function CORREL) Show in the spreadsheet,  the expected return and the standard deviation of different weightings of the two stocks from 0.0, 1.0 to 1.0, 0.0 in 0.1 increments. Note that this will show the return per month.
4. Include  a graph showing the risk (s.d.) on the horizontal axis and return on the vertical axis with these different weightings. Identify the minimum risk level. Label axes etc.

Repeat the analysis above with 3 stocks (that is, you add a third company to the analysis done thus far). Note that you will need to use a matrix approach. Note also that you will have difficulty identifying the precise point of minimum risk (you can  use Excel for the calculation and use Solver to find the minimum risk point -- calculating matrices using Excel isn't particularly straightforward, though). 

You should include

1. A brief description of  the third stock and WHY you selected this stock (that is, why you think that it would have low correlation with the other two).
2. An Excel spreadsheet in an appendix containing the data on the monthly stock prices, the increase per month and the % change per month.You need only show the middle portion of the spreadsheet to reduce printing.
3. A calculation of  the mean increase of the third stock per month (using the function AVERAGE), the standard deviation of the return each month (using the function STDEV) (note: you will need to use the square of the standard deviations - i.e. the variance -- in the covariance matrix) and the covariance between the three sets of % increases per month (using the function COVAR) (you will need to calculate three covariances and three variances for the covariance matrix).
4. Another Excel printout in an appendix showing the expected return and the standard deviation of 15 different weightings of the three stocks using a matrix mode of calculation (use the functions MMULT and TRANSPOSE where needed). Note that this will show the return per month. To reduce printing, you need only show a) the middle portion of the spreadsheet  and b) the portion where the calculations on resultant risk are performed.
5. Use Solver to calculate the minimum risk vector of weights and highlight this on the printout.
    
6. Optional: Include  a 3D graph showing the risk (s.d.) on the horizontal axis and return on the vertical axis with different weightings. Label axes etc. (Extra 3%)
    

.

  [ Professor O'Grady ]