Data Analysis for Optimal Portfolio Model

1
Data Analysisfor Optimal Portfolio Model
2
Return Calculation

• Return calculation
• Rt(Pt/Pt-1)-1
• Note
• Be careful about the sequence of your returns, do
  not calculate returns backwards!

3
Input Statistics

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

4
Covariance and Standard Deviation

• Covariance
• Use excel function COVAR to find pair-wise
  covariances, and construct the covariance matrix
• COVAR (A1A3, B1B3)
• STDEV (A1A3)
• VAR(A1A3)
• CORREL(A1A3,B1B3)

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

6
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.
• There are two ways to do this.
• Approach 1 Using the property of portfolio
  theory, you obtain ONLY two MVPs (any portfolios
  but I recommend the GMVP and the other one
  reasonably close to the GMVP) and calculate other
  portfolios by combining these two portfolios.
  Refer to Example 2 in Section B of Chapter 13.
• Approach 2 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
  worksheets SetUP2 and Short(2) in Example 2
  in Section B of Chapter 13.

7
Solver

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

8

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

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

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

12
Efficient Frontier with Lending Borrowing
CAL
E(r)
B
Q
P
A
S
rf
F
St. Dev
13
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

14
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

15
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. AB 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 (AB), the number
  of columns in matrix A must be the same as the
  number of rows in matrix B.

16
Matrix in Excel

• Excel Functions
• Transposition
• TRANSPOSE()
• Multiplication
• MMult
• e.g., MMULT(A1C1, D1D3)
• Where A1C1 refers to a 1X3 matrix (row vector)
  and D1D3 refers to a 3X1 matrix (column vector).
  

17
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

18
Examples

• Portfolio Mean
• ( W1 W2 ) W1ER1 W2ER2
• MMULT(A1B1, C1C2) for Excel command

19
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

20
Example with 2 assets

• Portfolio Variance
• 
  
• MMULT(MMULT(A1B1,C1D2),E1E2))
• MMULT(A1B1, MMULT(C1D2,E1E2))