Campbell R. Harvey

All students should be familiar with the concept of portfolio control
from the introductory finance course. The idea of this first assignment
is to apply these concepts in developing your own portfolio selection
software. *I want you to develop a program that you could use for your
own investments or to show to perspective clients or employers.*
I recommend that you develop this software in EXCEL
where it is easy to use the quadratic optimizer in SOLVER.

Form groups of four for this project and the other projects. *Group members
should make it clear to other group members at the offset how many assignments
you plan to turn in. Each group needs a name, i.e. Positive Skewness Asset Management.*

When the project is finished, copy it to the class directory on the student drive Harvey_BA453 (see below for file naming convention). I will run the program. It must be very user friendly. Assume the user has no knowledge of Excel.

The basic problem is to maximize expected return, **w′E**,
where **w** is an (n x 1) (rows x colums) vector of portfolio weights and
**E** is an (n x 1) vector of expected returns on n assets,
subject to at least two constraints. [Remember **w′** takes
a (n x 1) vector and transposes it into a (1 x n) vector.]
The first constraint sets the level of
variance **w′ V w**, where **V** is an
(n x n) variance-covariance matrix (variances on the diagonal).
The second constraint forces the weights to sum to unity, ** 1′w**,
where **1** is an (n x 1) vector of ones. Your program should
be general enough to allow for asset specific constraints. For example,
there should be limits to short-selling and long positions that the
user can input before the program is run.

The program requires three sets of data: the means **E**, the
correlation matrix, and the standard deviations (σ).
The your program must convert the correlation matrix into the
variance-covariance matrix. Remember from Finance 350, the covariance between
i and j (C_{ij})is:

C_{ij}=Corr_{ij} × σ_{i} × σ_{j}.

In terms of the correlation matrix,
multiply row1 by σ_{1}, row2 by σ_{2}, and
so on. Then multiply column1 by σ_{1}, column2 by σ_{2}, and so
on.
The diagonal elements are the variances (i.e. σ_{i}^{2}, for each asset i).

You will find it useful to use the vector and matrix commands in EXCEL
to do **w′V w** operation (i.e. MMULT).
You can
add as many options as desired such as a bar graph in the right-hand
corner that graphs the asset weights as SOLVER is working.

To start the exercise, download returns on at least five but not more than eight asset classes (at
least 3 of the assets must be
international). ** One of the assets must be the U.S. equity return**
I have put up on the CD and the student drive a number of files. In particular, look for files with the
preference Ibb (for Ibbotson Associates) or MSCI (for Morgan Stanley Capital International -- to be updated). Feel free to
draw data from DATASTREAM too.
Note you might want to design your program to
take more assets. This will make it easier to do future assignments. Use total return (not price return)
country indices (such as MSCI or IFC for equities and J.P. Morgan or Salomon for bonds, the U.S. equity
return is the S&P 500)
or regional indices.
Decide on your currency of choice; i.e. if you choose the U.S. dollar,
returns from all countries must be converted to U.S. dollars.
Using monthly data, calculate the unconditional
means (average), standard deviations and
the correlations of the returns.

Calculate the standard deviation (converted to
your currency of choice) of the U.S. equity return. Call this σ_{US-equity}.
Also download (if it is not
in your set of assets) a U.S. government bond portfolio total
return series (medium or long term) (in Ibb file)
and calculate its standard deviation. Call this σ_{US-bonds}.

- Optimize the weights for a portfolio standard deviation of
σ
_{US-equity}(exactly the U.S. equity standard deviation). - Compare the expected portfolio return to the strategy of
just holding the U.S. equity portfolio and comment on this.
- Reoptimize with no short-sales allowed. Comment how
this might affect the efficient frontier (that is, assuming the
constraint is binding).
- Reoptimize (not allowing short-sales) with a portfolio standard
deviation of σ
_{US-bond}(exactly the long-term government bond standard deviation). How does the expected return compare with just holding the U.S. bond? [If this standard deviation is not feasible, just use the minimum variance portfolio.] - Reoptimize allowing limited short sales (20%)
and limited long positions (<50%) with a portfolio standard
deviation of σ
_{US-equity}but this time change the expected returns to reflect your subjective assessment the expected asset performance over the next year. Interpret the results. Does the allocation make sense compared to the first allocation?

