EXERCISE 5  CAPM & beta: 

The goal of this exercise is to show CAPM graphically.  We start by calculating risk and return data (a.k.a. “risk/return profile”) for all the firms in the portfolio (as seen in port.xls) as well as risk/return profile for the entire portfolio and for the market.  After calculated the data, we draw a Security Market Line (SML) that graphically represents the actual data (the empirical SML).  The last step is to draw a theoretical SML going from the risk/return point of a risk-free asset through the risk/return point of the market and beyond.  All the work is to done in your current port.xls/Sheet1 (i.e. the file that began as “portmaster.xls” in week3).

1)  Open your file from last week’s hyperbola exercise.  Go to the “Sheet 1”.  Calculate the betas of all the firms in the portfolio (plus the portfolio and the market).  Save them into a row below Tier3 on Sheet1 in your port.xls.   Use the Excel function SLOPE [Remember that the beta is the SLOPE of the regression line that is the result of regressing Ke against Kmkt] to make the calculations, as in =SLOPE(range of daily returns for the dependent variable, range of daily returns for the independent variable).  Your first Excel cell format might look something like:  =SLOPE(B500:B750,$AC500:$AC750)   The actual rows and columns are likely to be different for your spreadsheet.  See also:  sample5.xls

2)  Calculate the total annual returns (Total K) of each security (plus the portfolio and the market).  This return calculation will be =(last day mkt$-first day mkt$)/first day mkt$, where “mkt$” is the data in Tier2.  Put the results of your “Total K” calculations immediately below your beta calculation from step1 (above).

3)  Create a scatter plot chart of empirical risk/return profiles (of all the firms plus the portfolio plus the market) on a graph with risk on the horizontal axis, returns on the vertical axis.  You create the chart by selecting the two rows of data (from step 1 & 2 above) >INSERT [from the Excel command ribbon] > Charts > scatter [use independent points and no connecting lines].

4)  Add a second set of data points for the theoretical risk/return profiles with betas and TotalK

Your new rows will look like this:

Beta         0           1.00        2.00

Total K    Krf        Kmkt     Ke

where Krf is the risk free rate, Kmkt is the expected return of the market, and Ke is the return of a hypothetical security with Beta=2.00. For this exercise, use these givens:

Krf=2%;  Kmkt=10%, and Ke=Krf+β (Kmkt-Krf)

To add these three new points, try the following [step vary according to which version of Excel you use]:

Place cursor in white space of chart.

Right click. “Select Data”

Click “Add”

Highlight the beta data in the spreadsheet and copy to the “Series X” field.

Highlight Total K data in the spreadsheet and copy to the “Series Y” field.

Hit OK

5)  Create the empirical SML by setting the cursor on any of the empirical (i.e. real) data points. Right click on the data point, > “Add trend-line”.

6)  Create the theoretical SML by setting the cursor on one of the three data points generated in step 4 (above). Right click on the data point, > “Add trend-line”.

7)  My sample5 has a pretty crude graph.  The lines aren’t good examples of what you’ll see because you’ll have many more data points.  And try adding labels to the chart.  For example:  label the chart “CAPM”, label the two axes “return” & “beta”, and maybe even label your two lines “theoretical” and “empirical”.  The accuracy of your graph is important.  The cosmetics are less important.   

8)  Submit your spreadsheet to Moodle, week5, CAPM.