## DCF model

A **DCF valuation** is a valuation method where future cash flows are discounted to present value. The valuation approach is widely used within the investment banking and private equity industry.

## DCF Valuation – step by step guide for free

This tutorial is quite simple and straight on – to give you a good understanding of the DCF model and how it works. If you want to take your modeling to the next level I can highly recommend **this modeling package** which I believe is the best available financial modeling tutorial on the market.

### To start – Download our free DCF model template in Excel

Download this **DCF model template** so that you can calculate the value of a business. All input will be described in the DCF model tutorial below.

## Step 1 – Enter historical financial information in the DCF valuation

### Enter historical information

Enter the historical information of the company you wish to value, this information can be found in an annual report or can be ordered via this link for example. It depends on what company you wish to estimate value of. The CAGR (Compounded Annual Growth Rate) and the percentage numbers will be calculated automatically when you have entered all information. Below is a picture of the information you should fill in:

### Steps

- Enter net sales, total costs, EBITDA, Depreciation & Amortization for each year, which will sum up to EBIT.
- Enter taxes paid, in this example 30% is used, but it varies from country to country
- Enter Capex (Capital Expenditure) which is the annual investments for the company each year. This is normally specified in the Annual Report under Cash Flow Statement. If you cannot find the information in the Annual Report you can also take the difference from two years in tangible assets. For example, if the company had tangible assets of “100” in year 2006 and “110” in 2007, the company spent “10” on investments (CAPEX) during 2007.

The historical information will be used to make “likely” forecasts of sales growth and EBITDA margins which will be performed in coming steps.

## Step 2 – Enter historical working capital

In step two we are entering historical information. This is needed in order to make good prediction of future working capital needed. The working capital is such an important and difficult input, which needs some extra attention.

In the picture below we have circled the information you should supply in order to calculate the change in net working capital. The first circle shows the outcome of the information supplied:

### Steps

- Enter Account receivables,
- Inventory and
- Prepaid expenses and other
- This information will sum up to
**Total Current Assets** - Enter Account payable,
- Accrued Liabilities and
- Other Current Liabilities
- This will sum up
**Total Current Liabilities** - The total Net Working Capital will now be calculated automatically in the model

If you have followed this step by step tutorial this far, and enjoyed it, I would very much appreciate if you could help me promote this website by clicking the google and Facebook symbols below to recommend this site to others:

## Step 3 – Make future projections

The projections in the DCF model have large impact on the valuation, therefore, this step is extremely important. We will now use the historical information as a base in order to make good and likely projections of the future.

In the picture below we have circled the information you should supply. However read the instructions below the picture before you make your assumptions and input.

### Steps

- Make projections of future sales by looking at historical values – In this example the business has had an annual organic growth between 14% and 21% implying a CAGR of 16%. This is normally a good measure for future estimates. However, we have spoken to the management of this company and they have a financial target of 6.900 in 2014 and that is why we use an annual growth of 8% in this example.
- EBITDA margins – look at historical values. This company has had EBITDA margins in the range of 13.5 – 15% in the past few years. In our example we have chosen to use an average value of the historical information in the projection period, implying 14.3%. This number will determine EBITDA in the projection period.
- Depreciation and amortization – look at historical depreciation in relation to sales and use an average from these year to use in the projection period. In this case, it was quite simple, we used 1.8% of sales.
- Taxes – use either the historical tax level or the business tax applied in your region. We have used 30% in this example.
- CAPEX – Determine capital expenditures that you believe the company will have in the future. This is quite difficult to estimate, therefore, use an average of the last five years in relation to sales. In our example the capex actually decreased between 2009 and 2010, which might be inappropriate. However, after a short discussion with the management of the company we still decided to use 2.3% of sales

## Step 4 – Calculate Unlevered Free Cash Flow, DCF model

We shall now calculate the unlevered free cash flow, but first we need to make some assumptions regarding the working capital and estimate the needs in the projection period.

See comments below picture:

