Problems Involving Stock Valuation And Investment Returns
Problems involving stock valuation and investment returns
Instructions
1. You have two problems - one on each tab of this Excel file.
2. Please show your work in the cells. Use Excel formulas instead of writing the values/answers directly in the cell. The instructor will then know where you made a mistake and provide you valuable feedback and partial credit (if appropriate).
The total points for these problems are 8.
Paper For Above instruction
The evaluation of stock value and investment returns is fundamental in financial decision-making. The first problem involves determining the present value of a perpetuity with a fixed annual dividend, illustrating the dividend discount model (DDM). The second problem extends this concept to a multi-year holding period with a finite dividend stream and a future selling price, employing present value calculations incorporating discounted cash flows and sale proceeds to estimate the maximum price an investor should pay for the stock to achieve a desired rate of return.
In Problem 1, you are asked to calculate the intrinsic value of a stock that pays a perpetual dividend. Given the annual dividend of $1.35 and a required rate of return of 9.5%, this problem applies the preferred stock valuation formula, which is a simplified version of the Gordon Growth Model assuming zero growth:
Price = Dividend / Required Rate of Return
This model assumes dividends grow at a constant rate; however, with a zero-growth scenario, the formula simplifies directly to dividing the dividend by the rate. Using Excel, the calculation can be expressed as:
=B2 / B3, where B2 contains the dividend, and B3 contains the rate of return. Substituting the given values:
=1.35 / 0.095 ≈ 14.21
This result indicates that, assuming the stock's dividends remain constant, it should be valued at approximately $14.21.
For Problem 2, the valuation becomes more complex due to the finite dividend period and a terminal sale price. The investor intends to hold the stock for 7 years, during which dividends of $6.00 per year will be received, and then sell the stock for $28 at the end of 7 years. Given an annual desired rate of return of 11%, the valuation involves calculating the present value (PV) of the expected dividends and the present value of the expected selling price.
The present value of the dividends is calculated by discounting each dividend back to the present using the formula:
=Dividend / (1 + r)^t
where t ranges from 1 to 7. Alternatively, recognizing the dividends are an annuity, we can use the Present Value of Annuity formula:
PV of Dividends = Dividend × [(1 - (1 + r)^-n) / r]
with n = 7 years, r = 11%. Using Excel, this becomes:
=B4 * (1 - (1 + B5)^-B6) / B5
where B4 contains the annual dividend, B5 the rate, and B6 the number of years.
The PV of the future sale price is calculated as:
Sale Price / (1 + r)^n
which in Excel is expressed as:
=B7 / (1 + B5)^B6
Adding these gives the maximum price you should pay today:
Price = PV of Dividends + PV of Sale Price
References
- Bodie, Z., Kane, A., & Marcus, A. J. (2014). Investments (10th ed.). McGraw-Hill Education.
- Ross, S. A., Westerfield, R. W., & Jordan, B. D. (2019). Fundamentals of Corporate Finance (12th ed.). McGraw-Hill Education.
- Brigham, E. F., & Ehrhardt, M. C. (2016). Financial Management: Theory & Practice (15th ed.). Cengage Learning.
- Damodaran, A. (2012). Investment Valuation: Tools and Techniques for Determining the Value of Any Asset. Wiley.
- Fabozzi, F. J. (2013). Bond Markets, Analysis, and Strategies. Pearson.
- Myers, S. C. (2018). Principles of Corporate Finance. McGraw-Hill Education.
- Higgins, R. C. (2012). Analysis for Financial Management. McGraw-Hill Education.
- Gitman, L. J., & Zutter, C. J. (2015). Principles of Managerial Finance. Pearson.
- Penman, S. H. (2012). Financial Statement Analysis and Security Valuation. McGraw-Hill Education.
- Sharpe, W. F., & Alexander, G. J. (2014). Investments. Pearson.