WACC and Capital Structure - Via Excel

Athletic Growth(AG) creates products and programs that are sold directly to doctors, hospitals, and medical specialty groups all over the world so their patients can live healthier, longer lives. What is AG's weighted average cost of capital, assuming that the firm has a corporate tax of 34%? Please use excel and show formulas.

a) Common Stock: The firm has 8.5 million shares authorized, and 6.39 million shares of common stock issued; the firm has purchased back 847,000 shares to provide employees through the firm's stock compensation program (determine the number of shares outstanding). These shares have a $1 par value.

Dividends on the firm's common shares are expected to continue growing at 7.25% annually for the forseeable future. Google Finance reports that these shares have a beta of 1.41. When the firm first sold shares on the NYSE, its shares traded at $22. Yesterday, AG paid a dividend of $8.764569. use the expected return to calculate the current market price of AGs shares.

b) Debt: AG has issued two different sets of bonds over the year. Bond A has a total face value of $126 million . Bond B has a total face value of $91 million. Bond A has 18 years left until it must be redeemed; investors demand 7.12% yield on these bonds, even though they pay a substantial coupon rate of 8.1%. Bond B consists entirely of zero coupon bonds that , when issued a decade ago, were expected to mature in 24 years. The Wall Street Journal Reports that these bonds just finished the week trading at 22.3955 in the security markets.

c) Preferred Stock: Three years ago, the firm issued 641,310 preferred shares. (It is authorized to issue 2.25 million of these shares.) Last year, AG issued 393,690 more of these 8.86% preferred shares. Though they have a book value of $100, investors expect a return of 9.8% on these shares .

d) Market Information: The average annual rate on savings accounts in the US is 1%. While the return on the market portfolio is 13.4%, the market risk premium at this time is 12.2%. find the risk-free rate.

The problem solution should be able to meet most of the criteria below and use excel formulas.