Weighted Average Cost of Capital



Exer12 Weighted Average Cost of Capital (WACC)

This exercise builds the formulas that calculate the weighted average cost of capital for a firm. An income statement and balance sheet is given to provide some of the required input variables. Also given is the following data for bonds, preferred and common stock.

a. bonds: XYZ 12s2020 close @ 120

b. preferred: pays $1.25 div/qtr, closed @ $41.00/share

c. common: div (ttm) =$6.00, expected growth in div=5% closed @ $48.00

d. effective corporate tax rate: from the Income Statement (taxes / Inc B/T)

e. transaction costs : $4 / Bond; $1 /share for Preferred; 2% Common

Steps:

1) Download sample12.xls, put your name in cell A1 and save the file locally with a filename of your choice.

2) Enter a formula in C8 that calculates the “net received by the firm”. This is the closing bond price in dollars [C6] minus the transaction cost [C7].

3) Enter a formula in E4 that uses Rodriguez’s Model to calculate the “cost of debt before tax”.

4) Enter a formula in E5 that calculates the tax rate from the income statement [H11/H10].

5) In E6, calculate the cost of debt after tax using the model BeforeTax(1-tax rate)=AfterTax.

6) In C13, calculate the net received by the firm for preferred.

7) In E10, calculate the cost of preferred.

8) In C16, calculate next year’s dividends using D0(1+g).

9) In C20, calculate the net received by the firm.

10) In E15, calculate the cost of common.

11) In E21, calculate the cost of retained earnings.

12) In C24 through C27, set these cells equal to the corresponding data in the given balance sheet [K11 thru K14]. Total the four components into C28.

13) Set D24 thru D27 to equal the calculated component costs (above).

14) In E24 thru E27, calculate the extensions by multiplying each of the four “Balance [sheet] $” (C24 thru C27) by its respective “Comp[onent] costs” (D24 thru D27). Total the “ext[ensions]” into E28.

15) The WACC (C29) is the total of the extensions (E28) divided by the total of the four balance sheet values (C28). Enter this calculation into C29 (as a decimal) and D29 (as a percent). This is your answer.

16) Make the following changes:

C6=1300

C11=43.00

C17=50.00

H11=30000

K11=600000

17) Save your file. Submit to Moodle2.

(

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download