Reference no: EM131955327

You have $100,000 to invest in 4 stocks. Pick each stock from a different sector (for example, technology, energy, consumer products, automotive).

Prepare the following using Excel. Make sure that your spreadsheets are well formatted, clear and understandable. Feel free

1) Identify the expected returns using Yahoo Finance. Look at current stock price, expected stock price in one year and the annual dividend. The expected stock price in one year less the current stock price plus the annual dividend gives you the return on your investment (expected appreciation in stock price plus the annual dividend you received). Divide this by the initial investment to get the return on your investment.

2) Once you have the expected return for each stock, identify the overall expected return. This is the return of all stocks weighted by the portion they constitute of your total investment. See slide 13-12 (from Chapter 13).

3) Develop the expected return for your portfolio of stocks using the Capital Asset Pricing Model. See slide 13-41. Assume the risk free rate is 2.6% (approximate US Treasury 10 yr bill rate). Assume the market risk premium is 6.7% (long term average stock market return, including dividends). Because of this, you do not need to add in the dividend yield of your stocks, unlike what I said in class. Calculate the average return for your portfolio of stocks. Compare the returns for each stock and the overall portfolio to what you calculated in 1) above.

4) Are there any reasons why one set of values may be different from the other? If the expected returns from 1) above are higher than those calculated in 3), it may be due to recent favorable conditions for the stack which are resulting in strong estimates for the stock price one year from now. See if you can find any reasons for possible differences.

Please put your name on the Excel sheet and save the file using your name as well. You can use multiple worksheet in an Excel workbook if you want, or answer 1 through 4 above on one worksheet. Deposit in the drop box I have set up under Assignments in Canvas.