Free 5YR DCF Fair Value Spreadsheet


Written by

Jae Jun

follow me on

Facebook

Twitter

EDIT: This is now an outdated article.

9 free fair value spreadsheets are available for you to download if you sign up to this blog with your email address.

The email form is right at the bottom of the page. Enter your email and receive the free spreadsheet downloads.

The DCF Fair Value Spreadsheet

Although finding great, stable companies with more than 10 years of historical data to examine is ideal, not all companies fall into this category. A vast majority of companies fall into the category of around 5 years of operational history. This free investment spreadsheet which is a modification of the free 10 year investment spreadsheet calculates the fair value of a company based on the past 5 years of available data from Morningstar and other sources. I’ll also take you through the steps to modify the spreadsheet yourself so that you can apply it to other databases other than Morningstar.

Changes to the Investing Spreadsheet

  • Multi yearperformance is calculated based on timeframes over the past 5 years.
  • Modified the forecasted EPS formula in the Benjamin Graham formula.

How To Fix The #DIV/0! Error

For companies such as DLB with 4 years of data instead of the required 5, you will get #DIV/0! errors because Excel is trying to calculate the median with invalid numbers.

DIV error

To overcome this, you simply have to restate the cell ranges by double clicking and then changing the range from =MEDIAN(B41:J41) to where there are actual values. In the case of DLB, this would be =MEDIAN(F41,I41;J41).

Median Edit

The screenshot shows the edit for the third row, but you would want to do it for the first row and then just grab the corner and drag it down to apply it to all cells.

Median edit 2

You can actually apply this method for the 10yr intrinsic value spreadsheet instead of downloading this one.

How To Customize The Spreadsheet

The stock investment spreadsheet utilizes an excel add-in called SMF (Stock Market Function) and in order to really understand how it works, you would have to follow the code and the comments within, but I’ll go over the main function.

(If you want further spreadsheets and templates with this add-in, visit the Yahoo page and navigate to the files section)

In the spreadsheets, the data in the statements tab is retrieved from Morningstar with the =RCHGetTableCell() function. Simply, this grabs the value from the cell of the table.

