* Evaluate the risk/return profile of individual stocks as compared to an equally weighted portfolio of these stocks * Prices are collected from Yahoo Finance (follow instructions in the book, pay attention to how the prices are ordered when calculating the returns later) * 10 stocks in the lab, 12 in the book

* For Motorola, use MSI instead of the ticker symbol in the book Steps:

1. Calculate Returns:

Rt=Pt-Pt-1Pt-1= PtPt-1-1

2. Mean Monthly Returns and Standard Deviations for Each Stock: Mean Monthly Return: R= 1T*t=1TRt → AVERAGE

Standard Deviation: STDEV = Var = 1T-1*t=1T(Rt-R)2 → STDEV.S 3. Convert Monthly to Annual Returns and Standard Deviations: Annual Mean Return: RA=12*RM

Annual Standard Deviation: STDEVA= 12* VarM= 12*STDEVM

4. Portfolio (equally weighted):

Monthly Return: RP= iwiRi

equally weighted: RP= 110iRi → AVERAGE

Variance: VarP= ijwiwjCovi,j

equally weighted: VarP= (110)2ijCovi,j

Covariance Matrix: Cov1,1⋯Cov1,n⋮⋱⋮Covn,1⋯Covn,n → symmetric

Create Covariance Matrix:

Two Options:

1. Data Analysis (Add-In Tool) (indirectly, returns population covariance) 2. Sample Covariance-Formula in Excel (see extra file)

Population Cov: CovRi,Rj=1n*t=1nRi,t-Ri *(Rj,t-Rj)

Sample Cov: CovRi,Rj=1n-1*t=1n(Ri,t-Ri)*(Rj,t-Rj)

Data Analysis: Multiplying every covariance of the output with nn-1 = 6060-1 will return the sample covariances * Insert Analysis: File, Options, Add-Ins, Excel Add-Ins, Go to: click Analysis, Solver (comes up under data on the right) * Go to the Return sheet, Click data analysis, tick covariance matrix, mark the returns and the names, tick names in first row * Returns half a matrix

* to receive the full matrix copy the data range that includes the Matrix and insert to the right with the Paste Option “transpose” (since the Matrix is symmetric) Ex: for 145726893T you will get 178429563

* Copy again and insert over the old matrix (mark it) with the paste option “skip empty cells” * Multiply by nn-1, n is the number of returns (enter 60/59 in a cell, copy, mark the matrix, insert with paste option “multiply”) Other Option (example extra Sheet):

* Use Covariance.S (Array 1; Array 2): find a formula for the upper left hand cell that can be dragged right and left and returns the whole matrix * Array 1: Return column constant as we drag right, move one return column to the right as we drag down one row * Array 2: move one Return column to the right as we drag the formula to the right, Return column constant as we drag down =COVARIANCE.S(OFFSET($A$2:$A$4;0;COUNTA($J$1:$J1));A$2:A$4)

* Array 1: Offset(Reference, rows, columns)

* Reference: $A$2:$A$4 (fix cells and columns, this is always the starting point) * Rows: 0, returns from the first return to the last return always included * Columns: in the first row, we want the first column of returns, in the second row, we want the second column of returns,… * CountA which counts the cells with content ($A$1:$A1) is defined so that it counts 0 for the first row (returns first columns of returns), 1 for the second row (returns second columns of returns), …. * Array 2: A$2:A$4 (as we drag right, columns change by one and therefore return the column of returns to the right, as we drag down nothing changes) In this case:

Variance: VarP= 1102ijCovi,j → 1102*sumcovariance matrix 5. Graph the risk return tradeoff between stocks and the portfolio Risk on x-Axis, Return on y-Axis

Copy data as values to the sheet “graph”

a) Insert: Graph: Point (XY)

b) Choose data: add: Name: Stocks, x: range of risks, y: range of returns c) Add: Name: Portfolio: x: risk, y: return

d) Layout: add axis titles

11.

Find frontier for 10 stocks without short selling, with short selling, and with short selling and the inclusion of a riskfree asset, identify tangency portfolio, change weights with Excel Solver * Copy the data...