Data analysis for optimal portfolio model
Download
1 / 23

Data Analysis for Optimal Portfolio Model - PowerPoint PPT Presentation


  • 46 Views
  • Uploaded on

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:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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 analysis for optimal portfolio model

Data Analysisfor Optimal Portfolio Model


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


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


Efficient frontier with lending borrowing
Efficient Frontier with Lending & Borrowing

CAL

E(r)

B

Q

P

A

S

rf

F

St. Dev


Matrix basics for portfolio optimization
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 optimization1
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


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


Matrix in excel
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).


Application of matrix in portfolio optimization
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


Examples
Examples

  • Portfolio Mean:

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

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


Application of matrix in portfolio optimization1
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


Example with 2 assets
Example with 2 assets

  • Portfolio Variance

    = * * = *

    =

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

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


Efficient frontier with lending borrowing1
Efficient Frontier with Lending & Borrowing

CAL

E(r)

B

Q

P

A

S

rf

F

St. Dev


Optimal portfolio with risk free asset1
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?


ad