Financial Excel Spreadsheets

123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 A B C D E F G Tax rate 0% Debt in the capital structure 0% 10% 20% 30% 40% 50% EBIT 120,000 120,000 120,000 120,000 120,000 120,000 Interest -4,125 8,750 14,625 22,000 31,250 Profit before taxes 120,000 115,875 111,250 105,375 98,000 88,750 Taxes ------Profit after taxes 120,000 115,875 111,250 105,375 98,000 88,750 Dividends 120,000 115,875 111,250 105,375 98,000 88,750 Total payments to security holders 120,000 120,000 120,000 120,000 120,000 120,000 Required return on debt 8.00% 8.25% 8.75% 9.75% 11.00% 12.50% Required return on equity 12.00% 12.50% 13.00% 13.50% 14.50% 16.00% Market value of debt -50,000 100,000 150,000 200,000 250,000 Market value of equity 1,000,000 927,000 855,769 780,556 675,862 554,688 Market value of the firm 1,000,000 977,000 955,769 930,556 875,862 804,688 Book value of debt -50,000 100,000 150,000 200,000 250,000 Book value of equity 500,000 450,000 400,000 350,000 300,000 250,000 Book value of the firm 500,000 500,000 500,000 500,000 500,000 500,000 Return on total capital 24.0% 24.0% 24.0% 24.0% 24.0% 24.0% Return on equity 24.0% 25.8% 27.8% 30.1% 32.7% 35.5% Number of shares outstanding 5,000 4,744 4,477 4,194 3,858 3,447 Price per share 200.0 195.4 191.2 186.1 175.2 160.9 Earnings per share 24.00 24.43 24.85 25.13 25.40 25.75 Price-earnings ratio 8.33 8.00 7.69 7.41 6.90 6.25 Book value debt ratio 0.0% 10.0% 20.0% 30.0% 40.0% 50.0% Market value debt ratio 0.0% 5.1% 10.5% 16.1% 22.8% 31.1% Weighted average cost of capital 12.0% 12.3% 12.6% 12.9% 13.7% 14.9% Free cash flow 120,000 120,000 120,000 120,000 120,000 120,000 Market value of the firm 1,000,000 977,000 955,769 930,556 875,862 804,688 Note: The number of shares and price per share are computed from the following considerations. Assume the change from 0% debt to any other amount of debt is accomplished by repurchasing shares with the borrowed funds. Then the price per share times the number of shares repurchased must equal the amount borrowed. Also, the price per share times the number of shares remaining must equal the market value of equity. Together, these imply that the price per share times 5000 must equal the market value of the firm. This fact is used to compute the price per share and then the number of shares is found by dividing the market value of equity by the price per share. Page 1CALCULATION OF WACC FOR 10% DEBT Data from Exhibit 1 Market value debt ratio 5.12% Cost of debt 8.25% Cost of equity 12.50% Tax rate 0% Weighted Weights Costs After-tax cost of debt 8.25% 5.12% 0.42% Cost of equity 12.50% 94.88% 11.86% WACC 12.28%