calculating instrinsic value of the stock
Not what you're looking for?
From Mayes' Financial Analysis with Microsoft Excel (4th ed.), p. 266.
1. Using the Yahoo! Finance website (http://finance.yahoo.com), get the current price and five-year dividend history for Eli Lily & Co. To gather this data, enter the ticker symbol (LLY) in the Get Quotes box at the top of the page and then click the GO button. Record the current price from this page. Now, at the left side of the quotes page, click on the Historical Prices link. To get a table of previous dividends, select the Dividends Only at the top of the table, set the Start Date to five years before today's date, and click the Get Prices button. Click the Download to Spreadsheet link at the bottom of the table to download a file with this data. You should have the choice of either saving the file or opening it directly with Excel. It is easier to let it open in Excel. Otherwise, save the .csv (comma-separated variables) file and then open it with Excel. It shouldn't need any further processing other than some formatting. You now have the dividends in a worksheet.
a. Since LLY pays dividends quarterly, calculate the quarterly percentage change in the dividends. Now, evaluate the compound quarterly growth rate of the dividends using the GEOMEAN function.
b. Now annualize the quarterly dividend growth rate.
c. Calculate the intrinsic value of the stock using a 10% required rate of return and the calculated annual growth rate. Use the sum of the most recent four dividends as D0 (aka "D" sub "zero").
d. How does the calculated intrinsic value compare to the actual market price of the stock? Use an "IF" statement to display whether the stock is undervalued, overvalued, or fairly valued. Would you buy the stock at this price?
Purchase this Solution
Solution Summary
This post illustrates how to calculate the intrinsic or fundamental value of a stock.
Solution Preview
See the attached file. Thanks
Part a.
Date Dividends % ...
Purchase this Solution
Free BrainMass Quizzes
Organizational Behavior (OB)
The organizational behavior (OB) quiz will help you better understand organizational behavior through the lens of managers including workforce diversity.
Situational Leadership
This quiz will help you better understand Situational Leadership and its theories.
Six Sigma for Process Improvement
A high level understanding of Six Sigma and what it is all about. This just gives you a glimpse of Six Sigma which entails more in-depth knowledge of processes and techniques.
Marketing Management Philosophies Quiz
A test on how well a student understands the basic assumptions of marketers on buyers that will form a basis of their marketing strategies.
Cost Concepts: Analyzing Costs in Managerial Accounting
This quiz gives students the opportunity to assess their knowledge of cost concepts used in managerial accounting such as opportunity costs, marginal costs, relevant costs and the benefits and relationships that derive from them.