Practice Project Guide

1
Practice Project Guide
2
Objectives

• ETF
• Theory of portfolio optimization
• Practice of portfolio optimization using
  spreadsheet
• Appreciation of the benefits of portfolio
  optimization

3
Practice Project

• Return calculation
• Rt(Pt/Pt-1)-1
• Note
• Be careful about the sequence of your returns, do
  not calculate returns backwards!
• You should have 5 time-series of returns, one for
  each individual ETF.

4
Practice Project

• Mean, standard deviation, covariance matrix
• Mean Which Excel function to use?
• Standard deviation Use information from the
  covariance matrix (explained next).
• Note Consider stacking the mean and standard
  deviations, so that your results look like this

5
Practice Project

• Covariance
• Use excel built-in function COVAR to find
  pair-wise covariances, and construct the
  covariance matrix, or,
• Use Covariance Analysis from Data Analysis
• Select data analysis under tools menu.
• Select covariance from data analysis

6
Practice Project

• Covariance
• The input box for covariance should look like the
  chart
• Input range should be cell references for the
  entire region of returns 60 months of return X
  5 stocks. It is also desirable to put labels in
  the first row, so that it will be easier to see
  what each return series is.
• Output range where you want you output to go.
  Remember that you have five stocks, plus the
  headings. Therefore you need 6 rows and 6
  columns of space for the output.

7
Practice Project

• Covariance
• Output from covariance analysis with Excel add-in
  will only have half of the matrix, you need to
  fill in the missing values using the property
  that the covariance matrix is symmetric against
  its diagonal, i.e., Cov(A,B)Cov(B,A).
• Diagonal cells are the variance for individual
  assets. For example, Variance of A Cov(A, A).
  You can use this property to find the standard
  deviation of A.

8
Practice Project

• 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.
• With mean and covariance matrix identified in
  step 3, you can use matrix operation to find the
  mean and standard deviation for portfolios
  invested in these five stocks. I have already
  put the formulas for mean and standard deviation
  in relevant cells (colored area in the Chart).
  In the template, portfolio weights are put in
  rows. More information about matrix will be
  given later.

9
Practice Project

• Minimum-Variance Construction
• Identify the levels of expected portfolio returns
  that you intend to achieve as your desired
  returns.
• What is the range of expected portfolio returns
  that you could achieve?
• Can you think of an efficient way of obtaining
  the necessary pairs of risks and returns for
  plotting the frontier?
• 21 data points
• Not-clustered, how?

10
Practice Project

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

11
Practice Project

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

12
Practice Project

• 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.
• I have included a chart so that you know how your
  chart may look like.

13
Practice Project

• How to identify your optimal portfolio (A) when
  there is no risk free asset?
• What is your objective?
• How do you quantify your objective?
• Remember that you should put your objective in a
  cell and use that cell as your target cell in
  your spreadsheet.
• What are your constraints?
• What cells can you change?

14
Practice Project

• How to identify your optimal portfolio when there
  is no risk free asset? (Contd)
• How do you identify this optimal portfolio on the
  efficient frontier?
• On the chart for the efficient frontier, right
  click, and select source data menu.
• Click on series TAB, and then click add. You
  can now enter the name, and the values for (X,Y).
  Remember, Portfolio A is only one point in the
  chart.

15
Practice Project

• 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?
• Again, remember that you should put your
  objective in a cell and use that cell as your
  target cell in your spreadsheet.
• 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. You
  can plot P on the efficient frontier as well, by
  adding another series of data in the original
  chart.
• To add CAL on the chart, you need to add a
  straight line. In excel, two points (the risk
  free asset, and P) define this line. You should
  extend this line to the right of P. To extend
  this line, you can use the TREND function in
  excel.

16
Practice Project

• How to identify your optimal portfolio (C) on the
  optimal CAL?
• Easiest way is to use the formula for optimal
  allocation on a CAL.

• Once you have found y, you can find the expected
  return and standard deviation for C, which can be
  used to plot C on the Capital Allocation Line.

17
Sample Output
18
Practice Project

• Have fun!

19
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

20
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

21
Matrix Basics for Portfolio Optimization

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

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

23
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

24
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