Pepsi or Coca-Cola The estimation of company betas is normally brushed over in many introductory finance texts. This often leads students to view beta as some magical number and not until later courses in finance that they realize that the choice of different returns, indices and intervals examined can deliver different beta estimates. This internet exercise takes you through the basic calculation of beta using Excel in order to help demystify a company beta. Along the way you will find how to download share price and index data online to be used for analysis in Excel. Yahoo Finance (finance.yahoo.com) has a wealth of financial information on companies and countries from all over the world. Once you arrive at this site you will find on the top of the webpage a search box where you can type in the stock symbol or name (if you are unsure of the stock symbol go to "Symbol Lookup"). To download financial data follow the detailed instructions below: Step one: download monthly stock price for Pepsi (PEP), Coca-Cola (KO) and S&P500 Index (SPY) price for the past 5 years so that we can use the prices to calculate returns. The tickers symbols are in the parentheses. Step 1: Download Stock Data • In the search box type in the stock symbol (for example Pepsi is PEP and the S&P500 index is SPY) and click on Search. • You will land on the summery page of the stock. Click on the Historical Data tab. Under this section, you will see different historical data of the stock. There are 3 fields where you can select different values. • Under “Time Period”, select the time range from 09/01/16 to 10/01/21; under “show”, select Historical Prices; under “Frequency”, choose monthly. Click “Apply”. Then you will be presented with monthly price of the stock for 61 months. • Click “download data” under the “Apply” button, then the data will be downloaded into a .csv file which can be opened with Excel. Step 2: Calculate monthly returns • The price we are interested in is the adjusted close price because it is adjusted for dividends and stock splits. Knowing this, we can calculate the returns. Specifically return of this moth can be calculated as Rthis_month = (Pricethis_month- Pricelast_month) / Pricelast_nonth • Note there are 61 monthly prices recorded, which means you can get 60 monthly returns. • Calculate the return for both stocks and S&P500, record and align them with the dates. • Compute the average monthly return of Pepsi and Coca-Cola (Excel function: AVERAGE()). If you use the average return as your expected future return, which stock should I choose to buy? Step 3: Stand-alone risk • We use standard deviation for the monthly return to measure volatility (standalone risk). Excel Function: STDEV.S() • Calculate the standard deviation for Pepsi, Coca-Cola, and S&P500. Which of the three has the highest standalone risk, which has the lowest? Why? • If you form a portfolio with 50% Pepsi and 50% Coco-cola, what would the monthly return be? Calculate the returns and list them along with the stand-alone stocks. • Calculate the standard deviation of the portfolio. Is it larger or smaller than the standalone stocks? What would be the possible explanations for that? Step 4: Beta and systematic risk • We use beta to measure systematic risk. Explain in your own words what beta is. In order to find betas, we use linear regressions. • We use the return of S&P500 as the market return. We will regress the return of the individual stocks (Y in the regression) on the return of S&P500 (X in the regression). Use the linear regression in data analysis toolbox in Excel for this procedure, and the coefficient of X is the beta we are looking for. Please refer to page 388 in the textbook for details. • What is the beta of Pepsi? What is the beta of Coca-Cola? • We use long-term treasury rate as the risk-free rate, which is 2.4% annually. And we use the average return of S&P500 we calculated as the market return. Keep in mind that the risk- free rate is an annual rate. The average return is a monthly return. You need to convert the monthly return to annual return by multiplying it by 12. • Use the CAPM model to calculate the expected return for both stocks. Which stock has higher expected return according to CAPM? Which stock should we choose to buy if we use these returns as the expected future returns of the stocks? Step 5: Which rate to use? • So far, we have seen that we can either use average historical returns or the expected returns from CAPM as our estimates for possible future returns of stocks • Do these two approaches agree with each other in this particular example? • What are the possible pros and cons of each approach?