You need to complete this problem in a spreadsheet and show all your work. Creat
ID: 2729594 • Letter: Y
Question
You need to complete this problem in a spreadsheet and show all your work. Create an excel file that shows your calculations. Clearly indicate your final answer(s).
You are trying to estimate the value of McGillicutty's stock. Your research has indicated that you can expect a growth rate in their cash flows (and dividends paid) of 15%, 13%, 10% and 8% over the next 4 years, followed by a constant growth rate of 4% thereafter. The most recent dividend paid by the company was $2.50 per share.
The company has a very high level of risk - their beta is 2.4. The risk free rate is 2.2% and the expected return on the market is 9.5%.
a. What is the required rate of return on the stock?
b. What are the dividends that are expected to be paid over the next 5 years?
c. What is the value of the stock today?
This must be in an excel spreadsheet that I can save and upload
Explanation / Answer
Required rate of return=Risk free rate + beta (Market rate of return-Risk free rate)
Required rate of return=0.022 + 2.4 (.095-.022)
Required rate of return=19.72%
This formula is based on the Capital Asset Pricing Model.
Current dividend , D 0= $2.5 per share
D1=2.5 + 2.5 *.15=2.875
D2=2.875 + 2.875 * .13=3.24875
D3=3.24875 + 3.24875 *.10=3.573625
D4=3.5736 +3.5736 *.08=3.8594
D5=3.8594 +3.8594*.08=4.1681
The dividend growth rates are changing , hence we need to use Multi stage dividend growth model to calculate the value of stock .
Value of stock is the present value of dividends paid during the high growth period , medium growth period and constant growth period.
For the high and medium growth period , we have calculated dividend amounts as below , from year 7 onwards the dividend will grow at a constant rate of 4 % which will require the use of constant growth model.
Current dividend , D 0= $2.5 per share
D1=2.5 + 2.5 *.15=2.875
D2=2.875 + 2.875 * .13=3.24875
D3=3.24875 + 3.24875 *.10=3.573625
D4=3.5736 +3.5736 *.08=3.8594
D5=3.8594 +3.8594*.08=4.1681
D6=4.1681 + 4.1681 *.08=4.5015
D7=4.5015+ 4.5015*.08=4.8616
Present value of dividend
Required rate of return =19.72%
D1=2.875/ (1+.1972) =2.4014
D2=3.24875/ (1+.1972) ^2 = 2.266642
D3=3.573625/ (1+.1972) ^3 = 2.082614
D4=3.8594/ (1+.1972) ^4= 1.878681
D5=4.1681/ (1+.1972) ^5= 1.694746
D6=4.5015/ (1+.1972) ^6= 1.528822
D7=4.8616/ (1+.1972) ^7= 1.379152
From 7th year afterwards , the dividend will grow at a constant growth rate of 4 % , hence we will use the formula of value of stock based on the constant growth model to calculate the value of stock.
Value of stock=Dividend/ (Required rate of return-Growth rate)
Value of stock=1.379152/ (.1972-.04) =8.77
Value of stock=Present value of all dividend=D1+D2+D3+D4+D5+D6+P7
V0 = 2.4014+2.26664+2.08261+1.8786+1.6947+1.5288+8.77=20.62
Value of stock=20.62
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.