Brokers' forecasts

Valuation approaches are mainly based on forecats. 

For most listed companies, the French Zonebourse website provides a Reuters' consensus on some core aggregates: 

  • Sales
  • EBIT
  • Profit Before Tax
  • Net income

Market risk premium

The discount rate includes the cost of equity (k) which is used in the DCF approach is based on the Capital Assets Pricing Model (CAPM) : 

k = rf + beta x [E(RM)-rf] where rf is the risk free rate and E(RM)-rf  is the market risk premium.

  • The risk free rate corresponds to the 10Y T-Bonds rate and can be easily found on the web for for markets.
  • The betas of most listed firms can also be easily fond on the web. They are provided for free on Reuters' website
  • The market risk premium is provided every year, country by country, by Aswath Damodaran, a professor of Stern New York University. Its market risk premiums are commonly used by investment bankers. 

Implied volatility

There are 2 ways to calculate a stock's valatility

  1. Historical volatility which corresponds to the standard deviation of the stock's past returns. This simple approach has 2 main drawbacks: on the one hand there is no unique historical volatilty as the output depends on the observations' period and periodicity; on the other hand, it does not take any forecast into account, assuming that the future replicates the past.
  2. Implied volatility for stocks which are financial options' underlying assets. This implied volatility (s) is unique and includes the market's expectations. It is obtained thanks to a the Black & Scholes' option pricing model. Indeed, the call premium, as a listed security is easily read on the web and the 4 other parameters of the Black and Scholes' formula are known: 
  • S = spot price of the underlying asset
  • E = strike price of the call
  • r = risk free rate
  • t = time to expiration.
The Call premium (C) is a function (f) of the 5 parametres including the volatility (s) : C = f(S,E,r,t,s). 
Then : s = f--1(S,E,r,t,C)
There is no formal algebrical solution to get s. 
But Excel enables to get it thanks to its "gaol seek" function (which can be found in  "Data - What if analysis - Goal seek"). 
In that context, based on a Black and Scholes' option pricing model, Excel is asketd to change s so that the known C call premium is reached.

Example : 
C = 22 euros
S = 120 euros
E = 100 euros
r = 2% in discrete time ie ln(1+2%)=1,98% in continuous time
t = 3 month = 3/12 year = 0,25 year
Then, using Excel's goal seek: s = 36%
The following movie enables appropriate the principle efficiently

Building tables on Excel

The tables are probably Excel's most useful tool for finance. Indeed, finance is focused on assets' valuation which relies on forecast which are intrinsically uncertain. 

To cope with this uncertainty, the best thing to do is whether to contemplate many contingencies for at least 2 variables which ca, be looked upon as core drivers of the valuation. Investment bankers are used to crossing 2 sets of levels of risk free rate and perpetuity growth rate to get the equity value. 

The variables to be crossed must be that have entered (and not calculated) in the model's central case.  

The table is obtained in "Data - What if analysis - Table". Then a small screen appears. It requests to clik on 

cell in row: first variable in the central case which takes several values in the first line of the table
cell in column: second variable in the central case which takes several values in the first column of the table

DCF Loop on the equity value

The DCF is a way to value the firm's assets as the sum of the present values of the future Free Cash Flows (FCF).

The discount rate is the Weighted Average Cost of Capital (WACC) = k.E/(E+D)+i.(1-t).D/(E+D) where:

  • k = cost of equity based on the CAPM
  • i = cost of debt
  • t = corporate tax rate
  • D = last available net debt
  • E = equity value

The equity value has to be an economic one. A proxy could have been the firm's market cap if it was listed or its value based on a listed peers approach. 

But, for the sake of consistency, as an aggregate is supposed to have one single value in a model, the right approach is whether to include the equity value which is the final output of the DCF modelling.

The equity value is required to get the WACC which enables to get it. That means the model has to loop on itself. This is possible as Excel enables the iterative calculations.

To do that, a first calculation of the DCF value has to be done with a first dicount rate. This first discount rate has to be entered in order to build the model and get an equity value. 

Then the first discount rate is replaced by the WACC formula taking the equity value into account.

At the end of the day, to terminate the modelling: "File-Options-Formulas-Activate the iterative calculation"

Excel file


Assets' volatility

The volatilty of listed assets can be easily calculated taking the historical or implied volatilty into account as described above.

The valuation of equity based on real options is based on an options pricing model assuming that equity is a call premium on the firm's assets. Then the volatility to be included in the model is that of the firm's assets which are not listed, as least for most of them.

As described in detail in the p.23 of the handout which can be found in the M&A page, Hull, Nelken and White (2004) proposed a calculation of the assets' volatilty which consists in solving a system of 2 equations with 2 unknowns: enterprise value (EV) and volatily (s). The 2 equations are:

  1. The Black & Scholes formula
  2. A formula to be looked upon as a consequence of the Ito's lemma, assuming a geometric brownian motion for the equity value

Such a non linear system of equations can be solved thanks to Excel's solver. 

The following Excel sheet includes the useful formulas to use Excel's solver based on the following assumptions

  • Equity value (based on the firm's market cap): 3 million euros
  • Debt: 10 million euros
  • Risk free rate: 5%
  • Time to expiration: 1 year
  • Volatility of equity: 80%
The solutions of the system of 2 equations with 2 unknowns are:
  • EV=12,40 million euros
  • s=21,23%

Excel model

The followig movie paves the way to use Excel's solver


Standard normal distribution table and other tables

Most calculations around financial and real options are based on the Black & Scholes or Merton formulas which assume the log-normal distribution of the underlying asset's price evolution. That's why theses formulas include the use of the standard normal distribution function to calculate P(X*<d1) and P(X*<d2).

Practioners use the Excel standard normal distribution function and tape, for P(X*<x): 

  • in English: =normsdist(x)
  • in French: =loi.normale.standard(x)
For students, tests require the use of the standard normal distribution table that is widely available on the web and in books. In the file below, the table has been built on Excel, thanks to the abovementioned function;
The following file also provides 2 additional sheets: one for the Fisher-Snedecor cumulative distribution function assuming a 5% risk of error and one for the Student cumulative distribution function

models for finance

Equity value and Black & ScholesFile
LGD and spread on debtFile
Patent valueFile
Option to abandon File
Oil concession valuation File
Cox-Ross-Rubinstein option modelFile
DCF valuationFile
M&A peersFile
NAV and SotPFile