Weighted Average Cost of Capital

The free excel template help the user to calculates the weighted average cost of capital for the long-term funding used by the firms, supplied by its creditors (or lenders) and the owners (or shareholders),

 Inputs in the green cells only

  • Equity financing (Column C, Row 8): The long-term funding supplied by the shareholders includes both common stock and preferred stock, common stock includes outstanding common stock and retained earnings
  • Debt financing (Column C, Row 9): The long-term funding supplied by the creditors is long term debt only. Accounts payable, accrued expenses, and other non-debt or current liabilities are excluded, short term seasonal debt is also excluded, if the short-term debt is continuing as a revolving, so that debt can be included as part of long-term debt
  • Risk free rate (Column F, Row 8): is return of an investment with zero risk, approximated by the return on very short government treasury bond
  • Market risk premium (Column F, Row 9): is difference between expected return on a market portfolio and risk-free rate
  • Levered beta (Column F, Row 10): is a measure of market risk
  • Debt interest (Column I, Row 8): it is annual interest on long term debt
  • Corporate tax (Column I, Row 9): tax on net profit

Outputs

 After you fill the green cells, the data will dynamically flow into the following below

  • Capital structure weights
  • Cost of equity using CAPM formula
  • Cost of debt after tax
  • Weighted average cost of capital
  • First dashboard showing the impact of changing capital structure on WACC
  • Second dashboard showing the impact of changing levered beta on WACC

Leave a Reply

Your email address will not be published. Required fields are marked *

Rating*

%d bloggers like this: