Weighted average cost of capital                      
                                 
In this spreadsheet we will calculate weighted average cost of capital
We will not stop on detailed explanations and economic meanings of the variables, because there is too much info on that in the web
We will focus on the main steps of  WACC calculation and different approaches applicable to each steps
You should know that there is no unique approach to WACC calculation and many analysts create their own approaches and logic for WACC model
WACC      
Traditional formula for WACC calculation is:
WACC = We x Ce + Wp x Cp + Wd x Cd x (1 - t)
or
WACC = Weight of equity x Cost of equity + Weight of preferred stock x Cost of preferred stock + Weight of debt x Cost of debt x ( 1 - tax rate)
Let's calculate separately each of the elements in the formula above
We (Weight of equity) = MVc / (MVc + MVp + MVd) = 
 = Market value of common stock / (Market value of common stock + Market value of preferred stock + Market value of debt)
Ce (Cost of Equity) = Rf + B x (Rm - Rf) = Risk free rate + Beta x (Market return - Risk free rate) = Risk free rate + Beta x Equity risk premium
This is one of the most important elements of WACC formula and one of the most difficult for calculation. We'll return to this formula later in "Cost of equity" part
Wp (Weight of preferred stock) =  MVp / (MVc + MVp + MVd) =
 = Market value of preferred stock / (Market value of common stock + Market value of preferred stock + Market value of debt)
Cp (Cost of preferred stocks) = Div / Price = Annual dividend on preferred stock / Market price of the preferred stock
Wd (Weight of debt) = MVd/ (MVc + MVp + MVd) = 
 = Market value of debt / (Market value of common stock + Market value of preferred stock + Market value of debt) 
Cd (Cost of debt) = the yield of company’s bonds; if the company has no bonds use the rate for company’s bank loan; if you have no information on yields or rates
but you now that the company has a debt, use the yields of bonds for similar companies' bonds (they should have same size, credit ratings, operate in the same industry, i.e. 
they should be similar in all possible ways)
t (tax rate) = the rate of corporate income tax in the country where it is registered (where it pays corporate income tax)
Let's also look at the formulas for some of the elements in the formulas above
Market value of common stocks (MVc) = number of common stocks x current market price of common stocks
Market value of preferred stocks (MVp) = number of preferred stocks x current market price of preferred stocks
Market value of debt (MVd) = Market value of the issued bonds; if the company has no bonds, but have bank loans, use their balance value for calculation; if the company
has mixed debt structure, use debt value from its balance sheet (Long-term debt + Short-term debt)
if the company has no debt than the Market value of debt = 0
COST OF EQUITY    
Cost of equity is one of the key elements of WACC formula
The classic formula for Cost of equity calculation is 
Ce (Cost of Equity) = Rf + B x (Rm - Rf) = Risk free rate + Beta x (Market return - Risk free rate) = Risk free rate +  Beta x Equity risk premium
Let’s calculate all the elements from this formula
Rf (Risk free rate) is usually a yield for government's 10 year bonds. In USA the yield of Treasury notes is usually used while for European countries you can use 10 years German bonds
Some analysts use current 10Y rates (for US 10Y notes it is about 2.9%  now, in Dec 2010), some prefer average historical values, some use forecast for 10Y notes yields
When analysts calculate cash flows not in $ or ˆ, but in some local currency, many of them use local government's 10 years bonds yield nominated in local currency
If there is no such long-term debts available, you can use the rate for borrowings of the most reliable and safest company in the country nominated in local currency
Many economists still argue on the definition of the risk free rate, because some Emerging Markets countries defaulted on their debts in the past (for example Russia, Argentina).  
Thus we can not  consider the yields of their bonds as a risk free rate in a classic /traditional interpretation. 
Anyway, you can choose the approach which is more suitable for you and which you consider logical for your situation
More on calculation of the risk free rate you can find on Aswath Damodaran's page
http://pages.stern.nyu.edu/~adamodar/pdfiles/papers/riskfree.pdf
B (Beta) calculation
Beta describes the relation of returns between the stock and the market.
The formula for Beta is
Beta = Cov (Rs; Rm) / Var (Rm) 
Cov (Rs; Rm) is the covariance of the stock's and market's returns
You can calculate covariance using COVAR function in excel (if you are interested you can also look at the definition of covariance in excel)
Var (Rm) is the dispersion of market returns
You can calculate Var using VAR function in excel
Let’s explain this step by step
First download the daily values of the market index and the stock prices in two columns. Download the data for 1,2,3 or more years
After that calculate daily returns for the index and for the stock
After that use COVAR and VAR excel functions to calculate Beta
The example of calculations in details you will find in "example" file
Rm (Market return)
Market return is usually calculated as an average historical return of the country's index
Usually analysts use 30 years history…or 50…That is for you to decide 
For US stock market I see Rm between 4% and 5.5% more often
Some stock exchanges on Emerging Markets have a very short history. 
In this case you can simply add Country Risk Premium to the historical return of a mature market (well it's actually US stock market)
More on calculation of the county risk premiums, as well as particular rates, you can find on Aswath Damodaran's page:
http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/ctryprem.html
For example, let's calculate Rm for Cuba
We have 5% market return in US and 11.25% country risk premium for Cuba (see the link above), so Rm for Cuba = 5% + 11.25% = 16.25%
Now we can calculate the basic cost of equity. 
Some analysts find this calculation not sufficient and add different premiums to the basic cost of equity
For example, one of the most widely used premiums is liquidity premium
Analysts add 0.5-3% liquidity premium to incorporate the risk of inability to sell the stock immediately
You can add premiums for poor transparency, weak corporate governance, risk of shareholders conflict etc 
Although some certain mathematical approaches to calculation of these premiums exist, many analysts often simply use their judgments
COST OF DEBT      
When you determine the cost of debt for the company you simply try to find at which rate the company is able to borrow money
If the company has publicly  traded bonds, your task is very easy - just use the yield of this bond as a cost of debt
Note: cost of debt (rate) should be calculated for the debt nominated in the same currency as your DCF-model (Discount Cash Flows model)
If you calculate your DCF-model in dollars, use dollar nominated bonds' yield
If the company has no traded debt - try to find similar company with publicly traded debt. You are lucky if you find a company of a similar size with the same 
rating as yours.  Use this company's debt yield as your cost of debt
If it has no rating, continue searching for a similar company with publicly traded bonds.
The company should be similar in the structure of the capital, in size, it should be from the same industry
If the company has a debt in form of a bank loan, try to find the info on the rates on this loan in company's reports (financial statements, MD&A, SEC fillings)
The bank loan is usually more expensive than bonds so you can make an adjustment if you want
If the company does not disclose the info on its rates, search this info on banks web sites, maybe ask your friends who work in bank about the possible loan rate for your company
In the separate excel files you can find examples of WACC calculation for different companies
EXAMPLE 1. For US incorporated company
We calculate WACC for ConocoPhillips
EXAMPLE 2. For non-US incorporated company
We calculate WACC for Brazilian Petrobras