AutoSave H Off Home Insert Drawi Page Layout Formulas Data: Review X Times New Roman 10 A A EE T = ID Paste BIU SUPP Cipboard F EX fx File 5. e View Help Wrap Text Merge & Center Alignment A B C D E F G H I J K 5 Stock Valuation 6 7 Estimating the present value of a share of common stock with the Dividend Discount Model. 8 9 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 10 carning 1%. What is the value today of one share of The ABC Company? 11 12 13 14 15 16 Given: 16 Definition Notation: Inputs: D 17 The expected upcoming (end of period 1) dividend 18 The expected upcoming (end of period 2) dividend D 19 The expected upcoming (end of period 3) dividend D3 20 r The required rate of return on the firm's common stock. The expected future stock price at the Horizon time 21 PH 22 The present value of the stock share (market price) Po 23 24 Finance Concept: P, PV of expected future dividends Ready H Unde 017 4 Accessibility: Investigate Type here to search 493 S A Fort Search (Alt+Q) 15 Number $4.00 $4.50 $5.00 1.0% $70.00 ? F F Conditional Format as Cell Formatting Table Styles Styles L H SI Insert Delete Format Cells M N E D. O 280 Sort & Find & Filler Select Editing P E a Comments O ohn's S Analyze Sensitivity Data Analysis Sensitivity Q R Share 130% D AutoSave H Off Home Insert Drawi Page Layout Formulas Data Review View Help X Times New Roman 10 EE T = Wrap Text ID A A A A Paste BIU- 93 Merge & Center SPP Cipboard F Alignment fx B C D E F G H I J 24 Finance Concept: P, PV of expected future dividends 25 Po D/(1+r) + D/(1+r) +...+(Div +PH) / (1+r) Where H is the horizon or specific investment time period. 26 27 28 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. 29 30 31 Excel Solution Using a Table: 33 32 27 33 Today Year 1 Year 2 Year 3 34 a Interest Rate 35 25 b Time $ 36 c Stock Price at year 3 S - $ - $ 70.00 37 37 d Dividends - $ 4.00 S 4.50 S 5.00 38 e Total Cash Flows - $ 1.00 S 4.50 S 75.00 39 f PV interest factor - 1.00 1.00 1.00 40 g Present Value $ 83.50 $ 4.00 S 4.50 S 75.00 41 42 43 1. It is easier to enter the known inputs before you enter the formulas. 44 Enter the interest rates, the dividends for years 1 through 3, the stock price at year 3. In row e find the Total Cash Flows by adding row c to row d. 45 20 File 5. e Unde 017 Ready H A Accessibility: Investigate Type here to search Fort 15 - 1 Search (Alt+Q) 2 Number F F Conditional Format as Coll Formatting Table Styles Styles L K