Purchase Solution

Portfolio Optimization Problem

Not what you're looking for?

Ask Custom Question

Create an Excel optimization file with good modeling techniques. Using Solver and Solver Table.

Not sure how to get started on this problem any help would be appreciated.

Thank you.
Here is the problemt:

Portfolio Optimization Problem
The annual returns for three companies over the last 12 years are given below, where the return for year n is defined as:
(closing price,n) - (closing price,n-1) + (dividends,n) / (closing price,n-1)

Year IBC NMC NBS
1 11.2% 8.0% 10.9%
2 10.8% 9.2% 22.0%
3 11.6% 6.6% 37.9%
4 -1.6% 18.5% -11.8%
5 -4.1% 7.4% 12.9%
6 8.6% 13.0% -7.5%
7 6.8% 22.0% 9.3%
8 11.9% 14.0% 48.7%
9 12.0% 20.5% -1.9%
10 8.3% 14.0% 19.1%
11 6.0% 19.0% -3.4%
12 10.2% 9.0% 43.0%

Part a: Using Excel, determine for each company the estimated mean return and standard deviation. Also, calculate the estimated correlations and covariances between the companies' returns.
Hint: Use the Excel functions AVERAGE, STDEV, and CORREL to determine the means, standard deviations and correlations. The covariances can then be calculated from the correlations (as in the class example). Click on fx in the Formula Bar for help with using these Excel functions.
Part b: Using the three stocks, a financial planner would like to create the least risky portfolio with minimum of a 12% expected return. Implement a spreadsheet model to determine the desired portfolio. Discuss the solution and highlight exactly how risk is minimized in the optimal portfolio, while achieving the desired return.
Part c: Either by repeatedly re-running the model, or by using SolverTable, create the chart for the efficient frontier for this portfolio optimization problem. Explain the usefulness of such a chart.

Purchase this Solution

Solution Summary

Portfolio Optimization Problem

Solution Preview

Step 1:

We put the data into excel.

Step 2:

Compute the mean, standard deviation and covariance matrix. To calculate the covariance matrix, we use the excel function COVAR (check the cells of the covariance matrix to see how I used this function.

Step 3 (part b).

To find the optimal portfolio, we suppose that we buy a amount of IBC, b amount of NMC and c amount of NBS. Here a, b and c could be any real numbers but they have to add up to 1 (meaning that the sum of a b and c must be 100% of your portfolio, ...

Purchase this Solution


Free BrainMass Quizzes
Measures of Central Tendency

Tests knowledge of the three main measures of central tendency, including some simple calculation questions.

Terms and Definitions for Statistics

This quiz covers basic terms and definitions of statistics.

Know Your Statistical Concepts

Each question is a choice-summary multiple choice question that presents you with a statistical concept and then 4 numbered statements. You must decide which (if any) of the numbered statements is/are true as they relate to the statistical concept.

Measures of Central Tendency

This quiz evaluates the students understanding of the measures of central tendency seen in statistics. This quiz is specifically designed to incorporate the measures of central tendency as they relate to psychological research.