WACC Calculator Spreadsheet with Formulas

 Nearly every single financial model here on this site has a DCF Analysis schedule built into it. Each one requires an input for a discount rate. How do you know what discount rate makes sense? Well, using the WACC (weighted average cost of capital) methodology is a common approach. This template walks you through all the calculations required to come up with the WACC, which you can then take and plug into your DCF Analysis.

$45.00 USD

After purchase, the template will be immediately available to download. This is also included in the valuation templates and Accounting templates bundle.

wacc

Template Features:

  • Walk the user through each calculation and input section to come up with a proper WACC and explain each component.
  • Includes a sensitivity analysis for various discount rates as different risk-free rates and expected market returns.
  • Includes a DCF analysis (15  years) where the discount rate is referenced from the WACC output calculation.
  • Includes a sensitivity analysis for NPV within the DCF Analysis based on varying risk-free rates and expected market returns.
  • Recently updated to automatically account for the tax benefit of interest expense (so if tax rate is 25%, then you only count 75% (1 - 25%) of the interest rate for the cost of debt calculation).

Performing a discounted cash flow analysis is primarily done to try and figure out if a project's net cash flows (in and out) can generate excess gains. The discount rate is essentially a measure of risk. The higher the discount rate, the higher the implied risk of the project and the higher the cash inflows need to be relative to the investment in order to achieve excess gains. Note, excess gain just means you have a net present value that is positive. Note that an IRR is the discount rate that makes the NPV equal to zero.

If I’m building a startup financial model to assess the viability of a new business and I know it takes $x to get started and the cash return is $y over the next 10 years as well as has an exit value of $z, then I want to know if the cash flows make sense given the risk of the type of business being modeled.

WACC looks at how much debt and equity a given business has or plans to have and then simply weights each one to come up with a required rate of return. The equity calculation is usually determined based on a capital asset pricing model (CAPM) and that is where you are looking at other similar returns in the market or returns of similar businesses. The idea is if the business is generating higher returns than this weighted average required rate of return, then in theory it is worth doing.

Cost of Debt

For this part of the calculation, you just take the weighted average interest rate of existing debt or whatever the prevalent average interest rate in the open market is, reduce that by the corporate tax rate, and this final result is the 'cost of debt' input.

Cost of Equity

The CAPM is a key place to focus. The main input of this section is the expected market return. You can use the average returns of industry-specific indices or similar companies as a benchmark if you believe they better reflect the market the asset operates in compared to a broader index such as the S&P 500, Nasdaq or Russell 2000. The point is you are trying to figure out what similar businesses generally return over time.

In the cost of equity calculation, the risk-free rate is subtracted from the expected market return in order to come up with the market risk premium.

Another component of 'cost of equity' is the underlying beta. This just means the degree to which the value of a company is likely to change with broader market changes. A higher beta means higher risk. The beta is multiplied by the market risk premium and then the risk free rate is added to that result in order to come up with the final cost of equity. (yes the risk-free rate is used in two different places for the cost of equity calculation).

Multiply the 'cost of debt' by the % of the capital structured funded with debt, then multiply the 'cost of equity' by the % of the capital structured funded with equity and then add those two results together. This is your WACC.

If the average returns of the successful businesses in the same space are high, that is going to imply a much higher risk for the business that is being analyzed. Higher risk implies higher expected returns as well as a higher chance of failure. So, in that case, a high discount rate means that in order to have a positive NPV, you need to have high cash returns relative to whatever the initial investment is.

Note, this template comes in an Excel and Google Sheet version. The G sheet version does not include a sensitivity analysis because that spreadsheet software doesn’t have the what-if analysis features that Excel has.

Similar Templates: