6.9 Chapter 6 HW - Spreadsheet Mastery // Stock // Problem 1 please fill out the table, steps are listed.

A B C D E F G H I J K 4 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 54, 54.50, and S5 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 earning 8%. What is the value today of one share of The ABC Company? 11 12 13 14 15 Given: 16 Definition Notation: Inputs: 17 The expected upcoming (end of period 1) dividend D $4.00 18 The expected upcoming (end of period 2) dividend D2 $4.50 19 The expected upcoming (end of period 3) dividend D3 $5.00 20 The required rate of return on the firm's common stock r 0.08 21 The expected future stock price at the Horizon time PH $70.00 22 The present value of the stock share (market price) PO 23 24 Finance Concept: Po =PV of expected future dividends 25 Po=D1/(1+1)+ +D2/(1+1)2 +...+(Diva +Pp)/(1+1)H 26 Where H is the horizon or specific investment time period 27 28 In Words: The Present Value of a stock share depends on the stream of expected dividends. 29 This is the generalized stock valuation model which is also how we value bonds 30 31 Excel Solution Using a Table: 32 33 Today Year 1 Year 2 Year 3 34 a Interest Rate