Currently the formula in the spreadsheet is: =RCHGetTableCell(“http://quicktake.morningstar.com/Stock/ Income10.asp?Symbol=”&Ticker, 2, “Fiscal Year-End:”, “>Revenue”)

To grab data from another site such as Yahoo Finance Australia for Telstra, I could go to the balance sheet page and then edit the function to display the cash by entering the following

=RCHGetTableCell(“http://au.finance.yahoo.com/q/abs?s=”&Ticker, 1, “PERIOD ENDING”, “>Cash”) where the ticker entered into the spreadsheet would be TLS.AX.

This command does the following:

  • points to the page http://au.finance.yahoo.com/q/abs?s=TLS.AX
  • selects the table that contains the exact phrase “PERIOD ENDING”
  • selects the very first cell right after the cell containing the exact phrase “CASH” which is characterised by the > sign.

To retrieve the next value from the table, you would simply change the 1 to 2 to get the second cell.

Do this method for all rows and cells. I just found out it was rharmelink from the SMF Group that created the 10 year statement template for Morningstar. (Just giving credit to the proper person)

To retrieve other information such as stock prices, historical prices, charts etc, you would use the other functions mentioned in the documentation.

I could go over the other useful functions but I think it’s pretty easy to figure out and will leave it up to you unless a number of readers request it specifically.

How to Install

A step by step guide is provided in the full spreadsheet installation guide.

IMPORTANT

Please read the guide and FAQ section. To date, I’ve been spending hours helping people with simple excel issues on a free product rather than anything spreadsheet related.

So for all excel and install problems, place all questions in the comment sections below. That way I won’t have to answer the same question again and again.

  • I discovered your homepage by coincidence.
    Very interesting posts and well written.
    I will put your site on my blogroll.
    🙂

  • Thank you for coming and commenting Susan. I hope I can keep the posts coming.

  • james

    Hi, wanted to thank you for a great site and for sharing. The 5 year speadsheet worked great for me last Friday, but now I am getting nothing but error values? Can you help? Thanks again

  • Hi James,

    Can you provide more detail about the error? I know that sometimes, excel doesnt always seem to update properly so I would restart excel or just try pointing to the add-in again.

    If you copied from one computer to another, try checking that the formula in the cells read =rchgettable(…) instead of =C:\programfiles\smf-add!RCHGETtable(…)

    If you need more information, send me an email with some screenshots or something and I’ll take a look.

  • james

    Hi, me again, it’s working again,thanks so much. your website is one of the best

  • James,
    Glad it’s working. Mind sharing what the issue was? I’m sure it will help somebody else one day.

  • james

    Hi. I wish I knew more to share, I am not very computer literate, I assume it was something to due with bringing in the morningstar data as the error values were thru out the file, sorry to not be more help.

  • Actually I know exactly to where you are referring to. Morningstar has some errors in their databases and doesnt display the statements properly even in a web browser. If this is the case, you just have to wait till Morningstar fix it.

    Your comment was a lot of help. One troubleshooting question resolved 🙂

  • Hey Jae, I appreciate your response on some of my post. On this sheet could you explain what the discount % on the DCF Valuation tab is? What is its purpose and can you give me some advice on how to determine what the number should be?

  • Jae, disregard my previous post. I read the “Buying Johnson & Johnson” example on F Wall Street and I think I understand. The discount % is what you expect as a return over the estimated projection time of your calculation. In other words, it is the % return used to determine the NPV of a companies cash flow which in turn is used to estimate your target purchase price. Correct?

  • Exactly. Ive also written specifically about discount rates and why I dont believe in using WACC and other stuff they teach you in finance school. Discount rate post link.

  • Jae Jun,

    I re-read this post but still cannot figure out how to use the “RCHGetElementNumber(Ticker,25)” function to get the ticker price for a stock I am interested in. I am trying to get a spreadsheet to auto-populate the current stock prices on my sheet. Can you help?

  • If you have the cell you type the ticker in labeled as “ticker”, the RCHGetElement should work. Let me know which company you are trying to get it for and maybe I could help.

  • Jim

    Jae,

    I haven’t noticed if anyone else has brought this up already but I wanted to make you aware of it concerning your 5 year DCF spreadsheet. The growth rate projection is suppose to use the CROIC number, Cell K33. Instead your spreadsheet is using Cell K32 which is the free cash flow percentage. So, all your extremely high Intrinsic Values are coming from the FCF percentage rather than the CROIC that should be used. I haven’t looked at your 10 year yet but i’m guessing its the same. Just wanted to point that out to you. I’ve made the adjustment already. btw, your spreadsheet is put together nicely. The one I made is very comparable but it doesn’t look as pretty 🙂

  • oh I must have forgotten to upload the changes I made. I first had the growth rate set to FCF, then changed it to CROIC because I believe that over the long run, the cash of the business will grow at the rate of its CROIC, but then I reverted back to FCF.

    Thanks for letting me know. I’ll have to update it.

  • Jim

    Yes, you did it to the 10 year too. I also believe that CROIC is the correct calculation in determining growth using the DCF model.

  • I decided against CROIC because it was too bullish.
    FCF can’t grow at a rate faster than its CROIC therefore the CROIC should be considered as the upper limit.

    In the case f CPY, its CROIC is 7.6% after smoothing the data over 10 years, but its FCF growth is 0% or negative.

    The growth of 0% or below may be unrealistic, but it seems like 7.6% is also being a little unrealistic. So I reverted back to the conservative side of FCF. Obviously I make my adjustments if FCF is too high as well.

  • Jim

    But in another company’s analysis (can’t remember which one off the top of my head, i’ve done over 20 today), FCF was 120% when CROIC was sitting at 29%. So, it works both ways and that’s another reason I use EPV as my ‘goto’ valuation. Still like the way you put together your DCF though. It really looks great.

  • FCF at 120%? wow. It’s pretty clear that you may have to use another method.
    Since I do believe that over the long run, the market is efficient, I use a growth rate which calculates the past intrinsic values which also fits the trend of the historical prices in my spreadsheet. I then take into account what the company plans to do in the future and the growth it is expecting. It’s worked fairly accurately so far.

    I’ve looked into EPV slightly over the weekend but I’ll do some more study and try to add it my bag of tricks.

  • Jim

    Hey Jae, was going over your DCF and wanted to point a specific out that maybe you could help me with. My understanding of FCF is that it is defined as Net Income + Amortization/Depreciation – Changes in Working Capital – Capital Expenditures. Invested Capital is Shareholder Equity + Total Liabilities – Current Liabilities. CROIC is FCF / Invested Capital.

    On your 5 year DCF (probably 10 year as well but I didn’t look yet) your FCF is being computed as Net Income + Amortization/Depreciation + Deferred Taxes + Non-Cash Items – Changes in Working Capital – Capital Expenditures.

    The difference between the two calculations as you can see is HUGE. Which do you believe is the correct way to define FCF?

    My second question is this: Since value investors typically deduct intangibles & goodwill from total assets when valuing a business, wouldn’t it make sense to also deduct them when coming up with the Invested Capital figure which would also significantly, in many cases, change CROIC?

    Thanks. Hope everything is going well for you and look forward to hearing from you.

  • Jim,

    I was looking at what you said over the weekend but I couldn’t see how you came up with that formula. In the spreadsheet, I take Morningstar values which is just the normal FCF formula of cash from operations – capex.

    Regarding taking out intangibles from the equation… another reader on F Wall Street mentioned the same thing previously, but I’m not quite sure enough to give an answer right now.

  • Jim

    I posed that question to Joe on fwallstreet. For intangibles and goodwill, I think it comes down to how conservative you want to take the valuation. As for as Morningstars FCF, it is calculated as Net Income + Amortization/Depreciation + Deferred Taxes + Non-Cash Items – Changes in Working Capital – Capital Expenditures. Not as Net Income + Amortization/Depreciation – Changes in Working Capital – Capital Expenditures which to my understanding is the definition of Free Cash Flow.

    Morningstar adds Deferred Taxes & Non-Cash Items in their FCF. Wasn’t sure if you knew that and wanted to bring it to your attention since Warren Buffett’s definition of Owner Earnings doesn’t include those items.

  • Jim

    Warren’s ‘Owner Earnings’ equation is very similar to FCF yet different. Here is a quote from him explaining his technique.

    “These represent (a) reported earnings plus (b) depreciation, depletion, amortization, and certain other non-cash charges…less (c) the average annual amount of capitalized expenditures for plant and equipment, etc. that the business requires to fully maintain its long-term competitive position and its unit volume….Our owner-earnings equation does not yield the deceptively precise figures provided by GAAP, since (c) must be a guess – and one sometimes very difficult to make. Despite this problem, we consider the owner earnings figure, not the GAAP figure, to be the relevant item for valuation purposes…All of this points up the absurdity of the ‘cash flow’ numbers that are often set forth in Wall Street reports. These numbers routinely include (a) plus (b) – but do not subtract (c).”

  • Thanks for sharing that with us. Yes I was aware Morningstar added back taxes and non cash items as they use the standard formula which is as you mentioned.

    I don’t believe this is that “huge” of a difference. After all, the whole point of DCF is normalize.

    Rather than normalizing over 5 straight years, I think what Joe did by using timelines is brilliant.

  • Jim

    Morning Jae, In continuation you mention that adding back taxes and non cash items were the ‘standard’. I always heard that the standard was to not include those items. That’s why i was asking. Have a great day.

  • hi jae,

    thank you for your wonderful website…very informative and really shows a dedication to rational valuation and educating your fellow investor…don’t know what your latest thinking on the subject is, but just wanted to get your thoughts on ponzio…actually, the more i read of the fwallstreet book, the more i wonder about the methods presented therein…specifically, the simple addition of owner equity straight from the balance sheet to the summed discounted future stabilized cash flows…hmmm…this is circular in the sense that the current stock price is utilized to calculate this, despite the fact that the whole valuation exercise is geared toward determining this value independently (and highlights ponzio’s own misunderstanding of price vs value imho)… also his discussion of discount rates is nonsensical if you really read it carefully (whatever happened to wacc?!?!)

    your thoughts?

    thanks, jae!!

  • @ rion t.,
    Thanks for the compliment rion.

    I get asked this question a lot and I’ve thought through it from different angels, dissected and discussed this in length.
    From a modern finance perspective, it will make no sense. From a value investing perspective, it somewhat makes sense. If you think in terms of real businesses, it makes perfect sense.

    I don’t agree that the share price factors in shareholders equity at all. I’m fully convinced that share prices are made up of a good amount of emotion, which is why there are so many companies selling below tangible net net working capital.

    From a business perspective, shareholders equity is what the investors are entitled to at the current point in time. DCF is the “sum of the future cash flow”. I emphasize future because DCF doesn’t consider the current assets at all.

    If I’m starting a business where I spent $1000 for office equipment, electronics and other goods and I am able to generate $100 a year consistently. If somebody offered me $1000 dollars for the business saying that is what the cash flow of the business is worth, the offer would be turned down immediately, because it doesn’t account for all the office equipment that he is getting with the deal.

    Regarding wacc.. I’m not much of a fan of beta so wacc never works for me either.

    Like Joe, most of what I write and say will make no sense unless you are thinking in terms of owning your own business. I always imagine that I own a little corner grocery store and base my decisions and investing rationale and approach like that. And WACC isn’t what a grocery owner will know about. All I know is that my inventory is worth this much now, and I can generate this much cash now and in the future.

  • jae,

    thanks for your response…i apologize for misinterpreting ponzio…you are right…share price does not have anything to do with the owner equity he uses…however, i still have trouble with this…to me, to add what he does use (straight book value OE) to the sum of discounted future expected FCF still doesn’t sit right…assuming for simplicity for the moment the omission of growth value, it is analogous to adding graham+dodd asset value to the graham+dodd EPV…however,EPV already implicitly includes the value of assets, and only differs from the asset value by the amount of the franchise value, if any…even taking his example (pg 104), he selects a hurdle rate for J&J of 9%…however, he will not achieve his 9% IRR if he pays the PV of the future projected FCF *plus* book value OE…it is one thing to come up with a (hopefully) properly adjusted asset value net of debt and use this as a standalone value possibility and contrast this to EPV (non-growing or growing) or DCF value in attempting to isolate the various components of the total value a la graham+dodd, but you can’t mash them together…this is double counting…thus, i still think ponzio’s “buy-and-hold” valuation is simply a flawed interpretation of plain old DCF, imho. anyways, thanks again for your thoughtful reply jae 🙂

Ready to try Old School Value?

TEST DRIVE THE DEMO TODAY
[checklist-collection]
[checklist-collection]