Part Two: For part two, you will need to create an Excel spreadsheet that calculates the weighted average cost of capital for the Alpha Corporation. I have attached a copy of a weighted average cost of capital spreadsheet; you may use this as a template if you wish. Do not calculate everything in one large step. You will need to use the following assumptions in your analysis. . There are 9M shares of Alpha common stock outstanding at $55 each. Alpha has a beta of 1.25. Use the CAPM to find the cost of common stock. o The risk-free rate is 2.10%, which is the current yield on a 10-year Treasury. o The expected return on the market portfolio is 10%. . Alpha has 2m shares of preferred stock outstanding, carrying a dividend of $2.25 per share, currently trading at $27. There are currently 200,000 bonds, each with a face value of $1,000, with a coupon rate of 6.75%. These bonds are trading at 109.5% of par value (i.e., $1,095). The bonds were originally issued with a 10 year maturity. Assume that interest is paid annually. * Alpha's marginal tax rate is 40% . I suggest that you use the =YIELD function for the cost of debt, not =YIELDMAT. We are concerned with the REMAINING yield to maturity, that is, the rate of return an investor receives if he purchases the bond today. YIELDMAT calculates the overall YTM from issue date to maturity. . Remember: weights are based on market-values, not book-values.Using the YIELD function in Excel: Choose the "frmction" option on the "insert" menu and select the "yield" function under the nancial functions. Alternatively, you can siruply code "='r"ield[...]" within the cell. Note: Your version of Excel may not contain the yield function if it does not contain the data analysis tool pack. Refer to the end of this document if you currently don't have the "Data Analysis" add-in installed. The Yield function assumes a $1M] par while most bonds have a $1,{l{l[} par so you will need to adjust your numbers accordingly {i.e., if the bond price were $2,, it would he $2M! in this formula]. Fill in each of the boxes [descriptions are provided below]. Do not hard type numbers; use cell references. o Settlement is the bonds settlement date {i.e., the date after the issue date when the security is traded to the buyer]. Assume that this is September 3!}, 2'320. o Maturity is the bond's maturity date, December 31, 2'322. o Hint: When entering dates directly into formulas, use quotes [i.e., "12f31!15"]. therwise, Excel will try to divide these numbers. Alternately, use cell references in the formulas that point to cells with the dates [in standard format without quotations] in them. o Rate is the annual coupon rate. o Pr is the current trading price of the bond as if the face value were $1 {it} [e.g., a bond selling at $2,!i would have a $2 Pr in the Excel formula}. o Redemption value is the face value of the bond as if the face value were $113!} {e.g., a $1,i}{] face value bond would be $1ii in the Excel formula}. o Frequency is the number of times payment occurs in a year. Use 1 for this bond. Note that the yield which the formula will provide is the before-tax cost of debt. TEMPLATE EXAMPLE Weighted Average Cost of Capital Calculations Shares of Common Stock Outstanding 1,000,000 Price Per Share $75.00 Shares of Preferred Stock Outstanding 500,000 Price Per Share $20.00 Bonds Outstanding 100,000 Price per Bond $990.00 Value: Common Stock $75,000,000 Preferred Stock 10,000,000 Debt 99,000,000 Total $184,000,000 Weights: Common Stock 40.76% Preferred Stock 5.43% Debt 53.80% Total 100% Cost of: Common Stock: Risk free rate 8.00% Beta 2.00 Return on Market Portfolio 18.00% Cost of Common Stock 28.00% Preferred Stock: Dividend per Share $3.00 Cost of Preferred Stock 15.00% Debt: Today's Date 1/23/2011 Maturity Date 12/31/2015 Price $99 Par $100 Coupon Rate 14.00% Frequency of Coupon Payments per year 2 Cost of Debt (pretax) 14.28% Tax Rate 28.00% Cost of Debt (after tax) 10.28% WACC 17.76%