calculate optimal portfolios

You may do this assignment individually or with one other person. If you work with another, be sure that both know how to set up the analysis and calculate optimal portfolios.

  1. Prices

Obtain monthly closing prices for five stocks from different industries for the last twelve years. Each stock must have traded publicly for the last twelve (so no recent IPO’s and no stocks with a bankruptcy filing during that period).

  1. Returns

Calculate monthly returns for each stock. (Be sure to account for dividends, e.g., by using the “adjusted closing price” from yahoo finance.)

  1. Average return and risk


(a) the average monthly return for each stock,

(b) the standard deviation of monthly returns for each stock, and

(c) calculate the correlation coefficient for returns on each pair of stocks.

  1. Equally weighted portfolio

Determine the average return and standard deviation for an equally weighted portfolio of the five stocks.

For each of 5-8 use constrained optimization (e.g., the “solver” in excel) to determine the investment in each stock needed to obtain the portfolio described.

For each portfolio, specify:

  • the weight for each stock in the portfolio,
  • the average portfolio return and
  • the portfolio standard deviation.
  1. Lowest risk portfolio

Find the portfolio that has the lowest possible risk (that is, the lowest standard deviation).

  1. Highest return portfolio

Find the portfolio that has the highest average return.

  1. Highest return portfolio with the same risk as the equally weighted portfolio

Find the portfolio that has the highest possible return and the same risk as the equally weighted portfolio (i.e., the same standard deviation as in question 4).

  1. Lowest risk portfolio with a return selected by you

Select a specific monthly return that is between the monthly return on the equally weighted portfolio (i.e., from question 4) and the return on the highest return portfolio (i.e., from question 6). Find the portfolio with the selected return that has the lowest possible risk (standard deviation).

  1. Comment(briefly) on your findings.

(Turn in the spreadsheet you use to do the calculations with the answers to each question highlighted to make them easy to locate.)

Leave a Reply

Your email address will not be published. Required fields are marked *