Notes for Lab

Chapter 10: * 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