### Steps

- Estimate total current assets in the projection period. Use the average during the past four years in relation to sales
- Estimate total current Liabilities in the projection period. Use the average during the past four years in relation to sales
- The net working capital will now be calculated automatically based on your above input
- The difference (increase or decrease) between e.g. 2010 and 2009 will now be subtracted or added to the cash flow. A growing business will normally take on more working capital for each year, which will lower the free cash flow
- The Free Cash Flow can now be calculated for every year in the projection period!

## Step 5 – Target Capital Structure and Beta

This section is for you who have access to a database such as Bloomberg or Reuters. If you do not have such access, you can type in:

*Debt to Total Capitalization: 30%
Equity to Total Capitalization: 70%*

This is the most common assumption when determining capital structure in a business valuation model. However, the below described method is more accurate and preferred if you have all the needed tools.

The picture describes the input we have made in our example valuation, which is further described below:

### Assumptions and input

- Identify a couple of listed companies that are similar to the one that you want to estimate business value upon
- Enter the listed companies beta which can be found via a database such as Bloomberg
- Enter the Market Value of debt of these companies. The market value of debt is the same as book value of debt
- Enter the market cap for the traded peers
- Enter the marginal tax rate
- All this information can be found in the database. Now the model will calculate the Beta and unlevered and levered Beta and put as input in the valuation model

## Step 6 – Determine WACC

The capital structure is given from the previous step and works as a base for determining wacc in the calculation below.

### Assumptions for WACC

- Enter the risk free rate. This is the same rate as the 10-year treasury bond and can most likely be found on your government’s website
- Enter the market risk premium – this is used to adjust for specific company risk and should be 7.1% according to: Ibbotson
- The Levered Beta is given from previous excercise
- Now add a size premium according to Ibbotson as well. We have used 1.7% since our company is pretty small
- Now enter cost of debt, the rate which your company gets to borrow money at. If you are not sure, you can calculate the rate by dividing interest paid during the last financial year with total debt
- Enter the tax-rate for the company’s current country – so that the tax-shield deduction can be calculated

## Step 7 – Present value of free cash flow

Next step is to calculate the present value of the generated cash flows in the projection period.

### Steps

- Make sure the WACC is correct according to step 6
- The Discount Period is set according to the mid year method and could be leaved as is since the cash flow is evenly generated through the year
- The Discount Factor is calculated with WACC and the chosen Discount Period
- The present value of the free cash flow is now automatically generated/li>

## Step 8 – Calculate Terminal Value

The terminal value has the largest impact on the valuation and it is extremely important that this input is correct performed.

Most values are already given as can be seen below:

### Steps

- Perpetuity growth rate is the rate at which the economy is expected to grow, this is normally 2.5% or 3%
- Make sure the implied exit multiple isn’t too high, since that probably means your assumptions are too aggressive in the terminal year. Another way of “judging” if this value is too high, is if you put it in the relation of the later calculated enterprise value. If the terminal value is more than 80% of the enterprise value, it is likely that something is wrong with your assumptions

## Step 9 – Enterprise Value

The DCF valuation is almost done, you have made all the inputs required and the enterprise value is already calculated. Now we will try to describe the results and make sensitivity analysis.

Below are the results in our valuation example:

### Comments

In the results above you can see the enterprise value of the business and some multiples on the 2010 years estimated results. You should also enter debt, cash and outstanding shares to get additional information on your valuation.

## Step 10 – DCF Sensitivity Analysis

With this sensitivity analysis you can see how the valuation changes with different assumptions and changes in input.

This is our sensitivity analysis:

### Comments

To perform a sensitivity analysis like this, you should copy the exact value of your WACC, EBITDA %, Perpetuity Growth and annual sales growth in the middle of each row and column. The numbers that you should replace are dark blue and bold.

### Valuation Range

It is now time to decide the valuation range for the company. In the example valuation we decide the range by changes in WACC with 1% up and one percent down which gives a range between approximately 5 000 – 6 000!

