Excel Template: WACC Calculator

In this post I am going go over WACC calculation and share with you a spreadsheet template I created to calculate WACC for a firm with given information.

WACC in other words, weighted average cost of capital. Or cost of capital in terms of weighted average calculation. Companies use debt or issue preferred stocks as a mean of generating capital for operations. WACC take into account the portions of these debt and equity financing and calculate the interest rate the company have to pay for every dollar it uses for financing activities.

This is also the rate company use as required rate of return in project management, capital budgeting, project evaluation, NPV calculations etc.

Investopedia.com says: "A calculation of a firm's cost of capital in which each category of capital is proportionately weighted. All capital sources - common stock, preferred stock, bonds and any other long-term debt - are included in a WACC calculation"

Here is a screen shot of the WACC calculator I designed.

You can download this spreadsheet free of charge and use it to calculate your required rate of return. (WACC calculator is copy right material of ashPresentom)

MS Excel: formulas that make your work easier

# To return a latest value of a column. (the column can not have empty cells between 1st value and last value)

=INDEX(H8:H14,COUNT(H8:H14))


in this formula, the range H8 to H14 we will get the latest value or the bottom most value in the correspondent cell that hold this formula.

Explanation:
count formula here counts the number of cells that has values.
using index formula, we find the index value within the given range, in this case H8:H14, and we want the index value of the 5th cell that is from top to down (the last cell with the values), which is the value given from Count formula.