- Please look at the tutorial for simulating investment returns. In particular, I would install the trial version of XLSim. I loaded it indirectly by Start, Programs, XLSIM2e, XLSim 2.0. Alternatively, you could use it as an add-in. Follow the tutorial (PDF). You need means and a variance-covariance matrix. Suppose you have 4 assets. Hence, the means will have four elements. The variance-covariance matrix will have 16. Highlight four new cells. Use the function wizard, under statistics find, gen_MVNormal. Follow the steps. However, I anchor the cells for the means and variance-covariance, i.e. $A$1:$D$1 for the means. Same for variance-covariance. When done, don't hit OK, hit control shift enter. You can then drag this row of four cells down, say one hundred rows. You have simulated the portfolio returns. You can double check the means and correlations. They should be close but not exactly equal to what you started with. Everytime you hit F9 (or load new data anywhere), a new simulation runs. Here is an example file. Note there are other ways to do this via Crystal Ball, for example.

- I only require a simplifed version of the resampling. Given your five assets, means, variance-covariance matrix, choose a point on the frontier that you are comfortable with. For example, you could choose the expected return that is the average of the maximum and minimum expected frontier return. Present the weights.
- Resample the data (i.e. generate a new set of data). Rerun the optimizer. Solve for the weights that match the expected return (or close to it) that you chose. Alternatively, given my example above, solve for the maximum expected return and the minimum. Take the expected return that is the average. Save the weights.
- Repeat the resampling. Depending on your computer platform this could get painful. Hence, you only need to do this 25 times.
- Average the weights. Each asset should have 25 weights to average. [Make sure the averaged weights sum to one, if not, rescale them.]
- Present a graph of the original data and the original efficient frontier. Mark on the graph the original portfolio you chose. Mark on the graph the resampled average portfolio (i.e. the resampled weights imply a portfolio using the original data that is different from your initial portfolio). Why is the resampled portfolio inside the original efficient frontier?
- Write down (but do not do the calculations) how you would tackle this problem if instead of just choosing one portfolio, you chose 10 portfolios in step one. Please write this down step by step so that a computer programer could understand how to create the full resampling.
- Bonus: The Michaud resampling method averages the weights in the final step. Can you suggest an alternative to the simple average.

I have a number of Excel files on CD. The key file is us_5yr_historical_data-updated_dec2005.XLS. There are more than fifty columns of data in this monthly file with some data going back to the 1850s. **You should expect to do some work updating and adding new data** (for example, see the far right fields which are a partially updated version of Robert Shiller's famous dataset). Develop a regression forecasting model using variables such as dividend yield, Aaa-Tbill, Baa-Aaa, various interest rates and spreads. Be sure to lag the forecasting variables. That is, the dividend yield in December 1998 should be matched with the five year return from January 1999 to December 2003. You might find it useful to look at the Federal Reserve Bank of St. Louis' website which contains good historical interest rate data.

If there are data updates, I will post on the Class site.

You can use overlapping data (by year) or non-overlapping data (only one observation for every five years). Provide a an out of sample forecast for 2006-2010. Comment on your forecast. Would you adjust your forecast? What would your adjusted (using judgement forecast be). What is the motivation for your adjustment? What is wrong with just using the dividend yield for expected returns? How would you adjust the forecasting model (different specification of variables or new variables)?

Reestimate your model through December 2000. What were the forecasts for 2001-2005?

Apply a simple valuation model to the S&P 500, the dividend growth model. I suggest the following, collect forecasted earnings for the next three to ten years. Use a version of the dividend growth model: [E(r)-g]=d/p. I have included a S&P 500 operating earnings file in the data directory on the CD. You can update with the following link. I have also put in a template that you can play with called Dividend_discount_model.xls (this sheet is rough and is only meant as an example of what could be done). Comment on the expected return that you get relative to the forecasted return based on the regression model. Is the market over or undervalued according to the model? Why?

- What do you think the value of the S&P500 should be today?
- What is you 80% confidence range, i.e. give me the 10% and 90% tails in your valuation.
- On a scale of zero (least) to seven (most), rate how
**competent**you feel about your ability to value the market.

*Please do not email files to my account.*