Question
I need help with putting Walmart's (WMT) current information into this excel spreadsheet of the Gordon constant growth model. More instruction if needed: visit yahoo
I need help with putting Walmart's (WMT) current information into this excel spreadsheet of the Gordon constant growth model.
More instruction if needed:
visit yahoo finance website
Click on Market Data on the left of the screen.
Type name of company in Search Box on top of the screen and click on Search Finance button.
3. Write down the information on the stock such as last closing price, beta of stock, EPS and Dividend in dollars and cents.
4. On the left side of screen, click on Key Statistics under Company. Note ROE as you scroll down.
5. You have the basic information to calculate the intrinsic value of the stock using the constant growth assumption in the Dividend Discount Model (refer to text readings).
6. a) Calculate the required rate of return (r) for the stock using the Capital Asset Pricing Model (CAPM).
r = (10-year Treasury Bond Yield) + [Beta (S&P 500 Index Return - 10-year Treasury Bond Yield)]
Search for 10-year Treasury Bond Yield using the Search box at finance.yahoo.com.
S&P 500 Index Return for 2014 can be obtained at google.com. Since the return for 2015 was negative, we can use the 2014 value of 11.39%.
Plug in the data and calculate the required rate of return using the value for beta.
b) The constant growth rate of dividends, g = retention ratio x ROE
= (1 payout ratio) x ROE
= [1 (dividend per share/EPS)] x ROE.
g will be a percentage. Convert to a decimal by dividing by 100.
c) Intrinsic value of stock = Po = [Do (1 + g)]/(r g)
Do is the dividend just paid while D1 = Do (1 + g) is the dividend to be paid.
Knowing Do or D1, r and g, you can calculate the intrinsic value.
You can also visit Nasdaq for more info
Below is the excel spreadsheet with the the correct formulas, I just need help replacing the example's numbers with Walmart's numbers that can be found on Yahoo Finance :
| Company Example | ||||||||||||||||||
| I. Basic Information | II. Formulas Used in the Analysis: | |||||||||||||||||
| D0 = | $1.15 | Dividend in Year t , Dt , in | Col. 2 | Dt-1(1+g) | Company example just paid a dividend of $1.15, and the dividend is expected to grow at a constant rate of 8.3%. What stock price is consistent with these numbers, assuming a 13.7% required return? | |||||||||||||
| P0 = | $23.06 | Intrinsic value (and price) in Year t, Pt , in | Col. 3 | Dt+1 / (rs - g) | ||||||||||||||
| g = | 8.30% | Dividend yield (constant), in | Col. 4 | Dt / Pt-1 | D0 | $1.15 | ||||||||||||
| rs = | 13.70% | Capital gains yield (constant), in | Col. 5 | (Pt - Pt-1) / Pt-1 | g | 8.3% | ||||||||||||
| Total return (constant) , in | Col. 6 | Div. yield + CG yield | rs | 13.7% | ||||||||||||||
| PV of dividends, discounted at 13.7% | Col. 7 | Dt / (1+rs)t | ||||||||||||||||
| III. Examples: | P0 = | D1 | = | D0 (1+g) | = | $1.25 | ||||||||||||
| Col. 2 | D1 = $1.1500(1.083) | $1.25 | ( rs g ) | ( rs g ) | 0.054 | |||||||||||||
| Col. 3 | P0 = $1.25 / (0.137 - 0.083) | $23.06 | P0 = | $23.06 | ||||||||||||||
| Col. 4 | Dividend yield, Year 1: $1.25 / $23.06 | 5.4% | ||||||||||||||||
| Col. 5 | Cap gains yield, Year 1: ($24.98 - $23.06) / $23.06 | 8.3% | ||||||||||||||||
| Col. 6 | Total return, Year 1: 5.4 % + 8.3 % | 13.7% | ||||||||||||||||
| Col. 7 | PV of D1 discounted at 13.7% | $1.10 | ||||||||||||||||
| IV. Table: Forecasted Results Over Time | PV of | |||||||||||||||||
| At end | Dividend | Capital | Total | dividend | ||||||||||||||
| of year: | Dividend | Price | yield | gain yield | return | at 13.7% | ||||||||||||
| (1) | (2) | (3) | (4) | (5) | (6) | (7) | ||||||||||||
| 2017 | $1.15 | $23.06 | ||||||||||||||||
| 2018 | $1.25 | $24.98 | 5.4% | 8.3% | 13.7% | $1.10 | ||||||||||||
| 2019 | $1.35 | $27.05 | 5.4% | 8.3% | 13.7% | $1.04 | ||||||||||||
| 2020 | $1.46 | $29.30 | 5.4% | 8.3% | 13.7% | $0.99 | ||||||||||||
| 2021 | $1.58 | $31.73 | 5.4% | 8.3% | 13.7% | $0.95 | ||||||||||||
| 2022 | $1.71 | $34.36 | 5.4% | 8.3% | 13.7% | $0.90 | ||||||||||||
| 2023 | $1.86 | $37.21 | 5.4% | 8.3% | 13.7% | $0.86 | ||||||||||||
| 2024 | $2.01 | $40.30 | 5.4% | 8.3% | 13.7% | $0.82 | ||||||||||||
| 2025 | $2.18 | $43.65 | 5.4% | 8.3% | 13.7% | $0.78 | ||||||||||||
| 2026 | $2.36 | $47.27 | 5.4% | 8.3% | 13.7% | $0.74 | ||||||||||||
| 2027 | $2.55 | $51.19 | 5.4% | 8.3% | 13.7% | $0.71 | ||||||||||||
| Sum of PVs from 1 to = P0 = | $23.06 | |||||||||||||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Recent Advances In Computational Finance
Authors: Nikolaos S. Thomaidis, Jr. Dash, Gordon H.
1st Edition
1626181233, 978-1626181236
