Risk and Return Financial project

Page 1 Revised March 30, 2018

Risk & Return Project FIN 201– Professor Koplik (Spring 2018)

This project is designed to give you some practical experience finding and using financial data as well as giving you some additional insights into the topic of risk and return. You can work on this project individually or together with a partner. You and/or your partner are free to discuss your project, your methods and work with other classmates, so long as you do not exchange spreadsheets or written materials.

Deliverables: Your spreadsheet (MS-Excel format) and write-up (MS-Word format) must be submitted through Blackboard as attachments. If you are working with a partner, please submit the project only once through Blackboard and make sure that both of your names are on the documents.

STEP 1: SELECT FOUR STOCKS AS FOLLOWS:

You will analyze four publicly traded firms from a list of firms that are a subset of the S&P 500. This list will be posted to Blackboard. As part of this analysis, you will examine the historical returns of the stock over a five year period.

Using the first four letters of your last name, pick four ticker symbols that begin with those same letters from among the provided list S&P500 symbols. If you are working with a partner, you should use the first two letters of your name and that of your partner, to determine the four letter. If your last name has fewer than four letters, then you can pick any S&P500 firms for the missing letters. For example, if the first four letters of your last name are KOPL, then you might pick the following symbols/firms: KLAC (KLA-Tencor Corporation); ORCL (Oracle); PKI (PerkinElmer); LUV (Southwest Airlines).

IMPORTANT: If you have any difficulties with the firms you select then select another stock symbol or seek advice before proceeding with the data analysis. Otherwise, you will waste time trying to replicate the analysis process for periods that are too short. Problems can arise, for example, if the firm does not have five full trailing years of adjusted close price data (e.g. this can happen if the firm was acquired, taken private or bankrupted). Some times, a firm’s stock reflects a merger or restructuring, which suggests a discontinuity in its beta over time. Stocks that perform extraordinarily well or very poorly during significant portions of the estimation period may produce beta estimates that are inaccurate or statistically weak. An effort was made to eliminate problematic price histories from the list, but some troublesome tickers may still persist in the list that will be provided.

Page 2 Revised March 30, 2018

STEP 2: REVIEW EACH FIRM

Research each of your firms and write a short paragraph explaining each firm’s business and associated risks. Based only on news and research about the business and its industry, assess the firm’s stand- alone risk. Would you consider each firm to be highly risky, moderately risky or low risk? Why?

IMPORTANT: Beta is a measure of the stock’s risk when held in a well-diversified portfolio. For this section you may comment on the firm’s market risk (i.e. beta), but it equally important to comment on the firm’s specific – and diversifiable – risk. Confusing beta (or systematic risk) with the overall riskiness of the stock will hurt your grade.

Steps 3 through 8 will be demonstrated and reviewed in class.

STEP 3:

Using Yahoo! Finance, download the last five years of weekly stock prices for each of your firms. For your analysis, you should use the “adjusted close” prices as these include the effect of dividend payments and splits. Use the “download to spreadsheet” function at the bottom of the page to capture the information in a spreadsheet. We will review this step in class. You will have to set the start and end dates to ensure that you cover all the weekly returns during the estimation period. Be mindful of whether the rows are in chronological or reverse-chronological order. You will need roughly 258 ( 52 X 5) returns for the estimation period. It is customary to align the start and end dates with a calendar quarter or month end. Whatever time frame you choose, take care to document the estimation period and use precisely the same estimation period for each of your stocks.

IMPORTANT: When you download the data into a spreadsheet format from the Yahoo! Finance web site, two things happen. First, the historical price data may be tabulated in reverse-chronological order (Yahoo! seems to keep changing their convention on the order). Second, the file type that opens in Excel may be in the “comma-separated-values” (.csv file) format. You should save the data into an .xls or .xlsx file before working with the data. Otherwise, you will lose all your formulas when you save your file. I require access to your cell formulas for grading purposes.

