Apologies to everyone for not keeping the valuation data current. It is a fairly time consuming process and one that I have decided is not the best use of my time.
Here is a description of the methodology I used to make the calculations. Maybe someone else will be willing to take the time to follow the steps, or a group of people may want to team together to share the workload. Also, any comments or suggestions or notification of error would be interesting.
******************************************************
Here are the sources for info that I used to calculate NDX 100 valuation metrics.
1) First you need to know the components of the index and their relative weightings:
dynamic.nasdaq.com
2) For each stock, you need to know the last 4 quarters (actual) and next four quarters (estimated) earnings. Here's the page for MSFT:
ec.thomsonfn.com
3) Get quarterly past revenues from here:
biz.yahoo.com
This page is not always up to date so you may have to refer to quarterly reports to fill in any gaps.
4) Get future quarterly revenue estimates from here:
biz.yahoo.com
There are usually only estimates available for the next quarter and for the next whole year. So you need to look at past revenue seasonality and extrapolate/estimate the revenue for 3 and 4 quarters ahead.
5) Get market cap from here:
finance.yahoo.com
OK, that is the tedious data gathering part. You still need to know stock prices of course. You can get those manually from whatever quote service you use. I used an eSignal feed straight into MS Excel to save a lot of time and guard against typos.
Now that you have the data, for each stock you work out the revs and earnings for the past 4 quarters, and for the next 4 (including the current one yet to be reported).
For each stock you make 6 multiplications:
A) weighting x price
B) weighting x past 4Q earns
C) weighting x past 4Q revs
D) weighting x future 4Q earns
E) weighting x future 4Q rev
F) weighting x market cap
Note: for earnings use per share amounts. For revs use total revs. This is purely because this is how the data are most easily found.
So for each stock you now have 6 valuation metrics A, B, C, D, E & F
Once you have done this you can work out the valuaton for the whole index as follows:
To work out the current P/E, you add all the "A" values and divide by the sum of all the "B" values.
To work out the current P/S, you add all the "F" values and divide by the sum of all the "C" values.
To work out the forward P/E, you add all the "A" values and divide by the sum of all the "D" values.
To work out the forward P/S, you add all the "F" values and divide by the sum of all the "E" values.
Have fun! |
