Average Market PER

[bullmarket]

Morning everyone

I've seen a variety of different average market PER's quoted from various sources ranging from about 15 - 18 or so.

I don't know how each of the quoted numbers are calculated...ie..whether they are based on prospective or historical EPS and/or whether they are weighted in any way...ie..according to market cap, index weighting or whatever.

Although I look at avge market PER's as only a ball park guide to whether the market is cheap or not I would prefer to be looking at a weighted, prospective market PER to help form my opinion.

So I have decided to calculate my own "average market PER" as follows:

1) I downloaded the constituents of the S&P ASX50 into an Excel spreadsheet from www.standardandpoors.com I'm adopting the KISS principle atm so only using ASX50 stocks for now.

2) I got the forcast 2006 EPS and ASX50 % weightings from Commsec research (I hope they're correct )

3) I then calculated the simple arithmetic and weighted (according to the company ASX50 index weights) average PER for the ASX50 index. The share prices are from the S&P site and look to be last Thursday's closing prices.

The data I used and results are in the attached (I hope ) spreadsheet. I've never tried to upload a spreadsheet so I hope it works. If it or a link to it doesn't appear below in this post I'll try to find another way of posting it.

In case the spreadsheet hasn't uploaded, the unweighted prospective 2006 average ASX50 PER = 16.0. The weighted prospective 2006 average ASX50 PER = 15.4

If anyone wants to download the spreadsheet to build on it, add more companies or whatever, then please do so. This spreadsheet is just a starting point atm.

If anyone has any suggestions on how to improve it, I'm all ears .

I'm using Excel 97 so later versions should open the spreadsheet.

cheers

bullmarket

 

[TheAnalyst]

Thank you Bullmarket and i appreciate your work and effort.

 

[nizar]

Morning everyone

I've seen a variety of different average market PER's quoted from various sources ranging from about 15 - 18 or so.

I don't know how each of the quoted numbers are calculated...ie..whether they are based on prospective or historical EPS and/or whether they are weighted in any way...ie..according to market cap, index weighting or whatever.

Although I look at avge market PER's as only a ball park guide to whether the market is cheap or not I would prefer to be looking at a weighted, prospective market PER to help form my opinion.

So I have decided to calculate my own "average market PER" as follows:

1) I downloaded the constituents of the S&P ASX50 into an Excel spreadsheet from www.standardandpoors.com I'm adopting the KISS principle atm so only using ASX50 stocks for now.

2) I got the forcast 2006 EPS and ASX50 % weightings from Commsec research (I hope they're correct )

3) I then calculated the simple arithmetic and weighted (according to the company ASX50 index weights) average PER for the ASX50 index. The share prices are from the S&P site and look to be last Thursday's closing prices.

The data I used and results are in the attached (I hope ) spreadsheet. I've never tried to upload a spreadsheet so I hope it works. If it or a link to it doesn't appear below in this post I'll try to find another way of posting it.

In case the spreadsheet hasn't uploaded, the unweighted prospective 2006 average ASX50 PER = 16.0. The weighted prospective 2006 average ASX50 PER = 15.4

If anyone wants to download the spreadsheet to build on it, add more companies or whatever, then please do so. This spreadsheet is just a starting point atm.

If anyone has any suggestions on how to improve it, I'm all ears .

I'm using Excel 97 so later versions should open the spreadsheet.

cheers

bullmarket

sorry 2 tell u this now but did u know that AFR has market PER updated weekly in the print version after market wrap...?

 

[michael_selway]

Hi on Comsec

All Ords average PER = 16.27

It use 2005 EPS and 2006 fore EPS weighted by time for each stock. This very accurate prediction

16.27 is a little high. IMO it should be under 15, 14-13 is more realistic

1/PER = rate of return = 1/16.27 = 6.14%pa. But if u put it in the bank its risk free and u can get 5-6%pa (although thats taxed).

So there should be some premium, thus PER of 13-14 is more "safer"

http://www.aireview.com/index.php?act=view&catid=8&id=3456

 

[Smurf1976]

If you're using forecast earnings then presumably you would need to adjust the "7 is cheap, 14 is fair, 28 is a bubble" rule of thumb downwards since it's based on trailing earnings not forecast. Not sure by how much you would adjust it though. Just don't make the mistake of using forecast earnings and applying that to a guide based on historic earnings and thus concluding that it's fair value. Need the right input for the model being used...

Stocks don't look cheap but they're a lot cheaper than some other asset classes at the moment. For example real estate with a p/e around 40 in some parts of the country and over 20 virtually everywhere (cities) versus a fair value p/e around 14 and anything over 20 considered over valued. Bonds p/e is likewise pretty high at the moment.

 

[bullmarket]

Morning everyone

