20 September 2018 Experts in DCF and LBO modeling

DCF model tutorial with free Excel

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. Read more about the DCF model here (underlying assumptions, framework, literature etc). On this page we will focus on the fun part, the modeling!

Step by step DCF Valuation tutorial

In these coming 8 steps, you will be able to perform your own DCF Analysis. This DCF model is simplified in order to teach you the basics of a DCF valuation.

Start by downloading this DCF model for Excel (it is 100% free and takes less than 1 minute to download). All required inputs will be described in detail below.

Expand each section to follow the (free!) DCF-model tutorial. Now lets do some modeling!

Sneak peek of the DCF valuation (click to enlarge screenshots)

Step 1 - Enter historical financial information in the DCF valuation
Step 2 - Make future projections
Step 3 - Target Capital Structure and Beta
Step 4 - Determine WACC
Step 5 - Present value of free cash flow
Step 6 - Calculate Terminal Value
Step 7 - Enterprise Value
Step 8 - DCF Valuation range Output
It would mean the world to me if you could help me with a 5-star rating here and possibly also some nice comments, if you have completed this tutorial successfully.

This will encourage me to continue to develop the best Excel models and will take very little of your time…

Any questions??

Business valuation proI will do my best to help you quick

I will gladly help you with any question you might have. Either comment below the relevant content or send me an email or use my contact form which can be found here.

Specialities: Business valuations, Excel models, Business development, Investment Banking, Buyouts, Divestments, Share issues, IPOs, Management Buyouts, Leveraged Buyouts.

Have a great day! (thanks for sharing my website which helps my content beeing available to more users)

