|
| quote: | Originally posted by Krypton
I starting a spreadsheet in which I will be able to value stocks based on 4 different fundamental ratios. They are the Price/Equity ratio, Price/Sales ratio, PEG ratio, and the Price/Book ratio.
It took me about an hour to write out 4 essential formulas using these metrics to determine a stock price valuation. What the spreadsheet will do is place a current valuation on the stock, and also place a valuation for a time period in the future.
---
The formulas are as follows...
Q = Stock Price
---
1. PE Valuation
(PE)(EPS) = Stock Price or Q
---
2. PS Valuation
(PS)(Sales! per Share) = Stock Price or Q
!Sales and revenue mean the same thing.
---
3. PEG Valuation
Q = BEG; where Q = Stock Price, B = PEG ratio, E = EPS, G = estimated EPS growth rate by %
---
4. PB Valuation
Q = [(PB)(X)] / A; where Q = Stock Price, PB = Price-to-Book, X = Shareholders Equity, A = Shares Outstanding.
-----------------------------
I'm going to develop this spreadsheet so by using metrics from the past 10 years, based on the formulas above, I will come out with a very accurate respresentation of what a companies stock is really worth. Each formula will have its own estimated stock price, but I'm going to average them all out to get a final stock price estimate. Currently, my valuations only use the PS ratio. Now that I've learned a lot more about the other metrics, I can incorporate them into my calculations. God, I love math.
Doing the valuations will be a bit quantitatively intensive with all the number crunching, but if you want to appraise a stock for its value, my method will be a pretty straight forward way of doing it. Once I make the spreadsheet, just follow my instructions, and it can be done by anyone who knows how to use excel.
I'm also going to continue looking for metrics I can turn into formulas to add to my spreadsheet calculations. I may add dividend and payout ratios and corresponding formulas for them tomorrow. |
BAM, the finished product.

The 6th formula was the 'Dividend Yield Valuation' formula and went like this...
Stock price = Dividend Rate / Dividend Yeild
This is only for dividend paying stocks only though.
I'm looking at other valuation metrics like price-to-cash flow but those are a bit harder to find.
With this spreadsheet, you enter all the metrics for each stock into each box . Each box is labelled so you know what information to put in. This baby uses 6 different formulas plus numerous algorithms to come up with one accurately estimated stock price. This is how an investor finds out if the price he is paying is high or low. If the estimate is more then 10% above the current trading price, then you have found yourself a good bargain and should buy. This spreadsheet requires 70 different metrics, but all of them can be retrieved through MSN's moneycentral. It's numbers crunching to the max, but this is what securities analysis is all about.
___________________
Last edited by Krypton on Sep-01-2007 at 05:23
|