Project 2: The Capital Asset Pricing Model and Portfolio Theory
Overview
In Part I, you will retrieve financial data and calculate stock returns and betas. In Part II, you will calculate portfolio returns and betas. In Part III, you will conduct a mean-variance analysis and construct an efficient frontier.
Part I: Retrieve Financial Data and Calculate Stock Returns and Betas
A. Yahoo Finance
1. Pick a publicly-traded firm that you are interested in analyzing. Make sure the firm is listed on the Dow Jones Industrial Average or S&P500. You can find the firms currently listed on these indexes by searching Google. Record the ticker symbol and company name.
2. Go to finance.yahoo.com and enter the ticker symbol in the search box.
3. Click ‘Historical Prices’ (on the left hand side of the screen).
4. Select the following: a. Start Date = January 1, 2009, b. ‘Monthly,’ and c. ‘Get Prices.’
5. Scroll down to the bottom of the page and click on the ‘Download to Spreadsheet’ link. Save the spreadsheet as ‘Project 2.’
6. Repeat this procedure four more times, downloading data for each firm and pasting into new worksheets. Title each worksheet with the ticker symbol of each stock. For each stock, price data must be available for the entire time period. If it is not, then select another.
7. Download Dow Historical Prices from Canvas (under Modules). On finance.yahoo.com you will see links to S&P500 and NASDAQ. Repeat the above data-gathering procedure for these indexes and also for the 30-Year Treasury bond (use the search box and enter ‘Treasury Yield’ to find).
B. Combine Worksheets
1. Create another worksheet titled ‘Price Summary’ containing the columns:
|
Date |
ABC |
DEF |
GHI |
JKL |
MNO |
DJIA |
S&P500 |
NASDAQ |
30-Year Treasury |
where ABC, DEF, GHI, JKL, and MNO represent the ticker symbols of your five stocks. In the rows below this, link to the dates and adjusted closing prices that you downloaded earlier. Freeze the top pane.
2. Insert a new worksheet titled ‘Annual Returns Summary.’ In the first column, label the first row ‘Date,’ label the next five columns ‘Annual ?XYZ’ where XYZ is the ticker symbol for each stock. Label the final four columns ‘Annual ?DJIA,’ ‘Annual ?S&P500,’ ‘Annual ?NASDAQ,’ and ‘Annual rf.’ Link the dates to those from the ‘Price Summary’ worksheet. Convert the monthly stock prices on ‘Price Summary’ into annualized returns by applying the transformation?X = Xt/Xt-12 – 1. Convert your Treasury data into rates by dividing by 100 and formatting as a percentage. Freeze the top row.
3. Use annualized returnsto calculate the betas for each of the stocks relative to each of the three indexes. There are three acceptable regression specifications for estimating beta:ri =ai +?irm,ri =ai +?i(rm – rf), and (ri – rf) =ai +?i(rm – rf). In each specification,ai is the excess return and?i is the slope. Initially, we will use the first regression specification, ri =ai +?irm.
a. Choose ‘Data Analysis’ on the ‘Data’ tab and choose ‘Regression.’
b. Input the Y range (the annualized returns of ABC).
c. Input the X range (annualized DJIA returns).
d. Under output options select ‘New Worksheet Ply,’ and click ‘OK.’
e. Repeat the procedure for the same stock using each of the other two indexes. Cut and paste the three regression outputs into a worksheet called ‘ABC Betas’ where ABC is the ticker symbol for your first stock. On this worksheet, place a label next to each regression output as follows: ‘ABC/Index’ where index is the name of the stock index used in the regression. Repeat the procedure for each of the four other firms. You will run a total of 15 regressions and create a total of 5 beta worksheets.
f. Create a new worksheet called ‘Beta Summary.’ Your table should be formatted as follows:
|
Regression Betas |
|||
|
DJIA |
S&P500 |
NASDAQ |
|
|
ABC |
|||
|
DEF |
|||
|
GHI |
|||
|
JKL |
|||
|
MNO |
|||
|
Portfolio 1 |
|||
|
Portfolio 2 |
|||
|
Variance/Covariance Betas |
|||
|
DJIA |
S&P500 |
NASDAQ |
|
|
ABC |
|||
|
DEF |
|||
|
GHI |
|||
|
JKL |
|||
|
MNO |
|||
|
Portfolio 1 |
|||
|
Portfolio 2 |
g. Fill in the ‘Regression Betas’ section by linking to the associated regression outputs. The cells for ‘Portfolio 1’ and ‘Portfolio 2’ will remain blank for now.
h. In the section labeled ‘Variance/CovarianceBetas,’ compute the betas for each of the three indexes for each stock that you have selected using the formula , whereis the variance (VARIANCE.P) of market returns andis the covariance (COVARIANCE.P) of the returns of stock i with the returns of the market. These betas should exactly match your regression betas.
You should now have the following worksheets in this order:
a. ABC
b. DEF
c. GHI
d. JKL
e. MNO
f. DJIA
g. S&P500
h. NASDAQ
i. 30-Year Treasury
j. Price Summary
k. Annual Returns Summary
l. ABC Betas
m. DEF Betas
n. GHI Betas
o. JKL Betas
p. MNO Betas
q. Beta Summary
Part II: Calculate Portfolio Returns and Betas
A. Portfolio Returns
1. In the ‘Annual Returns Summary’ worksheet, create two columns titled ‘Annual ?Portfolio 1’ and ‘Annual ?Portfolio 2.’ For ‘Annual ?Portfolio 1,’ each month calculate the average of the annualized returns of all five stocks. This column represents the returns of an equally-weighted portfolio.
2. Create a new worksheet titled ‘Portfolio 2 Value.’ The worksheet should have two columns. The first column will be the date (referenced from your ‘Price Summary’ worksheet) and the second column, labeled ‘Portfolio 2 Value,’ will be the sum of your five adjusted stock prices for that month.
3. In the ‘Annual?Portfolio 2’ column on the ‘Annual Returns Summary’ worksheet, use the values that you calculated in the ‘Portfolio 2 Value’ worksheet to calculate annualized returns. This column represents the returns of a value-weighted portfolio.
4. For each asset on the ‘Annual Returns Summary’ worksheet, calculate the total holding period return, annual geometric mean return, annual arithmetic mean return, and population standard deviation. Note that you will not be able to calculate the THPR or the annual geometric mean for the Treasury or Portfolio 1.
B. Portfolio Betas
1. In the ‘Beta Summary’ worksheet, calculate regression and cov/var betas for each of the two portfolios using each of the three market indexes. Put the regression output into two new worksheets titled ‘Portfolio 1 Betas’ and ‘Portfolio 2 Betas.’
2. Create a new worksheet labeled ‘Portfolio 2 Alt Specs.’
a. Create columns ‘Date,’ ‘rf,’ ‘?S&P500,’ and ‘?Portfolio 2’ by linking to the ‘Annual Returns Summary’ worksheet. Link the cells below this row to the appropriate dates and returns.
b. Create column ‘MRP,’ which is the difference between the annual S&P500 return and the 30-Year Treasury yield.
c. The last column is the difference between Portfolio 2’s annualized return and the 30-Year Treasury rate. Label this column ‘Portfolio Premium.’
3. On the ‘Portfolio 2 Alt Specs’ worksheet, run a regression as follows: rportfolio2 =aportfolio2 +?portfolio2(rm– rf). The dependent variable is annualized portfolio 2 returns, and the independent variable is the annual market risk premium. Set the output range to an available space on the same worksheet, and title the output ‘Spec 1.’
4. Run another regression as follows: (rportfolio2– rf) =aportfolio2 +?portfolio2(rm– rf). The dependent variable is now the portfolio premium. Set the output range to another available space on the same worksheet, and title the output ‘Spec 2.’
C. SML
1. Create a new worksheet and label it ‘SML.’
a. Create three columns of data: ‘Ticker,’ ‘S&P500 Beta,’ and ‘Average Return.’
b. Link ‘S&P500 Beta’ to the S&P500 betas in ‘Beta Summary.’
c. For each of your stocks and portfolios, link ‘Average Return’ to the geometric average annual returns that you calculated at the bottom of your ‘Annual Returns Summary’ worksheet. Note that you will have to use arithmetic returns for Portfolio 1.
Your data should now be formatted as follows:
|
Ticker |
S&P500 Beta |
Average Return |
|
ABC |
1.2766 |
-0.27% |
|
DEF |
1.7105 |
0.95% |
|
GHI |
0.9191 |
-2.21% |
|
JKL |
0.3698 |
1.14% |
|
MNO |
0.9433 |
1.64% |
|
Portfolio 1 |
1.0439 |
0.25% |
|
Portfolio 2 |
0.9044 |
-0.46% |
2. Run a regression between the average return (the dependent variable) and the S&P500 beta estimates (the independent variable). In the regression dialog box, check ‘Line Fit Plot’ and ‘New Worksheet Ply.’ Format the graph as a scatter plot with a line through the predicted points. Name this worksheet ‘SML Plot.’ Label the x and y axes, create an appropriately labeled legend, and give the plot a title.
You should now have the following worksheets in this order:
a. ABC
b. DEF
c. GHI
d. JKL
e. MNO
f. DJIA
g. S&P500
h. NASDAQ
i. 30-Year Treasury
j. Price Summary
k. Annual Returns Summary
l. ABC Betas
m. DEF Betas
n. GHI Betas
o. JKL Betas
p. MNO Betas
q. Beta Summary
r. Portfolio 2 Value
s. Portfolio 1 Betas
t. Portfolio 2 Betas
u. Portfolio 2 Alt Specs
v. SML
w. SML Plot
Part III: Mean-Variance Analysis
A. Variance-Covariance Matrix
1. Go to the ‘Annual Returns Summary’ worksheet.
2. Go to Data -> Data Analysis -> Correlation.
a. Highlight the annualized returns for your stocks, including the column labels, and put these in the ‘Input Range.’
b. Check the ‘Labels in First Row’ and ‘New Worksheet Ply’ buttons. Enter ‘Correlations’ in the ‘New Worksheet Ply’ box.
c. Click OK. This will create a new worksheet with the correlations formatted as follows:
|
AnnualDABC |
AnnualDDEF |
AnnualDGHI |
AnnualDJKL |
AnnualDMNO |
|
|
AnnualDABC |
1 |
||||
|
AnnualDDEF |
0.2124 |
1 |
|||
|
AnnualDGHI |
0.0287 |
0.3754 |
1 |
||
|
AnnualDJKL |
0.2538 |
-0.2731 |
0.1076 |
1 |
|
|
AnnualDMNO |
0.3465 |
0.3848 |
0.2009 |
0.2402 |
1 |
Name this worksheet ‘Correlations.’
3. Create a new worksheet titled ‘Variance-Covariance Matrix.’
a. Create a table titled ‘Incomplete Correlation Matrix’ by linking to all cells in the ‘Correlations’ worksheet. Your table should look similar to this:
Incomplete Correlation Matrix
|
AnnualDABC |
AnnualDDEF |
AnnualDGHI |
AnnualDJKL |
AnnualDMNO |
|
|
AnnualDABC |
1.0000 |
0.0000 |
0.0000 |
0.0000 |
0.0000 |
|
AnnualDDEF |
0.2124 |
1.0000 |
0.0000 |
0.0000 |
0.0000 |
|
AnnualDGHI |
0.0287 |
0.3754 |
1.0000 |
0.0000 |
0.0000 |
|
AnnualDJKL |
0.2538 |
-0.2731 |
0.1076 |
1.0000 |
0.0000 |
|
AnnualDMNO |
0.3465 |
0.3848 |
0.2009 |
0.2402 |
1.0000 |
The cells containing 0s are the cells that were blank in the ‘Correlations’ worksheet.
b. Below the ‘Incomplete Correlation Matrix’ create a new table titled ‘Completed Correlation Matrix.’
i. Construct the ‘Completed Correlation Matrix’ by first copying and pasting the values of the ‘Incomplete Correlation Matrix’ and then using the TRANSPOSE function to replace the zero values. You will need to transpose each column separately.
ii. Create a new bottom row and link it to the respective population annual standard deviations in the ‘Annual Returns Summary’ worksheet. Then insert a column vector that contains the transpose of these standard deviations (?’) to the right of the matrix. When completed, your table should be formatted as follows:
Completed Correlation Matrix
|
AnnualDABC |
AnnualDDEF |
AnnualDGHI |
AnnualDJKL |
AnnualDMNO |
?’ |
|
|
AnnualDABC |
1.0000 |
0.2124 |
0.0287 |
0.2538 |
0.3465 |
12.41% |
|
AnnualDDEF |
0.2124 |
1.0000 |
0.3755 |
-0.2730 |
0.3849 |
13.90% |
|
AnnualDGHI |
0.0287 |
0.3754 |
1.0000 |
0.1076 |
0.2009 |
13.66% |
|
AnnualDJKL |
0.2538 |
-0.2731 |
0.1076 |
1.0000 |
0.2402 |
8.07% |
|
AnnualDMNO |
0.3465 |
0.3848 |
0.2009 |
0.2402 |
1.0000 |
9.02% |
|
? |
12.41% |
13.90% |
13.66% |
8.07% |
9.02% |
c. Below the ‘Completed Correlation Matrix,’ create a new table titled ‘Variance-Covariance Matrix.’ This matrix is represented by the Greek symbol ?.
i. Fill in all of the cells in the Variance-Covariance Matrix (?). Each element in ? is ?xy, and is calculated as ?xy = ?xy*?x*?y. In other words, you need to multiply each cell in ? first by ?x and then by ?y. For example, in the correlation matrix above, the calculation for ?ABC,DEF would be ?ABC,DEF = ?ABC,DEF*?ABC*?DEF, or ?ABC,DEF = 0.2124 * 0.1241 * 0.1390 = 0.0037 = 0.37%. Hint: An easy way to complete the table is to multiply while anchoring the row on the horizontal standard deviation row and anchoring the column on the vertical standard deviation column.
ii. In the row below ?, create a row of equal weights (this is the w row vector). Create a check at the end of the row by summing all of the weights (the row should sum to 1).
iii. To the right of ?, create a column vector of weights using the TRANSPOSE function. This is the w’ column vector.
iv. To the right of the weights created in iii, create a column vector of returns by using the TRANSPOSE function and linking to the arithmetic annual mean returns for each stock in the ‘Annual Returns Summary’ worksheet. This is the E(r) column vector.
v. In the row below the weights that you created in ii, create a row labeled w?. Use MMULT to multiply the w row vector by the ? matrix. Your final table should now be formatted as follows.
Variance-Covariance Matrix (?)
|
AnnualDABC |
Annual DDEF |
Annual DGHI |
Annual DJKL |
Annual DMNO |
w‘ |
E(r) |
|
|
AnnualDABC |
0.0154 |
0.0037 |
0.0005 |
0.0025 |
0.0039 |
0.2000 |
-0.27% |
|
AnnualDDEF |
0.0037 |
0.0193 |
0.0071 |
-0.0031 |
0.0048 |
0.2000 |
0.95% |
|
AnnualDGHI |
0.0005 |
0.0071 |
0.0187 |
0.0012 |
0.0025 |
0.2000 |
|
