Problem:
With that in mind, you decide to put an Excel spreadsheet together that values the firm's stock and bonds. The company's stock trades for US$35 per share, with an annual dividend payment of US$1.50, expected to grow to US$1.58 next year. The required return on stocks is 10%, and the dividend is expected to increase by 6% for the foreseeable future. The bonds you are valuing have 3 years to maturity, a 4% annual coupon, and a face value of US$1,000. The market interest rates are 5%.
Use Microsoft Excel to calculate the value of the stocks and the bonds. In addition, answer these questions:
- At the current trading price of US$35 per share, is the dividend discount model value of the stock equal to the market value? If they are different, explain why.
- At the face value of US$1,000, is the bond trading price greater than, equal to, or less than its face value? Explain your answer.
Your submission should include the Excel spreadsheet.