CAPM Analysis and Stock Valuation - Deliver Different Beta Estimates - Finance Assignment Help

Assignment Task :

CAPM & Stock Valuation Project  

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 of the internet which can then be manipulated in Excel.  Finally, you will tie the concept of beta and the CAPM to stock valuation. The Yahoo Finance  site has a wealth of financial information on companies and countries from all over the world.  Once you arrive at this site you will find a box titled "Get Quotes" which you need to type in the  stock symbol (if you are unsure of the stock symbol go to "Symbol Lookup"). To download  financial data from the internet follow the detailed instructions in the text box below:  

Instructions for Downloading Stock Data  

• In the Get Quotes box type in the stock symbol (for example Verizon is VZ and the  S&P500 index is ^GSPC) and click the GO box.  

• Click on Historical Data from the Quotes section under the more on VZ (for example)  column. Click on the Time Period in the Set Date Range (then and enter the date range as  instructed below and click Done button, Show: Historical Prices, set Frequency to  Monthly, and click Apply. 

• The first page of data should now be visible on the screen. Click the Download link. Save  the data to a file, it will be saved as a CSV file.  

• Open Excel and then open the file you saved.  

• At this point you should have seven columns including the; Date, Open, High, Low and  Closing share prices for that month, plus Volume and Adj Close data for each month. You can only need the Date, Open and Close columns for each stock or index. 

• To download dividend payments, change the Show setting to Dividends Only, click  Apply and export and save this info to a spreadsheet. You will need to do this for any of  your stocks that have paid dividends over the time period below. Alternatively, you can  just enter the dividends for each stock into your spreadsheet from the Yahoo Finance  price table itself. Any stock index already includes dividends. 

1. First download into Excel the Best Buy (BBY), Home Depot (HD) and S&P 500 Stock  Index (^GPSC) monthly price data for the 5-year period from the Oct. 1, 2015 to Sept.  30, 2020 using the instructions outlined above. Using the Open and Close price data for  each month (the date given is the first trading day for that month: i.e. the open and  close for the month) to calculate the monthly percentage holding period returns for  each stock and index where each month’s Open price is the beginning of the month  price and each month’s Close price is the end of the month price. You can get rid of the  other data provided for each month. In addition, you will need to include dividend  payments for each stock to calculate monthly returns for the months that they made a  dividend payment (the index doesn’t have dividend payments). For example,  September’s monthly return is (Sept Close – Sept. Open + any Sept Div)/Sept Open.  You should have 60 monthly returns for each stock and index from Oct. 2015 to Sept. 2020. Helpful Hint: I strongly urge creating a table of the appropriate opening &  closing prices and dividends for each stock along with opening and closing prices for  the S&P 500 Index on a separate worksheet and then do your calculations on this  “fresh” worksheet. 

2. What is the average monthly return for each stock and the S&P 500 index over the  entire time period? Convert the average monthly return for each stock and index into  an APR (annualized rate) by multiplying your monthly average for each stock by 12.  Please convert your answers to percentages  

3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel.  

4. Now calculate the beta for each stock using Excel’s Slope or Linest function to use  estimate the slope of the linear regression line, which is beta. Use the S&P 500 index  as the market portfolio in finding beta.  

5. Now use the CAPM/SML Equation to estimate each stock’s required return. Use 8.5%  as the required market return and the recent 10-year Treasury rate of 0.85% as the risk free rate. Would you recommend buying BEST BUY and/or Home Depot if your APR  average for each stock from #2 is your expected return? Explain your answer.  

6. Construct a graph of the Security Market Line given by the parameters in #5. Point out  the SML returns you calculated in #5 on your SML. Also, find and plot BEST BUY’s  and Home Depot’s expected returns from the information given below gathered from  their recent Yahoo Finance stock quotes against each stock’s beta on your SML graph.  Would you recommend buying BEST BUY and/or Home Depot based on this  information? Explain your answers. Note: you are evaluating each stock individually,  not in comparison in one another.  

7. Now, assume that Home Depot’s current price is in equilibrium and is a constant  growth stock. What is Home Depot’s expected constant growth rate based on your  CAPM return? Use price and dividend data from #6. 

8. Use the variable dividend growth valuation model to value BEST BUY. Here are the  projections. 

• Year 1 dividend 2.40 

• Dividends are projected to grow 12% annually in years 2 through 4 • After year 4, dividends have a projected constant growth rate of 10% Using your Best Buy CAPM required return from #5, what is your valuation of BEST  BUY’s common stock today? Would you recommend buying BEST BUY’s stock  today and why? Use current price in #6 to help aid your recommendation.

This Fin 221 - Finance Assignment has been solved by our Finance Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.