I need the answer for assignment number 2

Assign ment #2 Stock Valuation A. Choose a stock that interests you. Utilizing Bloomberg (or other financial websites) as a source of data, collect the following information:

Assignment#1 Loan Amortization Bill decides to purchase a new car with a sticker price of $25,000. The car dealer offers Bill either $2,000 cash back or 2% financing for 5 years. If Bill takes the financing, he will make 60 equal monthly payments. Otherwise, he will pay $23,000 today for the car. (a) Calculate the monthly payment required if Bill agrees to the sticker price of $25,000 and finances the car at 2% per year. (20 points) (b) Determine the interest and principal for each payment if Bill agrees to the sticker price of $25,000 and finances the car at 2% per year. (20 points) (c) Prepare the amortization schedule for the loan using an Excel spreadsheet. (30 points) (d) Prepare a graph in Excel that shows the portion of each payment that goes to interest and the portion that goes to principal. (10 points) (e) Based on the time value of money, should Bill take the $2,000 cash back or the 2% financing? Clearly explain and defend your choice. (20 points) SAMPLE EXAMPLE Assignment #2 Stock Valuation A. Choose a stock that interests you. Utilizing Bloomberg (or other financial websites) as a source of data, collect the following information: (30 points) a. Current stock price b. The last dividend paid (0 ) c. The stock's Beta d. The annual expected growth rate of earnings (g) e. The rate of return on the market (S&P 500 Index) f. The risk-free rate ( ) B. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the intrinsic 0 ) (30 points) stock price ( C. Based on your above calculations, compare the calculated price with the current market price and indicate whether the stock price is overvalued, undervalued, or at equilibrium? Explain. (10 points) D. Now, assume that your company has just released a new product and will be experiencing supernormal growth of 25% for the next three years. In Excel, use the information in \"A\" and the Discounted Dividend Model for Nonconstant Growth Stocks and solve for the intrinsic stock price 0 ). (30 points) ( REFER TO THE SAMPLE BELOW FOR FORMATTING PURPOSE: The instructions below will help you do assignment 2. 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 ( ) e. The annual expected growth rate of earnings x x 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: x 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. x 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). x Next, go to the U.S. Dept. of the Treasury: http://www.treasury.gov/resource-center/data-chartcenter/interest-rates/Pages/TextView.aspx?data=yield o Use the 1yr rate of .13% as your risk-free rate (again, this is from 2014) x B. 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. In Excel, use the Discounted Dividend Model for Constant Growth Stocks and solve for the ) intrinsic stock price ( 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