Using an excel spreadsheet that has been provided, each student will determine the efficient frontier for a world consisting of three (3) stocks for BOTH unconstrained condition and constrained condition (weights assigned to stocks must all be greater or equal to zero)
Process
Step 1: Identify Three Stocks
The stocks must trade of a US stock exchange
Two of names of the stock must begin with your family name – example using Derek Yee, two stocks’ names must start with Y
One name of the stock must begin with your first name – example using Derek Yee, one stock’s name must start with D
Most convenient method https://ca.finance.yahoo.com Hint: to make your life easier, select stocks that no NOT pay dividends
Step 2: Gather data for each stock (which can be downloaded in excel spreadsheet format from yahoo)
60 months ending March 1, 2015 (make sure for each stock the dates are the same) closing price dividend paid during month in unconstrained excel spread sheet, add a workbook that contains data for all three stocks
Step 3: Calculations for each of the 3 stocks
Calculate monthly total returns for 59 periods
Calculate the average monthly return
Calculate the standard deviation of the monthly returns
Calculate correlations (using excel function CORREL)
Stock 1 and Stock 2
Stock 1 and Stock 3
Stock 2 and Stock 3
Step 4: Using “Portfolio” workbook in the unconstrained excel spreadsheet
Enter average monthly returns in cells B5:B7
Enter standard deviations in cells C5:C7
Enter correlations in cells B12:D14
Step 5: Determine range of values for “expected return”
Unconstrained excel spreadsheet
Low end of range is equal to 1/3 of the value of lowest monthly return of the 3 stocks
High end of range is equal to 3 time the value of highest monthly return of the 3 stocks
Determine 10 expected returns which are equally spaced between low & high end of range
Constrained excel spreadsheet
Low end of range is equal to lowest monthly return of the 3 stocks
High end of range is equal to highest monthly return of the 3 stocks
Determine 10 expected returns which are equally spaced between low & high end of range
Step 6: Open Solver
Select one (of the 10 equally spaced expected returns)
Say the value you selected was 1.0
Then inside solver, change value 1.5 in line “$B$48=1.5” to 1.0 in line “SB$48=1.0”
Step 7: in row 54, record values for
B48 (return)
B47 (standard deviation)
A41:A43 (weights)
