Risk and Return --- Individual stock and portfolios

Excel Template


1.      Visit finance.yahoo.com

2.      Search for the stocks of your choices, such as WalMart, Amazon, Apple, and S&P500 

3.      Click on historical data

4.      Time period: five year monthly, such as 5/1/2014 – 5/1/2019 (five years)

Show: Historical price

Frequency: Monthly

5.      Click download data

6.      Open in Excel the downloaded file

7.      Delete all columns except “date” and “adj close”

8.      Repeat the above for the other two stocks, such as Amazon, Apple, and S&P500. We use S&P500 index as a proxy for the market performance. 

9.      Combine the three companies into one excel file.

10.  Calculate the average return and the risk (standard deviation), correlation matrix, portfolio returns, and portfolio standard deviation. 

11. Calculate Beta, stock return based on CAPM, and draw SML