Regarding the chronological order issue, it makes not difference to me whether you list the prices in forward- or reverse-chronological order. You need only take care to use a formula for returns that properly accounts for the row ordering. It is a very common mistake to calculate the returns with formulas that reverse the prices and your grade will be penalized if you are careless about this.

STEP 4:

For the same five year period, download the closing values of the S&P 500. Copy this to your excel file. The ticker for the S&P 500 is ^GSPC. You will use this data to compute the weekly returns on the S&P500 index, which will we use as a proxy for the market return.

Page 3 Revised March 30, 2018

Step 5: Complete the following table (using adjusted close prices):

Stock Symbol

Firm Name High price over 5 years

Low price over 5 years

Arithmetic Average

Annual Return Over 5 years

Geometric Average Annual Return Over 5

years

Standard Deviation of

Annual Returns Over 5 years

^GSPC S&P 500

To calculate the standard deviation of annual returns you will need to convert the closing prices on the last trading day of the annual period into prices using a formula similar to that described in Step 6 below (except that you will use only the adjusted closing price for each year, not each week). Please note any significant differences between the geometric annual average and arithmetic annual average returns. You may find it easiest to calculate the annual returns by working with monthly priced data, using a 12-month period as the definition of a year, rather than the weekly price data you downloaded in STEP 4. This will be explained in class. STEP 6: Convert the adjusted prices into weekly returns. Do this for each of your four firms and for the S&P 500 Index. Remember to use the adjusted close data.

 1 1

t t t

t

AdjClose AdjClose Return

AdjClose 



 

STEP 7: Use the =SLOPE and =INTERCEPT functions in Excel to estimate the parameters of the market model. That is, estimate beta (β) for each company using the following equation where RM is the weekly return on the S&P 500:

𝑅 = 𝛼 + 𝛽𝑅 + 𝜀

IMPORTANT: Make sure that the dates of your firms’ returns line up with the dates of the S&P 500 returns. If you do not, then you will often find that you calculate a beta value that is very low (near zero) and a linear regression that is not statistically significant (i.e. low R2).

STEP 8: Download the Value Line Investment Survey Reports for each of your four firms and record Value Line’s estimate of each firm’s beta. Also, research and record Yahoo Finance’s estimate of each firm’s beta. Discuss any differences in the three estimates of beta. Which of the three estimates do you feel is most representative of the risk of each firm?

STEP 9: Determine the current risk-free rate (i.e., the yield on 30 year US Treasury Securities can be used as a proxy). You should use the same risk-free rate for each of the firms.

Page 4 Revised March 30, 2018

STEP 10: Complete the following table using the CAPM equation (Assume Market Risk Premium = 6%):

Firm

Calculated Beta

Value Line Beta

Yahoo Finance

Beta

Risk Free Rate

Required

Return based on

Calculated Beta

Required

Return based on

Value Line Beta

Required Return

based on Yahoo

Finance Beta

Firm 1 Firm 2 Firm 3 Firm 4

STEP 11: For each of the three sources of Beta, estimate the beta of a portfolio composed of your four companies, equally weighted (i.e., 25% each). Estimate the required return of this partially-diversified portfolio.

STEP 12: Conclude with a brief discussion about the appropriateness of your companies as potential investments. Based on your quantitative analysis and considering each firm’s risk in a well-diversified portfolio, would you now conclude that the firms were high risk, moderate risk or low risk investments, compared to investing in the S&P 500 index?

Submitting the project:

Please upload the Word and Excel documents as attachments to your submission, through Blackboard. If you work with a partner, please upload the project under one student’s Blackboard account and identify your partner in the MS-WORD document title page. Also upload the PDFs of the most recent ValueLine reports for each of your stocks. Feedback will be provider to the submitter through Blackboard and both students will be assigned grades in Blackboard.

Reviewing the project:

If you would like your calculations and spreadsheets to be reviewed before final grading, please feel free to submit it at least one week before the due date. You will be able to submit the project multiple times, up until the due date.