Posts Tagged ‘spreadsheet’

Learn how to invest, read stock analysis, and find stock picks

Benjamin Graham Formula Free Stock Valuation Spreadsheet

February 26th, 2008 by Jae Jun

Jae Jun

If you haven’t read The Intelligent Investor, you are missing out on timeless advice. One of which is to buy at a great margin of safety. I won’t be going through the details of the book but an explanation and how to is explained in the article titled “How to value a stock with Graham Formula“.

Instead, I’ve applied Benjamin Graham’s formula to a free stock valuation spreadsheet that allows anyone to quickly value the fair value of a company (quantitatively). There are sites that already do this but I wanted something where I could do it on my own for any company. (download link is down the bottom)

A quick quote to start things off.

Confronted with a like challenge to distill the secret of sound investment into three words, we venture the following motto, Margin of Safety. – Benjamin Graham

Benjamin Graham Formula Overview

Ben Graham’s formula is as follows:

Intrinsic Value = “normal” earnings x (8.5 + (2 x expected 5 yr growth)) x (4.4/20yr AA corp bond)

- Normal earnings refer to earnings over a period of years. Not just the previous year.
– 8.5 is the PE of a company with no growth.
– In the spreadsheet, growth rate is user defined. Check out a method to determine growth rate.
– Back when Graham wrote the book, he was using a 20 yr AAA corp bond rate of 4.4%. To apply the formula today, we need to normalize it to todays rate. I’ve put the 20yr AA corp bond rate as the denominator since the AA rate is slightly higher than the AAA and will give a slightly conservative number.

However, I use a very slight modification to this formula which I detail in an article I wrote titled “How to Value a Stock with the Ben Graham Formula”.

How To Use The Spreadsheet

I’ve tried to make it as user friendly and eye pleasing as possible. In order to get it working though, you MUST install the plugin for excel which is described below. The plugin allows excel to automatically retrieve all financial statements and prices that I use in the spreadsheet.

Some Explanations

A difficulty I had was to figure out how to come up with a reasonable future EPS guide. I know I’ve said I don’t like using EPS as a guide and I still stick to that. However, I wanted to see how the Graham formula worked and what type of valuation it revealed.

Here is how I calculated the future EPS. Note, I am a conservative guy. If you feel, the ranges are incorrect, let me know or try changing some things yourself and if it works better, let me know.

  1. For the 1st future year, I took the constant at which the EPS had linearly increased over 10 years
  2. I added the constant to the average increase of EPS throughout the past 10 years
  3. I then added an additional “growth sum” to the number I get from step 2
  4. For the 2nd future year, I took the constant
  5. Added it to the 1st future year
  6. Added the “growth sum”
  7. And so on

Other Points

Since this stock valuation calculator is free, if you enjoy this spreadsheet, consider buying the best stock valuation spreadsheet available on the internet.

IMPORTANT!

Please read the installation guide and FAQ.

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.

How to Download

The download has now moved. To download the spreadsheet, you must go to the main page of Old School Value and sign up with your email to receive a list of free spreadsheets.

The list includes nine free spreadsheets, the add-in required to download data from the internet as well as all the installation help material.

Premium Spreadsheets

Feel free to check out the free version and then when ready, go to the stock valuation software page and review what you will get with the premium.

The premium version includes several valuation models as well as fundamental analysis data, historical data, charts and competitor comparison features. Just by entering one ticker, you can immediately get all that information on your favorite stock which will save you hours in your analysis.

Go now and see what you get.

Benjamin Graham Spreadsheet Screenshot

 


Tags: graham formula, intrinsic value, spreadsheet
Posted in Investment Tools | 45 Comments »

Investment Portfolio Spreadsheet

February 12th, 2008 by Jae Jun

Written by

Jae Jun

[edit] Newer Version Now Available

There is now an even better version available. A brand new and updated version of the best stock portfolio tracking spreadsheet. Go check it out now.

Continue on with the Original Article..

For an investor, keeping track of personal performance and gains is important. Many people believe they know their return rate in their head, but surprisingly, this is always overestimated. Our actual return usually come out a few % lower and these few percentages can add up to ten of thousands. That is why a investment portfolio spreadsheet is important to track performance.

Tracking Your Performance

Tracking performance also allows an investor to be honest with themselves. As human beings, we tend to dismiss the failures much more often than when we try to acknowledge and learn from them. Everyone has made investment mistakes and it is vital to know what went wrong and then to never redo those mistakes. I believe that monitoring your portfolio in a spreadsheet or other form reminds you of those mistakes.

The Original Excel Portfolio Spreadsheet

I was sick of all the portfolio managing programs that you had to buy and install etc, so I made my own. But one day, I came across a great blog titled ‘Experiments in Finance’ where the author had created a great Portfolio Performance vs S&P 500 spreadsheet. Please visit the blog to get the original file and see the instructions.

Automated Excel Portfolio Spreadsheet

Now if you’re like me and can’t be bothered looking up the latest prices and then recording them manually, a great plugin for excel, written by Randy, is available at Randy’s Yahoo Group. You can find more info by going to ‘Files > Documentation’ section on the Yahoo group. For your convenience, I’ve made a link so you can download it right here.

Utilizing the excel add-in, the latest and historical stock prices for all tickers can be retrieved. You just add the stock symbol, no. of shares bought, purchase price and date of purchase and the rest should be updated.

Spreadsheet Installation Instructions

The instructions are pretty simple, download the zip file containing the add-in and its supplementary files and create a folder called “SMF” directly under C: and then extract all of the files into the “C:\SMF\” folder on your computer.

Then open Excel, make sure a blank workbook is open, choose the menu Tools->Add-Ins, in the dialog box which opens choose Browse and choose the folder “C:\SMF\” and then choose the file “RCH_Stock_Market_Functions.xla” and click OK.

edit: follow the link to view the full and comprehensive installation manual and FAQ.

Few Quick Comments

The #DIV/0! error you see is nothing. Just ignore it. It is the excel function spitting an error because there are no values available for an empty cell.

Feel free to modify it and share it.
If you are having trouble let me know and I will try to help.

Download Section

Randy’s SMF Add-in
Original Portfolio Performance vs S&P 500 spreadsheet & Instructions
Automated Portfolio Spreadsheet – Old School Value Version

How to Install

edit: (view the installation guide linked above)

You have to unzip the file into c:/SMF
If you put it in any other folder it wont work.

For Excel 2003:

  1. On the Tools menu, click Add-Ins
  2. Click the Browse button and navigate to the smf add ins folder
  3. Select the add in file that you see and then click OK
  4. Click Yes to any file copy or overwrite prompts
  5. Verify the Stock Market Function add in box is checked
  6. Press OK

For Excel 2007:

  1. Click the orb and then click on excel options
  2. Click Add in
  3. Down the bottom there is a drop down list and next to it there is a move button (I think). Click on the button
  4. Navigate to the SMF add in folders
  5. select the file and then click OK
  6. Verify the Stock Market Function add in box is checked
  7. Press OK

Open the spreadsheet, enter and ticker and you will see a “calculating xx%” in the bottom right corner. You are good to go.

Note: if you download the file, copy it to a USB and then copy it back to another folder or computer, MAKE SURE that the data in the “Statements” tab is pointing to c:/SMF and not your USB drive.

——————————————————————————–

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.

Tags: Portfolio, spreadsheet
Posted in Investment Tools | 33 Comments »

Newer Entries »