This document provides an overview and examples of using Excel to value stocks using different dividend growth models, including constant growth, differential growth, and multi-stage growth models. It demonstrates calculating stock prices given dividend amounts, growth rates, and required rates of return. Graphs are shown illustrating how stock price varies with different dividend growth rates. The document concludes with an example candlestick chart of Apple stock prices for May 2021.
This document provides an overview and examples of using Excel to value stocks using different dividend growth models, including constant growth, differential growth, and multi-stage growth models. It demonstrates calculating stock prices given dividend amounts, growth rates, and required rates of return. Graphs are shown illustrating how stock price varies with different dividend growth rates. The document concludes with an example candlestick chart of Apple stock prices for May 2021.
This document provides an overview and examples of using Excel to value stocks using different dividend growth models, including constant growth, differential growth, and multi-stage growth models. It demonstrates calculating stock prices given dividend amounts, growth rates, and required rates of return. Graphs are shown illustrating how stock price varies with different dividend growth rates. The document concludes with an example candlestick chart of Apple stock prices for May 2021.
This document provides an overview and examples of using Excel to value stocks using different dividend growth models, including constant growth, differential growth, and multi-stage growth models. It demonstrates calculating stock prices given dividend amounts, growth rates, and required rates of return. Graphs are shown illustrating how stock price varies with different dividend growth rates. The document concludes with an example candlestick chart of Apple stock prices for May 2021.
Download as XLSX, PDF, TXT or read online from Scribd
Download as xlsx, pdf, or txt
You are on page 1/ 20
Ross, Westerfield, Jaffe, and Jordan's Spreadsheet Master
Corporate Finance, 13th edition
by Brad Jordan and Joe Smolira Version 13.0
Chapter 9 In these spreadsheets, you will learn how to use the following Excel fu
Stock charts
The following conventions are used in these spreadsheets:
1) Given data in blue
2) Calculations in red
NOTE: Some functions used in these spreadsheets may require that
the "Analysis ToolPak" or "Solver Add-In" be installed in Excel. To install these, click on the File tab then "Excel Options," "Add-Ins" and select "Go." Check "Analysis ToolPak" and "Solver Add-In," then click "OK." the following Excel functions:
eadsheets:
equire that Chapter 9 - Section 1 The Present Value of Common Stocks
Unlike bond pricing, Excel does not have built-in functions for stock pricing, so we need to create our own equations
Example 9.2 Stock Valuation
Suppose an investor is considering the purchase of a share of the Utah Mining Company. The dividend information a
So, the stock price today with the constant dividend growth model is:
Stock price today $ 60.00
The constant dividend growth equation is just the present value of a growing perpetuity, but we could caution that Using the same information from above, we can calculate the stock price for various growth rates.
Graphing the stock price versus the growth rate, we can see the impact of the growth rate:
Stock Price for Various Dividend Growth Rates
$60
$50
$40 k Price
$30 $60
$50
$40
Stock Price $30
$20
$10
$- -2% 0% 2% 4% 6% 8% 10% Dividend Growth Rate
Differential Growth With differential dividend growth, it is a matter of inputting the correct equations in Excel. Because the equation is s use this stock price in the second part of the equation.
Example 9.3: Differential Growth
Consider Elixir Drug Company, which is enjoying rapid growth from the introduction of its new back-rub ointment. Y have gathered the following information:
Initial growth rate 15.0%
Years of initial growth rate 5 Second growth rate (perpetual) 10.00% Required return 15.00% Dividend next year $ 1.15
First, we need to calculate the present value of the first 5 dividends, which will be:
We have the present value of the first 5 dividends. Now, we need to calculate the present value of the future stock
Stock price in Year 5 $ 44.25
And the present value of this future stock price is:
Present value of future stock price $ 22.00
The current stock price is the present value of the known dividends, plus the present value of the future stock price
Current stock price $ 27.00
Three-Stage Growth
Suppose we have a stock that that has dividend growth in three (or even more) stages. We can set up a more gener important to remember that the price of a share of stock is nothing more than the present value of the future divide
We have the present value of the first 15 dividends. Now, we need to calculate the present value of the future stock
Stock price in Year 15 $ 357.00
And the present value of this future stock price is:
Value today of future stock price $ 74.62
The current stock price is the present value of the known dividends, plus the present value of the future stock price
Current stock price $ 121.44
You may wonder why we chose to calculate the dividends for the first 15 years, then use the dividend growth mode project supernormal growth for more than 15 years. In other words, this spreadsheet should accommodate most st extend for more than 15 years, we could easily alter the spreadsheet. e need to create our own equations. We will begin with constant growth in dividends.
mpany. The dividend information and required return are:
petuity, but we could caution that the equation is very sensitive to the growth rate estimate. ous growth rates.
owth rate:
dend Growth Rates
6% 8% 10% 12% h Rate
s in Excel. Because the equation is so involved, we will calculate the stock price at Time t, then
on of its new back-rub ointment. You want to know the current value of the stock price and
e:
e present value of the future stock price. The price of the stock in Year 5 will be: sent value of the future stock price, or:
tages. We can set up a more general model in Excel to handle more growth rate changes. It is e present value of the future dividends. Suppose we have a stock with the following:
he present value of the future stock price. The price of the stock in Year 15 will be: sent value of the future stock price, or:
hen use the dividend growth model. The reason is that it would be somewhat unusual to heet should accommodate most stock pricing models. If the supernormal growth rate did Chapter 9 - Section 5 The Stock Markets
A group of analysts called technical analysts examine historic stock prices in an attempt to find patterns in the stock Whether technical analysis is actually profitable we will leave to a later time. For now, we will show one of the stock stock for May 2021.
Below you will see an open-high-low-close, or candlestick, chart for Apple during May 2021.
To graph the candlestick chart, we selected the data for the opening price, high price of the day, low price of the da the open-high-low-close chart. We also made a couple of changes to the original chart. We selected the Y axis and c candlestick. We right-clicked on a down day, selected Format down bars, and changed the color to red. We followed
Candlestick charts show you how the stock performed on a particular day and over the entire period. The high and l lines for each day. For example, you can see the range of prices on May 3rd. At the end of the day, Apple stock close Since Apple stock closed down on May 10th, the top vertical line is the opening price of the day and the bottom ver ric stock prices in an attempt to find patterns in the stock price. If patterns are discovered, technical analysts argue that it is po ave to a later time. For now, we will show one of the stock price charts that are built into Excel's graphing functions. To the righ
e opening price, high price of the day, low price of the day, and closing price. Excel requires that these values be in this order in hanges to the original chart. We selected the Y axis and changed the range for the stock price. Next, in the default setting, Exce mat down bars, and changed the color to red. We followed the same procedure for the up days and made the up candlesticks gr
a particular day and over the entire period. The high and low price of the day are shown as the top and bottom of the vertical li rices on May 3rd. At the end of the day, Apple stock closed up for the day, so the top vertical line is the closing price of the day al line is the opening price of the day and the bottom vertical line is the closing price of the day. Excel has several other default al analysts argue that it is possible to trade on these stock price patterns and make a profit. aphing functions. To the right of this spreadsheet you will find price information for Apple
7/2021
hese values be in this order in the table. We then went to Insert, Other Charts, and selected xt, in the default setting, Excel graphs down days as a black candlestick and up days as a white d made the up candlesticks green.
and bottom of the vertical line, respectively. The open and close are shown as the horizontal is the closing price of the day, while the bottom vertical line is the opening price of the day. xcel has several other default stock price charts. We'll leave these for you to explore. Date Open High Low Close 5/3/2021 132.04 134.07 131.83 132.54 5/4/2021 131.19 131.49 126.70 127.85 5/5/2021 129.20 130.45 127.97 128.10 5/6/2021 127.89 129.75 127.13 129.74 5/7/2021 130.85 131.26 129.48 130.21 5/10/2021 129.41 129.54 126.81 126.85 5/11/2021 123.50 126.27 122.77 125.91 5/12/2021 123.40 124.64 122.25 122.77 5/13/2021 124.58 126.15 124.26 124.97 5/14/2021 126.25 127.89 125.85 127.45 5/17/2021 126.82 126.93 125.17 126.27 5/18/2021 126.56 126.99 124.78 124.85 5/19/2021 123.16 124.92 122.86 124.69 5/20/2021 125.23 127.72 125.10 127.31 5/21/2021 127.82 128.00 125.21 125.43 5/24/2021 126.01 127.94 125.94 127.10 5/25/2021 127.82 128.32 126.32 126.90 5/26/2021 126.96 127.39 126.42 126.85 5/27/2021 126.44 127.64 125.08 125.28 5/28/2021 125.57 125.80 124.55 124.61 Adj Close Volume 132.32 75135100 127.63 137564700 127.88 84000900 129.52 78128300 130.21 78892700 126.85 88071200 125.91 126142800 122.77 112172300 124.97 105861300 127.45 81806500 126.27 74244600 124.85 63342900 124.69 92612000 127.31 76857100 125.43 79295400 127.10 63092900 126.90 72009500 126.85 56575900 125.28 94625600 124.61 71311100 Chapter 9 - Master It!
In practice, the use of the dividend discount model is refined from the method we presented in the textbook. M 5 years and then estimate a perpetual growth rate at some point in the future, typically 10 years. Rather than h fast growth period to the perpetual growth period, linear interpolation is applied. That is, the dividend growth For example, if the high growth period is 15 percent for the next 5 years and the dividends are expected to fall the dividend growth rate would decline by 2 percent each year.
The Value Line Investment Survey provides information for investors. Below, you will find information for AbbV
2020 dividend $ 4.72
5-year dividend growth rate 16.5%
Although Value Line does not provide a perpetual growth rate or required return, we will assume they are:
Perpetual growth rate 4.0%
Required return 11.0%
a. Assume that the perpetual growth rate begins 11 years from now and use linear interpolation between the hig a table that shows the dividend growth rate and dividend each year. What is the stock price at Year 10? What
b. How sensitive is the current stock price to changes in the perpetual growth rate? Graph the current stock price out.
Instead of applying the constant dividend growth model to find the stock price in the future, analysts will often multiple
Payout ratio 45%
PE ratio at constant growth rate 15
c. Use the PE ratio to calculate the stock price when AbbVie reaches a perpetual growth rate in dividends. Now fi present value of the dividends during the supernormal growth rate and the price you calculated using the PE r
d. How sensitive is the current stock price to changes in PE ratio when the stock reaches the perpetual growth ra in 11 years to find out. e presented in the textbook. Many analysts will estimate the dividend for the next ypically 10 years. Rather than have the dividend growth fall dramatically from the d. That is, the dividend growth is projected to fall by an equal amount each year. dividends are expected to fall to a 5 percent perpetual growth rate 5 years later,
will find information for AbbVie found in the 2020 edition of Value Line:
, we will assume they are:
interpolation between the high growth rate and perpetual growth rate. Construct stock price at Year 10? What is the stock price today?
Graph the current stock price against the perpetual growth rate in 11 years to find
n the future, analysts will often combine the dividend discount method with
owth rate in dividends. Now find the value of the stock today by finding the you calculated using the PE ratio.
aches the perpetual growth rate? Graph the current stock price against the PE ratio Master It! Solution
