This advanced WACC analysis template is a comprehensive analysis used to calculate the weighted average cost of capital for a company. Use and modify this template to fit the needs of your analysis! This template expands on the concepts covered in CFI's regular WACC analysis template that is offered for free by bringing in more elements of how you actually find certain inputs and numbers.
This template can be used as is, but is meant to teach you how to use resources like CapitalIQ (if accessible) or free online resources to find information to populate the supporting schedules. Every necessary calculation in this template is able to be completed using free resources if you are not able to access paid financial databases.
This advanced WACC template includes several sections and supporting schedules that build out the final comprehensive WACC analysis.
This advanced WACC analysis template expands on basic WACC analysis by including the cost of preferred equity into the equation for calculating WACC. By using various inputs, this section outputs the weighted average cost of capital for a company.
This template uses several inputs to compute the WACC of a company. Some of these inputs are hard-coded inputs, while others are computed using supporting schedules that were derived from researched data. The list of inputs used in CFI's advanced WACC analysis template is as follows:
- Company Name
- Ticker Symbol
- Tax Rate
- Risk-Free Rate of Return
- S&P 500 Market Rate of Return
- Size Premium
- WACC weights: D/(D+E+P); D/E; P/E
- Cost of Debt
- Cost of Equity
- Cost of Preferred Equity
This template uses several supporting schedules to generate the inputs for the WACC calculation. Most of the inputs in the WACC calculation are derived using data to create an accurate assumption for the company's financial state. Here are the supporting schedules:
This schedule uses the company's five-year historical effective tax rates to compute an average tax rate as an input.
This schedule averages 10 years of S&P 500 historical annual total returns to create an assumption for the market return.
WACC Calculation Weights
This supporting schedule calculates the different weights used to calculate WACC based on the company's financial statements and current market price.
Cost of Debt
The cost of debt supporting schedule offers two different methods for calculating the cost of debt. The first method generates a synthetic credit rating. This method is based on a method created by Professor Aswath Damodaran at New York University's Stern School of Business. Professor Damodaran is a widely known and respected individual that teaches corporate finance and valuation at Stern. This method uses a company's interest coverage ratio to generate a synthetic credit rating of the company and an associated implied cost of debt. The synthetic credit rating method is great for students and analysts who do not have access to financial databases like CapitalIQ or Bloomberg.
The second method uses the company's most recently issued fixed income securities. By averaging the coupon rates of these securities, this template generates an average cost of debt. You will notice that the cost of debt generated in both methods is relatively similar in this template.
The beta schedule uses a comparable companies table to find the five-year industry average effective tax rate. Using this tax rate, the schedule sets up a comps table to generate the average unlevered beta for the industry. Then using this unlevered beta, it generates the company's levered beta.
Cost of Equity
The cost of equity schedule uses the following outputs to generate the cost of equity:
- Risk-Free Rate of Return
- Market Risk Premium - calculated using the market return from the previous supporting schedule
- Company Levered Beta - calculated from the beta schedule
- Size Premium
Cost of Preferred Equity
This schedule calculates uses inputs taken from the WACC calculation weights schedule as well as from the company's financial statements and notes to financial statements. The schedule generates the cost of preferred equity.
This advanced WACC analysis template also includes sensitivity analysis. This analysis shows how changes in capital structure, cost of debt, cost of equity, and cost of preferred equity will affect a company's WACC.
This advanced WACC analysis template was built using Apple Inc.'s financial statements. This template should be used mainly as a guideline for how to create an advanced WACC analysis, as each company's financial statements will be different. Differences in the financial statements will cause changes that need to be applied to the supporting schedules of this template.
If you want to see how an advanced WACC analysis is applied, check out CFI's Advanced Financial Modeling and Valuation Course, which values the stock price of Amazon!