Fe A1 13 14 15 16 17 18 19 20 21 XO 22 23 24 Copy- Format P Clipboard A Inst Draw Page Layout Times New Roman-10-A A Given: Formula D 25 26 27 28 29 30 31 Excel Solution Using a Table: Stack Score Data Review Higment Type here to search E Stock Valuation Definition The expected upcoming (end of period 1) dividend The expected upcoming (end of period 2) dividend The expected upcoming (end of period 3) dividend The required rate of return on the firm's common stock The expected future stock price at the Horizon time The present value of the stock share (market price) Finance Concept: P.-PV of expected future dividends Estimating the present value of a share of common stock with the Dividend Discount Model Problem 1: We are fairly certain The ABC Company will pay $4, $4.50, and $5 in dividends at the end of years 1, 2, and 3 respectively. We expect the stock share to sell for $70.00 at the end of year 3. An investment with comparable risk is earning 2%. What is 10 the value today of one share of The ABC Company? 11 12 View Help G H $-% I Notation: Inputs D D D F PH P P-D,(1+1) + Dy(1+) +...+ (Div +P)/ (1+r)" Where H is the horizon or specific investment time period. $4.00 $4.50 $5.00 2.0% $70.00 ? In Words: The Present Value of a stock share depends on the stream of expected dividends. This is the generalized stock valuation model which is also how we value bonds. J K L D. Inset Delte form Cell M Clear- N S&Find & O Qii 90F Clear Comments 4 My P A Q 12:20 AM 6/23/2022 A 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 FA ACA Ready Copy Format Clipboard 30 31 Excel Solution Using Table 32 33 34 A Times New Roman-10-AA F Stock Score Acomability inumstige 2-A. D Interest Rate Time Stock Price at year 3 Dividends Formu Total Cash Flows PV interest factor Present Value Type here to search E Today S - . F Year 1 $ 1 $ G Year 2 3. Next we find the PV of each cash flow in row g 2 H $%9A Year 3 1. It is easier to enter the known inputs before you enter the formulas. Enter the interest rates, the dividends for years I through 3, the stock price at year 3. In row e find the Total Cash Flows by adding row c to row d. 3 4. Lastly, we sun the PVs to get the PV of the annuity in cell 40. In cell E40 enter the formula: sum(F40:40) I Conditional Format Table J Pyles 2. Now we are ready to calculate the PV Interest Factor for each cash flow in row f. PVIF-1(1+r) In cell F39 enter the formula K SI520
