Spreadsheet Exercise: Chapter 7 You are interested in purchasing the common stock of Azure Corporation. The firm recently paid a dividend of $3 per share. It expects its earnings and hence its dividends-to grow at a rate of 7% for the foreseeable future. Currently, similar-risk stocks have required returns of 10%. To Do a. Given the data above, calculate the present value of this security. Use the constant-growth model (Equation 7.4) to find the stock value. b. One year later, your broker offers to sell you additional shares of Azure at $73. The most recent dividend paid was $3.21, and the expected growth rate for earnings remains at 7%. If you determine that the appropriate risk premium is 6.74% and you observe that the risk-free rate, RF. is currently 5.25%, what is the firm's current c. Applying Equation 7.4, determine the value of the stock using the new dividend and required return from part b. d. Given your calculation in part c, would you buy the additional shares from your broker at $73 per share? Explain. e. Given your calculation in part c, would you sell your old shares for $73? Explain. Solution a. Given the data above, calculate the present value of this security. Use the constant-growth model (Equation 7.4) to find the stock value. S Last dividend. D. Constant growth, g Required return, Price. Po 3.00 7% 1096 b. One year later. your broker offers to sell you additional shares of Azure at $73. The most recent dividend paid nyha anadanthate for a caminant 70% Tam data that the interiel Chapter 7 b. One year later, your broker offers to sell you additional shares of Azure at $73. The most recent dividend paid was $3.21, and the expected growth rate for earnings remains at 7%. If you determine that the appropriate risk premium is 6.74% and you observe that the risk-free rate, RF, is currently 5.25%, what is the firm's current required return, r? Risk premium of Azure Risk-free rate Required return 6.74% 5.25% c. Applying Equation 7.4, determine the value of the stock using the new dividend and required return from part b. Required return, r Constant growth, g Last dividend, D. Price, P. 0.00% 7.00% 3.21 S d. Given your calculation in part c, would you buy the additional shares from your broker at $73 per share? Explain. $ No, you would not want to buy additional shares of the stock at $73 because the intrinsic value is Your belief would be that the stock is e. Given your calculation in part c, would you sell your old shares for $73? Explain. Although you would be looking at a loss, you would want to sell your current shares at $73 because the intrinsic value is only $ which would be even a bigger 2 G A B D E Required return, Constant growth, g Last dividend, D. Price, Po 0.00% 7.00% 3.21 S 3 d. Given your calculation in part c, would you buy the additional shares from your broker at $73 per share? Exp 5 S 6 No, you would not want to buy additional shares of the stock at $73 because the intrinsic value is Your belief would be that the stock is 7 -B 39 30 e. Given your calculation in part c, would you sell your old shares for $73? Explain. 41 Although you would be looking at a loss, you would want to sell your current shares at $73 because the intrins value is only s which would be even a bigger -42 43 44 Points 1 1 46 47 1 48 45 Requirements 1 In cell E19, by using cell references to the given data, calculate the stock price. 2 In cell E25, by using cell references to the given data, calculate the required return. 3 In cell E32, by using cell references to the given data, calculate the new stock price. 4 In cell F37, type either overvalued or undervalued depending on your belief of the value of the stock. 5 In cell G42. type either gain or loss depending on the result of trading the stock. 6 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 49 50 1 51 52 53