## What now? – Further reading

If you want to become a real Investment Banker and master financial models, I have two recommendations for you:

The first recommendation is by far the best financial modeling guide available on the market. If you can afford it you should get it, since it will help you understand all aspects of valuation. **Link »**

The other recommendation I have is the book **“Investment Banking”** by Rosenbaum and Pearl which is a fantastic book with some great tutorials and it is less expensive than previous mentioned tutorial. It describes the dcf model as well as other valuation approaches extensively and also gives you a complete DCF template in Excel included with the book. The tutorials in the book are well written and easy to follow.

## Other Valuation methods

## External guides and resources

Question about calculating levered beta, the what happens when Equity market value is negative ? where do we get levered beta from. I tried with a company and yahoo was not even showing D/E, as E was negative. in this case, how should we get relevered beta ?

Then use the industry average or median D/E as a target capital structure.

Select a few similar companies (peers) and calculate their D/E ratio and apply this average ratio on your company if it seems relevant.

DCF model very nicely explained.I also enjoyed reading Rosenbaum and Pearl.

D S Prasad

Faculty(Finance)

IBS,Hyderabad

India

What is the 2010- 2011 market risk premium number – you had it in this model at 7.1% according to: Ibbotson. Thanks

Walt,

This link could be useful: http://www.iese.edu/research/pdfs/DI-0920-E.pdf

Choose the market risk premium based on the region of the company wou wish to value.

Dear Sir,

I need to know the Levered Beta and Unlevered Beta for Event Management industry. Kindly let me know if you know it.

Regards

Yash, have a look at this link for levered and unlevered betas: http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/Betas.html.

Look for your industry on Damodaran’s frequently updated website . If you cannot find an exact match, look for a similar industry or a broader industry in which the industry you are looking for is included.

Good luck!

Sir/Madam:

in business valuation report Income Approach DCF re: Mid year factor applied sample below:

FY2010 FY2011 FY2012 FY2013 FY2014 TV

0.5% 1.5% 2.5% 3.5% 4.5% 5.5%

is it correct to assume that the 4.5% of FY2014 should be carried forward to TV rather than the 5.5% given by the evaluator? Kindly advise and confirm correctness of TV-5.5%. thanks.

According to experience and standard business evaluation 4.5% should be carried forward to the Terminal Value (TV) and not the 5.5% which exceeds average growth rate assumptions. Kindly confirm. Thanks.

HondoBat21, If I were to use the mid year discount method, I would use 5.0 in the terminal value as discount factor.

I want to know when to use fcff and Fcfe model.and what adjustment to regarding debt part in fcfe and fcff model

P.Kumar, I am not sure what you mean with your question. Could you please specify?

may i know do you assist with valuation for companies for academic purposes

Kumar wants to know whether to use the Free cashflow for Equity (fcfe) or the free cashflow to firm (fcff) model.

Sindi,

It depends on the project.

Roger, P.Kumar,

I have copied two interesting links on the question you have asked.

– http://www.editgrid.com/user/wikiwealth/FCFF_vs_FCFE_Valuation_Model

– http://www.numeraire.com/value_board/messages/78.html

Let me know if you need further examples to fully understand the difference.

It is very useful.

I have a few questions to ask.

1. Can we use the company D/E ratio itself instead of industry D/E (based on your example, you used the industry D/E)?

2. For the terminal value, why do you add only 0.5 yrs for discount period (4.5 + 0.5), why not 1 yr?

Thank you very much.

Pear,

1. Yes you can use the company’s D/E ratio. It is sometimes more correct – it all depends on the situation. If the D/E ratio is reasonable, you should use the company D/E ratio. If the company has too much cash, or to much leverage you should use the industry ratio and adjust for this (otherwise the value of the company will either be too high or too low.). But remember, you need to argue for why you choose either method. Both ways are correct. It is not the financing of the company that is interesting in this part in the DCF valuation, it is what the business itself is worth – not its owners ability to finance the business. A potential acquirer may have a lot of cash which it easily could add to the business…

