Loading in 5 sec....

Data Analysis for Optimal Portfolio ModelPowerPoint Presentation

Data Analysis for Optimal Portfolio Model

- 45 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about ' Data Analysis for Optimal Portfolio Model' - samantha-hodges

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

### Data Analysisfor Optimal Portfolio Model

Return Calculation

- Return calculation:
Rt=(Pt/Pt-1)-1 or Ln(Pt/Pt-1)

Note:

- Be careful about the sequence of your returns, do not calculate returns backwards!

Input Statistics

- Mean, standard deviation, covariance matrix:
- Mean: Which Excel function to use?
- Standard deviation: Use information from the covariance matrix (to be explained next).

Covariance and Standard Deviation

- Covariance
- Use excel function COVAR to find pair-wise covariances, and construct the covariance matrix
- =COVAR (A1:A3, B1:B3)
- =STDEV (A1:A3)
- =VAR(A1:A3)
- =CORREL(A1:A3,B1:B3)

COV_VAR matrix

- Diagonal cells are the variance for individual assets. For example, Variance of A = VAR(A) = Cov(A, A). You can use this property to find the standard deviation of A.
- Off-diagonal cells are the covariance between two assets. For example, COVAR(A,B) = -0.1.

Min. Variance Portfolio (MVP)

- Choose weights that minimize the portfolio risk (Std. Dev or Var) given certain constraints.
Minimize Portfolio Risk (σ p )

with respect to Wi

Constraints:

Sum of Weights = 1;

Portfolio return = 15% for example.

4. Minimum-Variance Frontier Construction:

- To construct a minimum-variance frontier, you need to graph the relation between the mean and standard deviation of the minimum-variance portfolios.
- Obtain several MVPs (mean and std dev) and draw the graph connecting these portfolios – Need to run the Solver as many times as the number of MVPs you want. Refer to examples in Chapter 13.
- Textbook shows alternative approach with a different format. Some of the exhibits are uploaded in our class Webcourse.

Solver

Minimum-Variance Portfolio Construction:

- For each desired level of return, find the minimum variance portfolio using solver.
- What should be your target cell?
- Should you maximize or minimize your target cell?
- What are your constraints?
- What cells can you change values?
- Do NOT check on Assume Linear on the option.

Chart

- How to plot minimum-variance chart?
- After finishing previous step, i.e., obtaining all pairs of expected returns and Std.
- Under insert menu, select Chart.
- The Chart-type should be XY (scatter), with the data points connected by smooth lines.

Chart

- How to plot minimum-variance chart?
- Identify the X and Y in the inputs.
- What should be your X and Y?
- Follow the prompts to put Chart Title and other cool stuffs.
- You can always change your chart format by right clicking on the chart.

Optimal Portfolio with risk-free asset

6. How to identify the optimal risky portfolio (P) on the efficient frontier when there is a risk free asset?

- What is your objective?
- CAL with highest slope.
- How do you quantify your objective?

- What are your constraints?
- What cells can you change?
- Once you have found the weights for P, you also have the mean and standard deviation for P.

Matrix Basics for Portfolio Optimization

- Row matrix (vector) A=(a1 a2)
- Column matrix (vector) B=
- Square Matrix
- Number of rows equals number of columns
- Example of square matrix: variance-covariance matrix

Matrix Basics for Portfolio Optimization

- Matrix Basics
- Matrix transposition
- Change the rows (columns) in a matrix to columns (rows)
- e.g., A=(1 2), its transpose AT=
- B= its transpose BT=(0.8 0.2)
- exercise
- Transpose of a 3X2 matrix

- Change the rows (columns) in a matrix to columns (rows)

- Matrix transposition

Matrix Basics

- Matrix Multiplication.
- Example:
- A=(0.6 0.4) B=
- Here, A could represent the portfolio weights on two assets in a portfolio, and B could represent the returns on these two assets. A*B gives return on this portfolio.
- Portfolio return:
A * B = (0.6 0.4) * = 0.6* 0.10 + 0.4 * 0.15 = 0.12

- When multiplying two matrices (A*B), the number of columns in matrix A must be the same as the number of rows in matrix B.

- Example:

Matrix in Excel

- Excel Functions
- Transposition
- TRANSPOSE()

- Multiplication
- MMult
- e.g., =MMULT(A1:C1, D1:D3)
- Where A1:C1 refers to a 1X3 matrix (row vector) and D1:D3 refers to a 3X1 matrix (column vector).

- Transposition

Application of Matrix in Portfolio Optimization

- Portfolio return:
- A row vector storing portfolio weights
Multiply by

- A column vector storing portfolio return
- Or the transpose of a row vector storing portfolio return

- A row vector storing portfolio weights

Examples

- Portfolio Mean:
= ( W1 W2 ) * = W1*ER1 + W2*ER2

= MMULT(A1:B1, C1:C2) for Excel command

Application of Matrix in Portfolio Optimization

- Portfolio Variance
- A row vector storing portfolio weights
Multiply by

- The variance-covariance matrix
Multiply by

- the transpose of the row vector storing portfolio weights

- A row vector storing portfolio weights

Example with 2 assets

- Portfolio Variance
= * * = *

=

= MMULT(MMULT(A1:B1,C1:D2),E1:E2))

= MMULT(A1:B1, MMULT(C1:D2,E1:E2))

Optimal Portfolio with risk-free asset

- The new frontier is linear with portfolio P is the only optimal risky portfolio.
2. How to identify the optimal risky portfolio (P) on the efficient frontier when there is a risk free asset?

- What is your objective?
- CAL with highest slope.
- How do you quantify your objective?

- What are your constraints?

- What is your objective?

Download Presentation

Connecting to Server..