1 / 23

# Data Analysis for Optimal Portfolio Model - PowerPoint PPT Presentation

Data Analysis for Optimal Portfolio Model. Return Calculation. Return calculation: R t =(P t /P t-1 )-1 or Ln(P t /P t-1 ) Note: Be careful about the sequence of your returns, do not calculate returns backwards!. Input Statistics. Mean, standard deviation, covariance matrix:

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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:

Rt=(Pt/Pt-1)-1 or Ln(Pt/Pt-1)

Note:

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

• Mean, standard deviation, covariance matrix:

• Mean: Which Excel function to use?

• Standard deviation: Use information from the covariance matrix (to be explained next).

• 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)

• 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.

• 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.

• 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.

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.

• 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.

• 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.

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.

CAL

E(r)

B

Q

P

A

S

rf

F

St. Dev

• 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

• 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

• 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.

• 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).

• 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

• Portfolio Mean:

= ( W1 W2 ) * = W1*ER1 + W2*ER2

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

• 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

• Portfolio Variance

= * * = *

=

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

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

CAL

E(r)

B

Q

P

A

S

rf

F

St. Dev

• 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?