Using the Gordon Growth Model to Find the Cost of Equity (Two parts, 15 points)

A firm's current stock price is $42. You expect the firm to pay $1.25 worth of dividends next year and for dividend payments to continue growing at 3.25% a year forever. Earnings growth is exected to be 4% per year and the firm currently borrows at 6.5%. Find the firm's cost of equity. Then, create a data table to show the cost of equity changes as the growth rate in dividends increases.

1) Make a scatter plot to show this relationship. Label the y-axis "Cost of Equity" and the x-axis "Dividend Growth Rate". Do not show any decimal places on either axis (e.g. 20%, not 20.00%). Title the graph "Firm's Cost of Equity as a Function of the Dividend Growth Rate"

2) Finally, what is the cost of equity? Why are do we even care?

Please use excel to solve and show steps in solving