69 thoughts on “DCF model tutorial with free Excel

  1. Hello,

    Thank you for you for this excellent spreadsheet.

    I have a few questions:

    1/ I try to value a $100 million US company. For such a small cap, is it possible to forecast an increase in NWC every year (+2% as a percentage of sales)? That allows my terminal value as a % of enterprise value to decrease.

    2/ Is 74% correct as a % of enterprise value or it needs to be under 70%? I try to be as much conservative as I can but can’t find a reasonable way to be under 70%…

    3/ According to Aswath Damodoran’s website, the current equity risk premium stands at 4.78% (adjusted payout) or 5.18% (cash yield). Are you sure the current ERP is still around 7% as in your model?

    4/ I don’t understand what the discount period is, should I keep it at 0.5 in the first year forecasted?

    Thank you,

    Best regards,

    Pierre-Alexandre Ravoire

    1. Dear Pierre-Alexandre,
      I will try to reply to your questions;

      1) If a company is growing, NWC is almost always growing also. A normal way to approach this is to use NWC as a % of net sales. It makes sense that it reduces the Terminal Value, if the company is growing into perpetuity.
      2) If you mean Terminal value is 74% of total value… I personally do not like when the Terminal Value is too high, but if you cannot get it lower, then it works with 74% also. One way to reduce the Terminal Value is to make the forecast period longer, from 5 to 10 years,
      3) The equity premium changes several times per year, it is also depended on what market/country your company is active in etc. Aswath Damodaran knows what he is talking about… so I would definitely use the numbers you sourced from him (as long as you source the where you got the info from). Maybe this link can also help you: https://www.business-valuation.net/small-cap-premium/
      4) It can either be 1 or 0.5 depending on the method you use. If you use 1… then it means you value the company from 1 of January year [XXXX] (starting year). In this model I have 0.5 because you do not get the cash flow at the end of the year, you get half of it in the first 6 months, and the rest on the last 6 months… It is called mid-year discounting and kind of makes sense if you think about it. Both methods work however!

      Good luck!

      1. Hello,

        Thank you for your answer, that’s helpful.

        The issue is that I don’t have much history on this stock, only 2 years of historic datas… So it’s a bit difficult to know if their NWC was too high last year or if is their normal. Using their current NWC the fair value decreases a bit but the terminal value increases to 80% from 74%…

        Yes indeed more years of estimates would help but the model stops at five years of projected data. Plus, as far as I’m concerned the shorter the period of forecasts the better… 🙂

        Best regards,

        Pierre-Alexandre

        1. If you do not have enough history of the company, then you can look at peers… For example; if all peers have 15% NWC / net sales… Then you can use that as an estimate, if the company is similar.

          And regarding valuation approach, sometimes a DCF is not always the best solution! a simple multiple-valuation can of course also work. Read more about trading comps valuation here; https://www.business-valuation.net/methods/comparable-companies-analysis/

  2. Thank you for putting together such a comprehensive spreadsheet. I have a few questions…

    In your spreadsheet it seems you have EBIT= EBITDA + Depreciation. Isn’t that double counting the D&A? I thought EBIT + DA equals EBITDA. Shouldn’t EBIT simply be pulled directly from the 10k as “operating income”, then adding D&A to achieve EBITDA? Im confused. Furthermore, you instead derived EBITDA from Net Sales minus Total Costs. If I do that, then I get a totally different value versus simply adding EBIT (operating income) to D&A to achieve EBITDA. Can you clarify the reason behind why you did this? Thank you.

    1. Dear Pat, I totally understand your question. The way the model is built is that all costs, are made as negative numbers in the input cells… Therefore EBIT + depreciation equals EBITDA, exactly as you have stated. But in the model we use net sales – total costs = EBITDA… and then to come to EBIT you deduct the depreciation. I think you will understand what I mean if you download the excel again, as it was before you made changes to it. Good luck!

  3. Hi,

    It was wonderful I found this site!. Thank you so much for the tutorial, it was amazing! I just would like to sk you, where can I get the information for step 5? I mean should I have an access to the paid terminal of Bloomberg or it is public?

    Mariya

  4. Hi, I enabled the option you told me about. And my number still off by a lot! Especially för Thé second sensitivity analysis

    1. Hi again MM, good you solved the iterations part. Now you need also to change the numbers in W4 and T7 (W8 and T15 for the second one), to match the assumptions you did in your model. Basically in the top sensitivity analysis, use the your exact perpetuity growth rate and WACC that was used in the model. Good luck!

  5. Hi, how do you exactly perform Sensitivity Analysis after coping the values of X and Y?
    Thanks for your amazing work!

    1. Hi MM, If you have done everything right, and still dont see any result, it may be so you have to activate iterations in your excel. It can be found under settings / options/ formulas and then click “activate iterative calculation”. Let me know if you did not get it to work.

  6. Thank you for this great template.
    I noticed that the financial charges not listed in this example. Is this company don’t have these charges or I shouldn’t include it.

    Regards,
    Raed

    1. Hi Raed, it depends really on the situation. If you are looking from a buyer-perspective (wanting to acquire the business) you will have your own financing anyhow, so normally you do value a business in a dcf model without interest costs. The leverage is reflected in the WACC where you have both equity and debt.

  7. For Digital Media Distribution companies many times payments for rights to content and intellectual property rights are made in advance and subsequently amortised as they are consumed ie sold over the period. This amortisations constitute a major part of the cost of sales. The purchase of this rights is more like buying inventories. Typically amortisation are added back to determine EBITDA & Owner’s Discretionary Cash Flow. I think in this industry (Digital Media Distribution) such amortisation should be treated as inventory purchase and not added back as typically amortisation. Please what is your take and any guidance on this.

    1. Uzoigwe,

      Good question. If you must use a DCF model to value this business, then you shall use the exact cash flows for each year as they will appear. If you sell or buy someting year 1, this cash effect shall be seen year 1. A DCF model only takes into account free cash flow per year (over a period of years), then discounted back as of today. Amortisation only has a TAX-effect in this context (and in such way affects the FCF).

  8. 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?

    1. Hi Blair and many thanks for your kind words.

      The reason why you do this, is because you want to include the difference between the years, that actually consumes cash (if the working capital level increases in the company). In a growing business you sometimes need to increase the inventory level, and this consumes resources (cash converted into inventory). That is why you want to include the effect between the years in Net Working Capital (NWC).

  9. 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

  10. 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…

  11. 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

  12. 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!

  13. 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

  14. 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?

  15. 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).

  16. 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.

  17. 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.

  18. 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

    1. 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 model such as: LINK: Bank (FIG) Modeling
      b) 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!

    1. 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).

  19. 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??

    1. Raksha,

      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.

      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%)

      what are these multiples exactly as in above its written implied exit multiple as 7x

      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.

  20. 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.

  21. 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?

  22. 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.

  23. 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?

  24. 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 a comparable company valuation but maybe also see how much money the company has spent on R&D historically.

  25. 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.

  26. 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

  27. 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?

  28. 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.

  29. 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,,,

  30. 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).

  31. 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 ????

  32. 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!

  33. 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.

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

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

  36. 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.

  37. 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.

  38. 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

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

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

    D S Prasad
    Faculty(Finance)
    IBS,Hyderabad
    India

  41. 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 ?

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *