Overview

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.

Contents

This advanced WACC template includes several sections and supporting schedules that build out the final comprehensive WACC analysis.

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.

Inputs

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
  • Date
  • 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

Supporting Schedules

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:

Tax Rate

This schedule uses the company's five-year historical effective tax rates to compute an average tax rate as an input.

Market Return

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.

Beta

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.

Sensitivity Analysis

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.

Final Notes

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!

Share
Reviews Add a review
No reviews yet

More From Victor Ho

Browse our top rated business templates. See All
Opportunity Cost Template
393
35
This opportunity cost template is a fun tool you can use to calculate the opportunity cost of purchase. This tool…
Purchase Order Template Word Preview Purchase Order Template (Word)
304
25
This purchase order template (word) is a document that you can use to request a purchase from a vendor. Use…
Purchase Order Template Excel Preview Purchase Order Template (Excel)
347
27
This purchase order template (excel) is a document that you can use to request a purchase from a vendor. Use…
Personal Budget Template
314
24
This personal budget template is a basic tracker for your daily, monthly, and yearly personal finances! Use this budget if…
Advanced Personal Budget Template Preview Advanced Personal Budget Template
425
38
This advanced personal budget template includes everything you need to keep track of your daily, monthly, and yearly personal finances!…
cable industry comps template preview Cable Industry Comps Template
1,539
11
Use this cable industry comps template as a guideline for a comparable companies universe for Charter Communications Inc. Charter is…