2. If you have decided to use the mid year discount method (which is correct), the company/owner is in control of the cash evenly troughout the year. Some cash is paid out in January and some in December, to mention one example. To adjust for this you use the mid-year disount period.

But when you calculate the terminal value, you “are in control” of the cash flow at the end of the period (in this DCF model, in the end of 2014). That is why 0.5 is added. 1.5 would be in the middle of 2015…

So for the Terminal value, you get all the cash at the end of the year (2014) and that is why you only add 0.5.

Let me know if you have further questions, good luck!

Many thanks fo your efforts.

I want to know can we add the value of the Fixed assets in this way to have the value of the company ????

Tom,

That is a different valuation approach and can be useful when you compare your cash flow valuation and other business valuation methods. The book value is the value of the company’s assets (intangible and fixed assets).

Sir,

I have few questions

1.Why you didnt consider the Depreciation in the calculation of Unlevered Free Cash Flow.

2.in determination of Net Working Capital, do I have to add the Postdated Cheques recieved from customers to the Account receivables.

3.If I have debit or credit balances in Current assets / Liabilities from companies in the same group i.e within the same holding company, Do I have to consider these balances in calculation of Net Working Capital.

Regards,,,

It’s very useful.

Thanks for all efforts.

Regards.

Dear Sir,

No reply till now

Thanks in advance

Tom,

1) Depreciation is not a cash flow component and does not affect cash flow, it only reduces tax.

2 and 3) These questions are business specific question, to give you a correct judgment on these questions, I would have to verify the underlying components myself and understand in what specific situation these questions arises. It all depends on the business, case-by case, recurring or non recurring?

Regarding no 2. Yes it sounds like it should be added.

3). All working capital that is needed to run a business is working capital and should be included in the net working capital calculation. If you are valuing the Group, then you have a different P&L and balance sheet where internal transactions are eliminated (consolidated). The answer is both yes and no, it depends on what you want to accomplish, what company you want to value etc.

To BV Pro,

If we are supposed to valuate and analyse the cash flow of a pharma business, would it be correct to assume that a straightforward DCF analysis is not applicable?

Would there be a better valuation model to use? I am not sure about the approach here because pharmas tend to have low revenues and high R&D costs?

From an investors point of view why would such a pharma stock be practically attractive?

Will this template work for a start up company, without historicals? If I have projected sales, expenses, etc for the company. What would be your recommended way to approach this type of valuation?

Thank you

Brendan,

If you have projected sales, expenses etc. It could work… It also depends on what type of start-up, how large etc. But the future is very uncertain (especially in a start-up), therefore you need to increase WACC big time with e.g. a small-size premium. But it depends on the situation.

Sometimes the sales can be pretty easy to foresee – but sometimes it could be just wild guesses, often too optimistic which will result in a value that is way too high in the dcf model.

In a business valuation, you (always) need a DCF model in one way or another. I would probably try to do one, and also combine it with a trading comps valuation and a precedent transaction valuation. To estimate the value of a start-up is difficult.

Mou P,

I am not the right person to ask. It is difficult to foresee future cash flows of a pharma business. Therefore you also do see their stock prices going both up and down with high volatility. I believe many investor (at least some) see pharma business as a kind of lottery ticket, e.g. I have a 10% chance of a XXX% increase. If all fails, I will only lose XYY. But that also depends on the maturity of the pharma business, in which phase it is currently in, what projects it has on the table and so forth.

If I were to value a pharma business I would try, if I could, to do a

dcf valuation, but I would not rely on it since it would probably be just guesses (not even the management knows the outcome of new potential projects)… I would definetely do acomparable company valuationbut maybe also see how much money the company has spent on R&D historically.Dear BV Pro,

Thank you for letting me use this template. I did some calculations and I have a few questions. First two are:

1. Is Present value of Free Cash Flow in Cell Q5 a value of the business now in 2012?

