New Discounted Cash Flow Investing Spreadsheet
Previously I put up a Ben Graham formula investment spreadsheet. As with any fair value calculating method, there are shortcomings and disadvantages. Even with this new one that I have uploaded, there are disadvantages but it is logical and reasonable. This spreadsheet utilizes the Discounted Cash Flow method, which I believe to be the most logical method of valuing a business and estimating its fair value.
Valuing a Business
The discounted cash flow fair value calculation is based off FWallStreet’s method. The original spreadsheet can be downloaded from his post on JNJ. The link to download is at the end of the post.
My version is just an enhanced version of the original spreadsheet. The underlying calculations are the same except many tweaks to the formula and variables. But the theory is the same. To fully understand, please read the JNJ post I linked to above.
For more detailed information on why you must value a business before buying any stocks, the four part series is an excellent read and highly educational for any investor.
How to Use
If you have read the AAPL stock analysis post, you would have seen the screen shots of what it looks like. If you have personally used either the Graham investing spreadsheet or the Personal Portfolio spreadsheet you will know how much time you save with all the automatic data retrieval and updating.
Once you have the spreadsheet working, just be sure to enter the ticker ONLY in the yellow highlighted box. Everything starts from there.
View the full spreadsheet installation guide.
Features
Ive enhanced the spreadsheet with the following:
- financial statements from Morningstar.com
- calculating fair value
- graphing the historical price and market fair value together
- includes the Benjamin Graham intrinsic value spreadsheet
- provides basic ratios for competitors
- a checklist of questions regarding the company
- The spreadsheet DOES NOT work with financial companies. Financial statements for financials are complicated to figure out so even Morningstar does not have any data.
Download The Investing Spreadsheets
- Old School Value: Intrinsic Value Spreadsheet
- Randy’s SMF Excel Add-in
- **Spreadsheet installation guide**
- You can also download the other FREE and best premium investing spreadsheets.
——————————————————————————–
If you found this article helpful and would like to receive more information on free investing tools and spreadsheets, as well as business valuations, stock analysis and updates on merger arbitrage, subscribe to the Old School Value RSS or via email.
————————————————
World’s top web hosting companies offers best web hosting services with their dedicated server. You can register or transfer your existing domain names from one dedicated server to another. They look forward to help you with your web site design and domain name registration.
————————————————
[...] Refer to the image below to see the financial performance for KTII from my intrinsic value spreadsheet. [...]
[...] intrinsic value spreadsheet and Graham formula spreadsheet both are based on past years to create a linear progression for the [...]
[...] A step by step guide is provided in the previous posts. Check out the installation section here. [...]
[...] Installation of the SMF-addin is required and the installation instructions can be found here. [...]
Hey Jae,
Awesome job with the blog. It is really great–mostly the spreadsheets–now if only you could help me get them to work–I am having the hardest time getting the add-ins to work. I thought I managed but then I put in a symbol ODP and I got a bunch of #NAME! things to come up on the statements tab of the Valuation Spreadsheet…Please help. Thanks.
Jae,
To add on to Andrei, why dont financial’s work with your sheet?
@Andrei,
It still seems like the function isnt being called properly. Did you unzip the files to the proper folder and then point excel to the file in the folder you unzipped to?
@Luis,
if you take a look at the financial statements for financial companies such as AXP, you’ll notice that the statements are different to the regular companies. Since the template for the statements tab only applies to non-financials, I would have to create another statements tab for financials. But, I won’t be doing that because I dont know how to determine the intrinsic value for banks etc.
Hi Jae,
This spreadsheet is very nice looking and I was excited to see it for JNJ. However, it seems to freeze when I put in another stock symbol to get information. I have a fast internet connection, so I don’t think that’s the issue. Could it be the Morningstar site, or is there something wrong with the sheet, or does it simply take a long time in general?
Thanks
Hi Aurelien,
The spreadsheet freezes because it has to access the Morningstar database and then run the commands on each cell to pull the commands.
Although Morningstar loads quickly in a web browser, the excel sheet has to request the information from Morningstar for every cell. Basically it’s asking the same thing about 200 times.
That’s the cause for the delay. If you look in the bottom right corner, it will tell you the % complete.
To update on my previous post: The sheet is working fine, it was just slower then expected.
Very cool. I’ve already found a company that seems to be quite undervalued and also has a 10% dividend yield. MAS.
Will look more into it tomorrow.
Great. Hope you find heaps of opportunities with it
Hi Jae,
The spreadsheet is suddenly only giving me error’s in the cells. Have you heard anything about Morningstar changing their data display, or any other reason for this?
Thanks,
Aurelien
I too have been getting the same error. I’m not quite sure what the error is but it seems to work again after I restart excel several times or try writing the ticker in both lower and upper case.
I’m running Excel on a Mac - so there is no c:/program files for me to download to.
I put the files in Applications/Microsoft Office 2004/Office/Add-Ins/SMF, but I get a compile error ‘Expected: end of statement’
Can you help?
Spike,
It wont run on Mac. You’d have to run it off a Windows OS.
http://www.oldschoolvalue.com/intrinsic-value-spreadsheets/
[...] Intrinsic Value Spreadsheet — Old School Value [...]
[...] ValuationBenjamin Graham Formula Free Investment SpreadsheetForbes 200 Best Small Companies ProjectFree 10YR DCF Fair Value Investment SpreadsheetForbes Best Small Companies Project: Part 3Analysis Now Includes Spider GraphsFree 5YR DCF Fair [...]
[...] ValuationBenjamin Graham Formula Free Investment SpreadsheetForbes 200 Best Small Companies ProjectFree 10YR DCF Fair Value Investment SpreadsheetAnalysis Now Includes Spider GraphsForbes Best Small Companies Project: Part 3Free 5YR DCF Fair [...]
[...] ValuationBenjamin Graham Formula Free Investment SpreadsheetForbes 200 Best Small Companies ProjectFree 10YR DCF Fair Value Investment SpreadsheetAnalysis Now Includes Spider GraphsFree 5YR DCF Fair Value Investment SpreadsheetForbes Best Small [...]