How to Build a Basic Discounted Cash Flow Model

One of our readers kindly asked us for a simple discounted cash flow model and of course we are going to deliver.

Below is the output of a basic DCF template lets take a look:

Cost of Capital:

  1. Enter in your BETA (not this kind of beta) by scurrying over to Bloomberg
  2. Add in your Risk premium, Risk Free, Cost of debt and Tax rates

Summary Valuation:

  1. Enter in your Cash on hand, debt numbers and diluted shares outstanding

Terminal Value:

  1. Calculate a long-term growth rate (usually linked to GDP)
  2. Enter in your percentage debt and equity

1

 

 

 

 

 

 

 

 

 

 

Free Cash Flow:

  1. Code in your EBIT or link this to your three statement working model
  2. Run your CAPEX,, working capital (off built balance sheet) and D&A numbers through

2

 

 

 

 

 

 

Results:

  1. The sensitivity table runs off of your WACC in your assumptions tab
  2. You run a multiple to spot check your analysis, notably these numbers are not real so they seem quit high

3

 

 

 

 

 

 

That’s really it guys. Remember to keep it simple.

 

Comments

  1. DCF says

    Thanks so much guys the model is simple and clean.

    I’ll pass along the info you’re doing great work over here.

  2. Manny says

    Hey, awesome stuff and I’m a big fan of the blog. For the past week I’ve been reading a bunch of the personal finance and finance career stuff here, and I’m not even close to done lol.

    Do you guys think you could do a post on software to learn for financial industry jobs– IB, Research/Investor Relations, S&T? I know previously you said for IB/Research an Excel course would be a good idea.

    • Wall Street Playboys says

      Glad you are enjoying the site.

      If you want to be good in IB/Research/Equity sales and trading here is the software you should maximize.

      Microsoft office.

      If you really want to be a go getter.

      Bloomberg, CapitalIQ, FactSet, Thomson

      Purchasing courses on how to “model” is a colossal waste of money, you will have templates at work and we will continue to expand our modeling information and explanations as the site continues to grow.

      Believe this answers your question if not let us know.

  3. James says

    Thanks for the help, this is great. One question.
    When calculating terminal value, did you use 7.2% and 3% for Discount and Growth rate respectively? I am trying to get to your $52,805 answer with those numbers and can’t seem to hit it…
    Heres my math in case you see a glaring error:
    ($2242 * 1.03)/(.072-.03) = $54982…

    Sorry if this a mistake on my part, I am new to DCF’s as of today.

    • Klyde Hughes says

      James you’re correct.
      Terminal Value: FCF of terminal period(1+perpetuity growth rate)/(WACC-perpetuity growth rate)
      2242(1+.03)/(.072-.03)=54,982
      Furthermore, PV Terminal=Terminal Value X Discount Factor
      =36,228
      If OP is using a different calculation, it would be appreciated if he could attach his template in an excel file.

      • Klyde Hughes says

        WACC would have to be 7.34% in order to get OP’s terminal value. I tried to see if he used the averages off the sensitivity analysis of WACC but that comes out to be 7.45%.

    • Wall Street Playboys says

      James,

      The 7.2 has decimals you’re looking at rounding errors. Your math is correct.

      Terminal value = future/(k-g)

      In the sheet the exact numbers for reference

      $2241.570009375/(7.245%-3.0000%)
      = $52,804.9
      = $53,805.

      The numbers were real (ie: that is why there are rounding errors).

      You don’t need to add another 1.03 to the number becuase the $2,242 number already reflects the out year.

      Look at FYE+4 = $2,176
      therefore the term is 1.03*2,176 = $2.242.

      —-

      In short rounding errors.

  4. Klyde Hughes says

    I am assuming you’re using the perpetuity growth method.

    Why the FYE+4 part since you’re supposed to use the unlevered CF from the last projection year, i.e. $2241.570009375? Feel free to send your template to my email.

    Thanks for the quick response.

    [mod note: Do not put your email in the comments. Also we do not send out our excel templates. Finally, Re-read the above you use year 5. good luck!]

    • Klyde Hughes says

      Okay then, lets just take PV of year end 5 cash flows and then add it to PV Terminal Value Year 4.
      Or better yet, might as well just add PV of year end 4 cash flows to PV Terminal Value Year 4…

  5. Wall Street Playboys says

    Ahhh, understand the discrepancy. It is between how things are done on the job and how you do them in academia.

    Your last presented number is the term number. 9/10 times on the job. Keep it simple!

    To clarify, in an interview you write it out in full
    Term = 1+g * last year/ (k-g)

    However… In the work force, your last presented year is generally just used as the term value. As you can see it does not move the valuation much ~2%. So you simply say “last year’s cash flow is assumed in perpetuity”.

    That should clarify it all. Doesn’t make sense unless you have work experience.

Leave a Reply

Your email address will not be published.