Project 2: The Capital Asset Pricing Model and Portfolio Theory

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