Problem:
The meeting with Bob went well. Now Kim wants you to turn your focus to valuing the firm's stock and bonds. She likes to use the dividend discount model to value the firm's stock and the present value formula to value bonds.
With that in mind, you decide to put an Excel spreadsheet together to value the firm's stock and bonds. The company's stock trades for US$30 per share, with an annual dividend payment of US$1.67, which is expected to grow to US$1.75 next year. The required return on stocks is 12%, and the dividend is expected to increase by 5% for the foreseeable future. The bonds you are valuing have 3 years to maturity, a 6% 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$30 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 and explanation slides of your analysis and answers to the two additional questions.