Chapter_08 - Ross, Westerfield, and Jordan's Spreadsheet...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
by Brad Jordan and Joe Smolira Version 9.0 Chapter 8 In these spreadsheets, you will learn how to use The following conventions are used in these spr 1) Given data in blue 2) Calculations in red NOTE: Some functions used in these spreadsheets may the "Analysis ToolPak" or "Solver Add-In" be installed in To install these, click on the Office button then "Excel Options," "Add-Ins" and select "Go." Check "Analysis ToolPak" and "Solver Add-In," then click "OK." Ross, Westerfield, and Jordan's Spreadsheet Master Fundamentals of Corporate Finance, 9th edition Formatting values as text Concatenation Web queries Stock charts
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
e the following Excel functions: readsheets: y require that in Excel.
Background image of page 2
Chapter 8 - Section 1 Common Stock Valuation Constant Dividend Growth Current dividend: $2.40 Dividend growth rate: 5.00% Required return: 13.00% With this growth rate, the dividend next year will be: Dividend next year: $2.52 So, the stock price today with the constant dividend growth model is: Stock price today: $31.50 g Stock price 0.00% $18.46 1.00% $20.20 2.00% $22.25 3.00% $24.72 4.00% $27.73 5.00% $31.50 6.00% $36.34 7.00% $42.80 8.00% $51.84 9.00% $65.40 10.00% $88.00 11.00% $133.20 12.00% $268.80 Graphing the stock price versus the growth rate, we can see the impact of the growth rate: Unlike bond pricing, Excel does not have built-in functions for stock pricing, so we need to cre we have a stock with the following: The constant dividend growth equation is just the present value of a growing perpetuity, but w Using the same information from above, we can calculate the stock price for various growth r
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Nonconstant Growth Suppose a stock will pay the following dividends and has the following required return: Year 1: $1.00 Year 2: $2.00 Year 3: $2.50 Required return: 10.0% After the third year, the dividends will grow at: 5.0% What is the price of the stock? First, we need to find the price of the stock when it begins a co Price in Year 3: $52.50 The price today is the present value of the future dividends, plus the present value of the futur Price today: $43.88 Two-Stage Growth -2.00% 0.00% 2.00% 4.00% 6.00% $- $50.00 $100.00 $150.00 $200.00 $250.00 $300.00 Stock Price for Various Dividend G Dividend Growth Rate Stock Price
Background image of page 4
Example 8.5: Two-Stage Growth The Highfield Company is going through a period of fast growth. You want to know the curren Initial growth rate: 20.0% Years of initial growth rate: 5 Second growth rate (perpetual): 4.00% Required return: 10.00% Dividend just paid: $2.00 First, we need to find the stock price when the dividends achieve a constant growth rate forev The stock price in year 5 will be: $86.26 So, the stock price today is: Stock price today: $66.64 RWJ Excel Tip Three-Stage Growth Current dividend: $1.50 Required return: 11% Perpetual growth rate: 6% Dividend growth Dividend PV of dividend 1 28.00% $1.92 $1.73 With two-stage dividend growth, it is simply a matter of inputting the correct equations in Exc then use this stock price in the second part of the equation. So what's unique about the above calculation? Change the number of years of initial growth After an equal sign at the beginning of a cell, using quotes causes Excel to treat anything ins (&) as a concatenating operator, that is, an operator that joins the contents of two or more ce concatenate twice. In between the ampersands, we used the TEXT function to tell Excel to g
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 30

Chapter_08 - Ross, Westerfield, and Jordan's Spreadsheet...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online