Excel Problems

Web Portfolio - Click here to download the spreadsheet (78.0K)

The accompanying spreadsheet uses the same methodology as Selected Dow Stocks in Chapter 6. It allows you to construct efficient frontiers for different target rates of returns. The data that is contained on the spreadsheet is return for World Equity Benchmarks (WEBs). The file contains data on all of the WEBs that had been trading for 48 months through December 2000. The returns are price relative returns and monthly returns have been annualized for portfolio comparisons. See Chapter 6 pp. 219-223 or the Selected Dow Stocks Excel application in Chapter 6 for complete explanations and development of the models. The allocations to the various indices have been constrained to be zero or positive. This restricts the use of short sales. The model can be modified to allow short sales by making appropriate changes to the weights.

Problem
The data for this problem is on the worksheet entitled WEB Returns for Problem. The data includes monthly returns for seven Webs and returns for the SP 500 Index. The monthly returns are used to calculate the correlation matrix. The returns are annualized at the bottom of the spreadsheet to use in the portfolio application.

Use target rates of return of 2, 4, 6, 8, 10, 12, 14, 16 and 17 percent to construct the efficient frontier. The returns and standard deviations are shown below. The returns and standard deviations can be copied directly from WEB Returns for Problem using the Paste Special Transpose Command in Excel.

World Eq. Bench.	Returns	S.D.	Country
EWA	2.6	21.8	Australia
EWK	15.4	27.9	Belgium
EWQ	16.5	20.8	France
EWG	11.2	24.6	Germany
EWH	6.4	41.1	Hong Kong
EWW	16.2	38.8	Mexico
EWU	8.8	15.3	United Kingdom
SP 500	17.2	17.2

Contrast these results with the diversification benefits demonstrated in the initial web portfolio model.

Click here to download the solutions (70.0K)