2. Is Enterprise value in Cell Q12 a value of the business in 2016?

Is there a way to contact you besides leaving comment? ie email?

Saken,

No problem, my pleasure!

1 and 2) The valuation in this example is per 2009-12-31. You discount future cash flow into a value of “today”. In my example per 2009-12-31. The terminal value however is a theoretic value per 2014-12-31 in my example.

So Q19 is where you should look!

Let me know if you have further question, I am happy to answer these here as comments.

Thank you for getting back to me.

I failed to mention that what I did is change the actual years and forecasted period. I took out 2006 completely, and changed the years to 2009-2011. For the forecased period, I changed the years to 2012-2016.

This is basically to bring it uptodate.

But we’ll use your original file as to not confuse both of us.

1. So from your post I understand that Present value of Free Cash Flow (Q4) is the present value of the firm for year 2009.

2. And Terminal Value (Q7) is the theoretical value of the business in year end 2014.

3. What does the Enterprise Value (Q12) reflect? I thought that this reflects the value of the business in year end 2014, no?

4. Q19 is implied share price. Times it by 10,000 shares gives us $5740, which is the Implied Equity Value. You’re saying this is what I should look at to see what the business will be worth by the end of 2014?

I’m sorry I’m a little confused by different values. I’m just trying to find what the business is worth in the present time and future time at the end of the forecasted period. This is to help me pitch to investors, give them the values and they would pay for what share of the company.

5. Also, what’s stopping us from adding projected numbers for 2015, 2016 etc and increasing the projected value of the business in the process?

Saken,

1. Cell Q4 is; present value of free cash flow. Not present value of the firm. Meaning, the value of future free cash flow as of “today”.

2. Yes regarding Q7. Q9 is then the discounted value of the terminal value, as of “today”. Which is then added into the enterprise value in Q12.

3. Enterpise value reflects the value of the business as of “today” including net debt. All future cash flows and the terminal value are discounted with wacc to represent the value of today.

4. Implied share price, Q19 is what every share is worth “today”. When you buy a share for this price, then you pay for/expect future cash flows, but with a discount. The reason why you discount is, simply put it, because you have to wait for the cash which also means uncertainty. If you were 100% sure of the future cash flows, then the discount would be less (meaning a lower wacc).

5. You can add more years in the forecast period, it will make the dcf model even better (less impact of the terminal value). Normally you use a ten year forecast period, but I only had five years in this model to keep it simple.

i would like to know how to calculate perpetuity growth rate when the forecasted cash flows are not always increasing and having highs and lows.

and what are these multiples exactly as in above its written implied exit multiple as 7x as i’am calculating enterprise value i need to know….can neone help me??

Why is cash not included in current assets?

Johnny Ice,

In the annual report and balance sheet, cash is not divided into “cash used as working capital” and excess cash. Some cash should however be added to working capital, depending on the business. What you can do is to use ~2% of sales and add this into current assets. But it depends on the business, some companies need more than 2% and some need much less (if any).

Raksha,

The perpetuity growth rate should be the growth rate of the economy where the company operates. If your dcf model is made for a company in the US, then you should use the growth rate of US economy (~2%)

It is just a multiple so that you can see if the assumptions you have made a reasonable. An exit multiple of >15x is not very likely, one below 3x is not very likely either. If you end up either above or below, some of your assumptions are probably either too optimistic or negative.

Thanks, your DCF Valuation analysis is very concise and explicit. Thanks once again.

However, I have some pertinent questions to ask.

a) Can this DCF model be used to value majorly commercial banks (or be used for other financial services firms too e.g Insurance)? Because i understand that Bank’s DCF valuation is peculiar because of the nature of their business model (regulation etc). Hence, if it can not,please shed more light on how to value banks using DCF.

b) The same question about valuing oil and gas companies.

c) When calculating the Terminal value, must the the two approaches (Perpetual Growth Method and the Exit Multiples) give the same value?

Thanks

Emerald,

