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
