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.
  • The risk-free rate is 2.10%, which is the current yield on a 10-year Treasury.
  • 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 function option on the insert menu and select the yield function under the financial functions. Alternatively, you can simply code "=Yield(...)" 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 $100 par while most bonds have a $1,000 par so you will need to adjust your numbers accordingly (i.e., if the bond price were $2,000, it would be $200 in this formula).
• Fill in each of the boxes (descriptions are provided below). Do not hard type numbers; use cell references.
  • 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 30, 2020.
  • Maturity is the bonds maturity date, December 31, 2022.
  • Hint: When entering dates directly into formulas, use quotes (i.e., 12/31/15). Otherwise, 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.
  • Rate is the annual coupon rate.
  • Pr is the current trading price of the bond as if the face value were $100 (e.g., a bond selling at $2,000 would have a $200 Pr in the Excel formula).
  • Redemption value is the face value of the bond as if the face value were $100 (e.g., a $1,000 face value bond would be $100 in the Excel formula).
  • 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.

• You will need to provide the Excel file containing your WACC calculation table. Make sure to add this to the Excel file from Part 1 so that you are only uploading one Excel file to Canvas. I will deduct points if you upload more than one Excel file to Canvas. Use cell references in your spreadsheet. I should easily be able verify the validity of your WACC calculation and quickly solve for the WACC when making small changes to the assumptions of the model. I should not have to type a number in more than once in order to solve for the WACC. For example, if I make a change to the firms beta then your template should be able to automatically calculate the new WACC.