I need help for Finance assignment

Assignment #2 Stock Valuation

A. Choose a stock that interests you. Utilizing Bloomberg, Yahoo Finance, Google Finance, etc. as a

source of data, collect the following information:

a. The stock’s Beta

b. The rate of return on the market (S&P 500 Index)

c. The risk-free rate (𝑟𝑅𝐹) d. The last dividend paid (𝐷0) e. The annual expected growth rate of earnings

 Go to google finance: https://www.google.com/finance and type in a publicly traded company that you are familiar with. For example, Microsoft:

o Collect the current stock price, the most recent dividend payout (D0), and the Beta (all examples boxed in red above). Assume Google uses a year of data to calculate their Beta.

o

 Next, interact with the MSFT chart so that it shows 10yrs of data:

 Note the dividend payouts throughout the 10 years ranges from $.08 in 2004 to $.28 in 2014. If

we use the PV=FV/(1+r)t formula and solve for r to get the geometric average increase in

dividends. So, .08 = .28/(1+r)10, solving for r gets us 13.35%. This will be used for g.

 Next, go to Kenneth French’s data library website: http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html

o Note that the Return on the Market minus the risk-free rate (Rm-Rf) is 23.52% for the last 12

months (this was noted back in 2014).

 Next, go to the U.S. Dept. of the Treasury: http://www.treasury.gov/resource-center/data-chart- center/interest-rates/Pages/TextView.aspx?data=yield

o Use the 1yr rate of .13% as your risk-free rate (again, this is from 2014)

 Now, use the CAPM to calculate the Required Rate of Return for MSFT based on the information you have collected.

Re = Rf + B(E(Rm)-Rf)

So, with MSFT

Re = .13% + .9(23.52%) = 21.30%

You would use 21.30% for the Required Rate of Return for the Dividend Growth Model.

B. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the

intrinsic stock price (𝑃0̂)

With the Microsoft Example, dividends are paid quarterly. For sake of time and ease, just use the

annual amount of dividends for D0, so .28 x 4 = 1.12. Using annual dividends will match up with our

annual figures for R and g. Next, create D1 = D0(1+g) = 1.12(1.1335) = 1.26

So, putting it all together:

We have D1 = 1.26, R = .2130, and g = .1335

using Po = D1/(R-g), we would have Po = 1.26/(.2130 - .1335) = $15.85.

Based on your above calculations, compare the calculated price with the current market price and

indicate whether is the stock price overvalued, undervalued, or at equilibrium? Explain.