A company, West Berwick Enterprises, has a capital structure as follows:
Total Capital $1,000,000
Debt $400,000
Preferred Stock $100,000
Common Stock $500,000
What would be the minimum expected return from a new capital investment project to satisfy the suppliers of the capital? Assume the applicable tax rate is 40%, interest on debt is 7%, flotation cost per share of preferred stock is $0.75, and flotation cost per share of common stock is $4. The preferred and common stocks are selling in the market for $26 and $143 a share respectively, and they are expected to pay a dividend of $1.50 and $4.50, respectively, in one year. The company's dividends are expected to grow at 7% per year. The firm would like to maintain the existing capital structure to finance the new project.
I have to be able to show this in Excel.