Reading through earlier posts, they seem to confirm there are numerous ways different sources calculate PER averages and many ways diiferent people use and interpret them.

There used to be a ball park rule of thumb that said fair PER = 20 minus the inflation rate. So if inflation is about 3% atm then fair PER = ~17 atm. But there are obviously different ways to interpret a fair PER.

Anyway, I've updated my earlier 'prototype' spreadsheet that calculates average PER's. (see attachment if interested)

I've created a 'Data' sheet within the spreadsheet into which you copy and paste share price data, whatever EPS data you want to use and market capitalization data (for weighting purposes).

I got all my data from:
http://www.standardandpoors.com (sector/index constituents and share prices)
and
http://investor.ninemsn.com.au/investor/shares/finder/default.asp (forcast EPS, market caps - registration is free)

Both of the above sites allow your search results to be downloaded directly into a spreadsheet and/or a comma delimited txt or csv file so there is no need to manually enter any data.

You can use the spreadsheet to calculate the avge PER for whatever index/sector you like as well as your own personal indices or porfolio holdings.

To calculate an avge PER for an index/sector please follow the steps below:

1) Create a new sheet by copying one of the 2 already existing sheets in order to retain the formulae that do all the number crunching.

2) Delete all the company codes in col A.
Don't touch any cells in col's B-H as they contain formulae which do all the number crunching.

3) Insert more rows in the table if you need them to accomodate the numbers of codes in your index/sector.

4) Copy the formulae in cols B-H from one of the old rows to any new rows inserted.

5) Delete any extra unused rows in your new table.

6) Enter manually or copy and paste the company codes of which you want to calculate the avge PER in Col A of your new table.

Note: To ensure the vlookup functions in the formulae work correctly (all transparent to the user) make sure there are no leading or trailing blank spaces in your company codes in col A. If in doubt, use Excel's TRIM function to remove leading/trailing blank spaces.

7) Copy and paste share price, EPS and market cap data for the companies in your sector/index into the appropriate tables in the 'Data' sheet. Unused blank rows in these tables are ok.

Again, to ensure the vlookup tables work ok ensure there are no leading/trailing blank spaces in the company codes (as above) in the 'Data' sheet tables.

If anyone is interested, feel free to download the attached spreadsheet and use/modify it to suit your needs. The methods I used in the spreadsheet suit my needs. I'll soon add the ASX200 and ASX300 indices to the spreadsheet. I've had to put the spreadsheet into a WinZip file to meet the file size limit for xls files for this forum. The unzipped xls file is 556kb.

If you have any problems, just post them here and I'll try to help.

cheers

bullmarket

 

[bullmarket]

Morning everyone

I've finished 'playing' with my PER's spreadsheet

I've added the ASX200 and ASX300 indexes and some error checking (by using sumif, countif functions). So if your data source has missing EPS or market cap data for any stocks, then those stocks will be ignored in the average PER calculations until you find the missing data from whatever source and update the 'Data' sheet.

I've also added a worksheet called 'Charts' which displays the various unweighted and weighted PER's from your indexes on a chart and so makes it a lot easier to see how the various index PER's compare with each other.

Anyway, the attached spreadsheet (in a ZIP file) is set up for my needs and I now know exactly which stocks are used in each index and what data and method is used to calculate PER. So if interested, feel free to download it, use it, hack it, butcher it or whatever you like to it to suit your needs. Instructions on how to add more indexes is in an earler post in this thread. If anyone has any problems with it please post them in this thread and I'll try to help.

cheers

bullmarket

 

[RichKid]

This graph is extracted from this article, a graph of avg p/e's. Have I mentioned that I love charts!!??: http://www.asx.com.au/resources/new.../20060314_aussie_market_in_bubble_wiseowl.htm (I've also posted the link in the 'General Market outlook 2006' thread).

 

[bullmarket]

Hi RichKid

Interesting chart but I'd be interested in finding out which EPS numbers they used....ie...last full year EPS, forcast EPS or even a combination of both.

S&P had their quarterly index adjustments last Friday but I saw on the weekend they hadn't updated their index constituents lists yet so I will check again in a few days so that I can download them and update my Market PER spreadsheet I posted earlier in this thread.

cheers

bullmarket

ps............will be cheering on Craig Mottram tonight :aus:

 

[bullmarket]

Morning everyone

For anyone interested I have attached an updated Average Market PER's spreadsheet after S&P's quarterly index adjustments last Friday.......so if it is of any use to anyone feel free to download it and play with it or modify it to suit.

The PER's are based on 2006 forcast EPS and the prices are about 2 days old.

The data sources I used are the same as described in my earlier posts in this thread.

For those who do not have Microsoft Excel I have also attached a screen dump of the spreadsheet's summary charts.

cheers

bullmarket