**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 r_{f}.’ 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 = X_{t}/X_{t-12} – 1. Convert your Treasury data into rates by dividing by 100 and formatting as a percentage. Freeze the top row.

3. Use annualized returns**to calculate the betas for each of the stocks relative to each of the three indexes. There are three acceptable regression specifications for estimating beta:r _{i} =a_{i} +?_{i}r_{m},r_{i} =a_{i} +?_{i}(r_{m} – r_{f}), and (r_{i} – r_{f}) =a_{i} +?_{i}(r_{m} – r_{f}). In each specification,a_{i} is the excess return and?_{i} is the slope. Initially, we will use the first regression specification, r_{i} =a_{i} +?_{i}r_{m}.**

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.

You Want A Similar Paper Done? Don’t be stressed, Click Here To Order this essay!!

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,’ ‘r_{f},’ ‘?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: r_{portfolio2} =a_{portfolio2} +?_{portfolio2}(r_{m}– r_{f}). 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: (r_{portfolio2}– r_{f}) =a_{portfolio2} +?_{portfolio2}(r_{m}– r_{f}). 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 | -2.21% |

AnnualDJKL | 0.0025 | -0.0031 | 0.0012 | 0.0065 | 0.0017 | 0.2000 | 1.14% |

AnnualDMNO | 0.0039 | 0.0048 | 0.0025 | 0.0017 | 0.0081 | 0.2000 | 1.64% |

w | 0.2000 | 0.2000 | 0.2000 | 0.2000 | 0.2000 | 1.0000 | |

w? | 0.0051 | 0.0063 | 0.0059 | 0.0018 | 0.0042 |

d. Beneath the Variance-Covariance Matrix create a table formatted as follows:

Solver Values | |

Variance | 0.0047 |

Standard Deviation | 6.86% |

Expected Return | 0.25% |

i. Variance is calculated as w?w’. Since w? is already calculated, you just need to multiply w? by w’.

ii. Calculate standard deviation based on variance.

iii. The expected return of the portfolio is , or the multiplication of the w row vector by the E(r) column vector.

You Want A Similar Paper Done? Don’t be stressed, Click Here To Order this essay!!

**B. Construct an Efficient Frontier**

1. Use Excel’s Solver function to alter asset weights and create a variety of expected portfolio returns to construct an efficient frontier.

a. Go to Data -> Solver.

b. ‘Set Objective’ should be set to the Variance cell in the Solver Values table that you created in the ‘Variance-Covariance Matrix’ worksheet. Select the ‘Min’ option, as we are trying to minimize our portfolio’s variance.

c. Set the ‘By Changing Variable Cells’ to the cells in the w row vector.

d. To use the ‘Subject to the Constraints,’ click the ‘Add’ button. The ‘Cell Reference’ is the portfolio weight check cell (the cell you created to verify the weights add to 100%). Set this cell equal to a constraint of 1. Set a second constraint that forces the expected return to equal -0.05.

e. Uncheck ‘Make Unconstrained Variables Non-Negative’ and click ‘Solve.’ This will bring up the ‘Solver Results’ box.

f. Check ‘Return to Solver Parameters Dialog,’ click ‘Save Scenario,’ name the scenario ‘-0.05,’ and click ‘OK.’

g. Repeat steps a through f changing the expected return constraint to 0, 0.05, 0.10, 0.15, 0.20, 0.25, 0.30, 0.40, and 0.50.

2. In the Data tab, click the drop-down labeled ‘What-If Analysis.’

a. Click ‘Scenario Manager.’ This will bring up a dialog box. The ‘Scenarios’ cell should contain the names that you previously saved.

b. Click ‘Summary’ and set the ‘Result cells’ to ‘X,Y’ where X is your ‘Standard Deviation’ cell and Y is your ‘Expected Return’ cell. This will create a ‘Scenario Summary’ worksheet.

c. Go to the ‘Scenario Summary’ worksheet and rename it ‘Mean-Variance Scenarios.’ If you entered your scenarios in order, the data in this worksheet should be horizontally sorted by ascending expected returns.

3. Create a scatter plot (with smooth lines and markers) of expected returns (vertical axis) vs. standard deviation (horizontal axis). Name this worksheet ‘Efficient Frontier Plot,’ and label the plot accordingly.

4. Overlay on this plot the geometric mean return and standard deviation of each stock from the ‘Annual Returns Summary’ worksheet.

a. Right-click on the chart and pick Select Data and then Add.

b. Go to your ‘Annual Returns Summary’ worksheet and select the return and standard deviation of one of your stocks.

c. Repeat this procedure for the remainder of your stocks and two portfolios.

You should now have a completed set of 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

x. Correlations

y. Variance-Covariance Matrix

z. Mean-Variance Scenarios

aa. Efficient Frontier Plot

You Want A Similar Paper Done? Don’t be stressed, Click Here To Order this essay!!