please answer all questions on ecxel 1. Assume that the required return for Nike

ID: 2780794 • Letter: P

Question

please answer all questions on ecxel

1. Assume that the required return for Nike is 7%. Given the Dividend and the 1-year target estimate (1y Target Est), what is the value of the stock? would you buy it 2. A stock just paid a dividend of $3 and dividends are expected to grow at 5% per year forever. If the required return is 11%, what is the fair price of the stock?

3. A stock will pay a dividend of $2 next year, $2.50 in2 years and $3 in 4 years. You expect that you could sell the stock for $75 at that time. If you have a required return of 10% for this stock, what is the fair value? If the price of the stock is $65, should you buy it?

NIKE, Inc. (NKE) NYSE-Nasdaq Real Time Price Currency in USD Add to watchlist 55.20 +0.13 (+0.24%) As of 3:39PM EDT. Market open Summary Chart Conversations Statistics Profile Previous Close Open Bid 90.06B 0.50 23.50 2.35 55.07 Market Cap 55.15 Beta 55.26 x 700 PE Ratio (TTM) 55.27 x 2100 EPS (TTM) 4.60-55.32 Eanings Date Ask Dec 18,2017 Day's Range 52 Week Range49.31-60.53 Volume Avg. Volume Dec 22, 2017 Forward Dividend 0.72 (1.29%) Ex-Dividend Date 2017-08-31 59.00 & Yield ,787,017 9,772,487 y Target Est

Explanation / Answer

value of stock = dividend paid + present value of 1 yr target price

dividend paid =

0.72

present value of 1 yr target price = target price/(1+r)^n

59/(1.07)^1

55.14019

value of stock

.72+55.14

55.86

dividend declared

growth rate

expected dividend = dividend paid*(1+grwoth rate)

3.15

required return

11%

fair value of stock = expected dividend/(required return-growth rate)

52.5

Year

cash flow

present value of cash inflow = cash inflow/(1+r)^n r= 10%

1.818182

2.5

2.066116

2.253944

56.34861

fair value of share

sum of present value of cash flow

62.48685

No I would not buy the stock as it fair value is 62.49 which is less than the market value of 65 so stock is over valued would not be purchased