Excel Homework: Is your stock overvalued or undervalued?
Your next homework is to help you understand how to evaluate a stock.
- Pick your own stock, one of the publicly traded stocks. Everyone is supposed to work on a different stock. If you have not picked your own stock, please tell me what you will pick by email.
- Compute the beta coefficient of the stock using the following procedure:
- Download the historical data of your stock price (closing price) from the website, such as Yahoo.com: 3 year data.
- Download the historical data of S&P 500 index (closing price) from the website, such as Yahoo.com. 3 year data.
- Convert the stock price data into return data:
Return (today) = (Today’s Price – Yesterday’s Price) / Yesterday’s Price
- Use the return data to compute the beta coefficient by regressing your stock return on the S&P 500 return.
Rj = α + β Rm + ε
The beta coefficient is the coefficient estimate of x variable in the regression results. In Excel, the X variable is the market return, and the Y variable is your stock return.
- In estimating the above equation, use Excel. When you go to Excel/Data, you will find Data Analysis. Click Data Analysis. Choose “Regression.”
Note] In case your computer is not yet equipped with Data Analysis, you can install it using the following procedure:
- Click the Filetab, click Options, and then click the Add-Ins
- In the Managebox, select Excel Add-ins and then click Go.
- In the Add-Insbox, check the Analysis ToolPak check box, and then click OK.
- Determine the equilibrium return based on the CAPM (or SML). In the calculation, use 10-year T-bond rate as the risk-free rate, and 7% as the market risk premium.
- Draw the SML using the 10-year T-bond rate and 7% as the market risk premium. To draw the SML use Insert/Charts/x y (scatter). You will be able to draw an SML. Then click on “+” to write chart elements and use “Trendline.”
- See where your stock is located. To compute the expected return on your stock, use the 1-year target price and the expected dividend available from Yahoo.com. Is it located above the SML or below? Then you will be able to say whether your stock is currently undervalued or overvalued or fairly valued.
Once you finish your work, email me your Excel results. Your Excel results should consist of the 3 year data on your stock and S&P 500 index, regression results, SML graph, and your valuation decision.
TO GET THIS OR ANY OTHER ASSIGNMENT DONE FOR YOU FROM SCRATCH, PLACE A NEW ORDER HERE