Thanks for visiting my site!

a) For bank valuation or insurance you will have to use a different model than the one above, a specific

bank valuation modelsuch as:LINK: Bank (FIG) Modelingb) For oil and gas valuation models you will have to look on this link named Oil and Gas Financial and Valuation Modeling

c) Absolutely not. If the values are in the same range, your assumptions are probably correct. If the values differ a lot, then you will have to go through why they differ so much. Probably some unrealistic assumptions, or maybe they do differ because they should. If that is the case, you will have to choose one of them and argue why one is better and more correct!

Thanks.

Again, can you do an article/write-up for this forum on how to value banks and financial institutions as well as oil and gas sector.

Have a question,

in your DCF model, why is your formula for “Net Working Capital” adding both current assets and liabilities, shouldnt it subtract? Please take a look. It is confusing. Thanks.

Hi Roger, thanks for asking. Total current liabilities, on row 53, is in my excel template negative – which means that in order to calculate net working capital I will have to add these two lines together (row 47 and row 53). Maybe it would be more natural to show current liabilities as positive numbers… I don’t remember why I did not do this (maybe to show that liabilities in fact is a liability and not an asset).

okay makes sense…thank you! Also, for net change in WC, why is it that its decrease from 2006-07 when it seems like it increased from 155 to 300?

Great template and instructions. 2 questions, if you’re still responding….

1) I used this to value a UK small-cap however the sensitivity analysis would not update to the correct value. I tried reinserting Q12 and some other stuff but nothing seemed to work – so my sensitivity analysis tables have different (and wildly inaccurate) values. Any ideas?

2) What would be the best way to go about performing a valuation of a stock like Kering (KER.PA) a fashion house with a multitude of different brands (Gucci, McQueen, Saint Laurent) and associated revenues/ stores/ costs etc.

Best,

J

Hi James,

1) It is important for the sensitivity table to work that you have exact values from your model pasted into following cells: W4, T7, T15 and A12. For instance, if you have a different wacc than set in the table, it will show different values. If you have the exact numbers including decimals correct, and it still not shows the right values, then maybe you have deactivated tables to automatically update. If this is the case, let me know and I will try to guide you.

2) This is not an easy question. The stock price is of course the best measure, but if you want to do your own valuation, a SOTP (sum-of-the-parts-valuation) will of course be the best valuation approach. Combine this with a cash flow analysis and trading comps (and transactions), then you have a complete valuation set-up.

Good luck with your valuation!

If I may ask;

Why do you only take the next 5 years into account instead of making the periods indefinite?

Best regards,

Peter,

dutch student IBA

Hi Peter,

Thanks for your question.

By having a terminal year at the end of the forecast period, the period is what you call indefinite. What you could do is for example to add additional 5 years into the forecast, to make the forecast period even longer. But it is very difficult to predict what will happen ten years from now…

I am including the following line items in my working capital calculation, but each year, cash goes up substantially and the net increase in working capital (assets-liabilities) gets so large that Unlevered FCF just keeps getting more and more negative as I go further out. Why is this happening?

TOTAL CURRENT ASSETS:

Cash

Accounts Receivable (Net of Allowances)

Platform Development Costs (Net of Accumulated)

Other Current Assets

Due from Affiliates

Member Loan Receivable

Employee Loans (Current)

Computer Software, FF&E and Leasehold Improvements (Net)

Employee Loans

Security Deposits

TOTAL CURRENT LIABILITIES:

Line of Credit

Loan Payable (Current)

Accounts Payable

Accrued Expenses

Loan Payable

Deferred Tax Liability

Deferred Revenue

Deposits Held

Total Current Liabilities

Fantastic work on this template. Great for educational use as well. I just had one question.

I understand that making all liability cells negative can make it easier to view the values as liabilities but when calculating net working capital, why do you minus the current year from the previous year. Should this not be the opposite?

For example, cell C22 is calculated as B56-C56. Perhaps you just forgot to include a negative sign?