Excel Problems

Excel Problems for chapter 6: Selected Dow Stocks and Two-Security Model

A. Selected Dow Stocks - Click here to download the spreadsheet (71.0K)

The accompanying spreadsheet is an Excel model that is similar to the model presented in Section 6.10 of the text. The model develops the efficient frontier for 8 securities that are currently included in Dow Jones Industrial Average. Actual price relative returns for the selected stocks were used the analysis.

The model is currently set to analyze eight securities but can be easily modified to accommodate different numbers of securities. As currently constructed, the model requires returns and standard deviations for eight securities and pairwise correlation coefficients for those securities. The efficient frontier graph is currently set to graph ten efficient frontier points. Any adjustment to that number would require adjustment in the graph. The only adjustment that is required when using eight securities and ten analysis points is to change the names on the data series for the graph.

The model is currently set to constrain investment to positive amounts. Any adjustment to allow leverage simply requires adjustment in the constraint section of solver.

Question:
The worksheet titled Dow Stocks for Problem contains the return information for the Dow Stocks that are listed below by their ticker symbol. Develop an efficient frontier ten points on the frontier that start at 20 percent and increment by 3 percent for each point. The last point on the frontier will be 47%. The correlation matrix can be calculated directly in Excel using Tools Data Analysis.

AXP, BA, IBM, JPM, MMM, SBC, MO, and MCD

B. Two-Security Model - Click here to download the spreadsheet (48.0K)

The accompanying spreadsheet can be used to measure the return and risk for a portfolio of two risky assets. The model calculates the return and risk for varying weights of each security along with the optimal risky and minimum risk portfolio. Graphs are automatically generated that adjust for various model inputs. The model allows you to specify a target rate of return and solves for optimal combinations using the risk-free asset and the optimal risky portfolio. The spreadsheet is constructed with the two security return data that is used in Section 6.9 of the text.

The model allows the minimum risk and optimal risky portfolio to be calculated under two methods. The first method allows short sales while the second method constrains the allocations in each security to be positive. The portfolio calculations that allow short sales result in weight in excess of 100% for one of the securities. Short sales usually result when securities have relative large positive correlations. When securities have relatively positive correlations, larger investments in the security with a better risk/return trade-off can be financed by selling short the less efficient security. This results in a superior risk return trade-off. This concept is developed in the discussion of the arbitrage pricing model in Chapter 8.

The initial required inputs for model include the return and standard deviation for each of the two securities, the correlation coefficient of returns for the two securities and the risk free rate. Once these inputs are supplied, the model constructs the minimum variance and optimal risky portfolio. The model will also calculate efficient combination to meet a targeted rate of return.

Questions
You are considering investment in two securities. Security 1 has an expected return of 9% and a standard deviation of returns of 18%. Security 2 has an expected return of 17% and a standard deviation of returns of 28%. The correlation coefficient of returns for the two securities is 0.30 and the T-Bill is offering a 3.5% rate of return.

1. Assuming that no short sales are allowed, what would be the weights for each of the securities in the minimum variance portfolio?
2. Assuming that no short sales are allowed, what would be the weight for each security in optimal risky portfolio?
3. Suppose you wanted to achieve a target rate of return on 12%, what would be the optimal combination using both risky assets and the risk-free security?
4. Compare that result to the case in which you do not the option of investing in the T-Bill under the condition that you have a target rate of return of 12%.