Data Analysis for Optimal Portfolio Model

1. Data Analysisfor Optimal Portfolio Model

2. 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!

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

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

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

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

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

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

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

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

13. Efficient Frontier with Lending & Borrowing CAL E(r) B Q P A S rf F St. Dev

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

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

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

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

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

19. Examples • Portfolio Mean: = ( W1 W2 ) * = W1*ER1 + W2*ER2 = MMULT(A1:B1, C1:C2) for Excel command

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

21. Example with 2 assets • Portfolio Variance = * * = * = = MMULT(MMULT(A1:B1,C1:D2),E1:E2)) = MMULT(A1:B1, MMULT(C1:D2,E1:E2))

22. Efficient Frontier with Lending & Borrowing CAL E(r) B Q P A S rf F St. Dev

23. 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?