Example ASX stock screen and valuation

Hey guys,

Today we’re going to run through a couple of examples using my Stock Screener spreadsheet to hone in on some cheap stocks, and then my Stock Valuation spreadsheet to estimate the value of those stocks to us as investors.

At the risk of ruining the surprise, the stocks I value in this post are the following:

  • Metcash Limited (MTS);
  • St George Mining (SGQ); and
  • Crown Resort Limited (CWN).

Check out the demonstration video below to see how the three company’s performed using the Stock Screener and Stock Valuation spreadsheets.

Download the applicable spreadsheets at these links: ASX Stock Screener and Stock Valuation spreadsheet.

To hone in on cheap stocks I use the Price-EPS ratio and Price-Book Value ratio. For the former, I filter out all stocks with a ratio greater than 15. For the latter, I look for stocks with a ratio between 0.6 and 1.5. This screen yields about 150 cheap stocks, some of which worthy of further investigation. I personally like to go for large Market-Cap stocks so that is the next search criteria I use. From there, I wanted to analyse stocks not in the “Real Estate” GICS Industry Sector (personal preference really, large Market-Cap Real Estate stocks tend to dominate the low P/E universe of ASX stocks and I like to choose from the minority).

Once I’d narrowed my stock list down to MTS, SGQ and CWN, I used my Stock Valuation spreadsheet to estimate the value of these companies by calculating their systematic risk – beta, and their cost of equity via the Capital Asset Pricing Model, and finally the sum of their discounted, expected future cash flows via the Dividend Discount Model.

A note I made in the demonstration video which I’ll make again here is that my Stock Valuation spreadsheet will only tell you if the company is presently undervalued or not. There’s a bunch more research and analysis the user/investor should carry out before investing in an undervalued stock. These include the following:

  • Assessing the competency of the managers of the company;
  • Ensuring the company has long term prospects (e.g. a product which will always be in demand);
  • Ascertaining the stability and understandability of the company (look for company’s with ROE figures which consistently exceed 10% with little fluctuation).

If the above sounded familiar, you’re not crazy – I’m not trying to reinvent the wheel here. I believe these are the criteria Warren Buffett uses to select the stocks he invests in.

In addition to the above, you might also like to check the compatibility of your new stock picks with the rest of your portfolio. I’ll be soon to upload a couple of spreadsheets which will do just that. One will estimate the variance of your portfolio, and whether adding a stock adequately reduces the overall portfolio risk. The other is slightly different but more exciting in my opinion, after inputting the ticker symbols of your portfolio and clicking a few buttons, the spreadsheet will calculate and plot your portfolio’s efficient frontier. It will also calculate the optimal weights of your portfolios assets for a given target return by minimising portfolio variance.

As covered in the demonstration video, there are a couple of circumstances where my Stock Valuation spreadsheet is unsuitable for estimating the current value of a company. This was the case for St George Mining (SGQ). According to the Morningstar data available to free members, SGQ had consistently and significantly negative ROE values for the past three years. This in combination with no dividend payments and thus dividend payout ratios of zero over the same time-frame meant the company growth rate could not be calculated and the Dividend Discount Model could not be used.

During the analysis of Crown Resort Limited (CWN) a separate issue was encountered. Given CWN has only be listed on the ASX since December 2007, shortly before the GFC hit, and the sample statistics presented on the “CAPM” sheet are calculated based on common time horizons (i.e. only market data since December 2007 is included), the expected value for the market (ASX200 in this case) is negative for three out of four holding period durations. The negative expected value produces spurious cost-of-equity values when used in the Capital Asset Pricing Model, and thus the results presented on this sheet, in this instance should not be utilised. Fortunately, the results presented in the “CAPM2” sheet are sound. This is because the expected value used here is based on market returns over the life of the index (since the ASX200 began in 1992). This longer time horizon is less affected by the large, negative returns of the market in 2008 and 2009 following the GFC.

As mentioned above in the recommended additional analysis points, stability and understandability of a company is important. This is usually assessed by a fundamental analysis of the company’s financial statements. I presently have a spreadsheet in my portfolio which analyses this information but it is quite convoluted and it requires the user has access to Morningstar’s DatAnalysis service which few individual investors do. I will work on improving this spreadsheet and upload it when its ready.

As usual, let me know if you have any issues or recommendations. Happy to assist where possible.



Stock valuation via DDM and CAPM with Beta Calculation

Hey guys,

Below is the link to download my Stock valuation via DDM and CAPM with Beta Calculation spreadsheet.

Stock Valuation spreadsheet

In brief, the spreadsheet automatically downloads stock, market, sector and rates data from various websites before performing a number of calculations to produce an estimate of the stocks intrinsic value. The spreadsheet calculates the stocks systematic risk (beta), cost of equity via the Capital Asset Pricing Model (CAPM), and intrinsic value via the Dividend Discount Model (DDM) and minor variations of.

The following instructional video runs through the steps for analysing an example stock, Transurban (TCL) in this instance, and provides a high-level description of the calculations which are carried out.

The second instructional video below runs through the VBA subroutines which perform most of the calculations in the background. This is not necessary viewing for people who simply want to use the spreadsheet without knowing the mechanics of the all calculations.

A more detailed explanation of the mechanics behind the spreadsheet follows…

Firstly, the spreadsheet, at the command of the user, downloads the following data from various websites:

  • Stocks previous closing price (among other key statistics) from Yahoo Finance;
  • Current Australian Government Bond yields from Bloomberg;
  • Current list of all the company’s listed on the ASX;
  • Stocks complete historical pricing data from Yahoo Finance;
  • Market (e.g. ASX200) complete historical index data from Yahoo Finance; and
  • GICS Sector (e.g. Industrials) complete historical index data from Yahoo Finance.

The spreadsheet can be updated on a daily basis simply by repeating the steps outlined in the above instructional videos, however, any new historical pricing or index data will be inserted above existing the data, i.e. the original data set won’t be replaced but added to thus improving accuracy of estimates over time.

After all the necessary data has been downloaded/updated, the spreadsheet can calculate the company’s systematic risk or Beta. This is done using daily, weekly, monthly and yearly Holding Period Returns (HPRs) allowing the user make a choice based on personal preference. In my opinion, the shorter the holding period, the more accurate the results, for example, there are more data points available for daily HPRs compared with yearly HPR.

For each holding period duration, Beta is calculated two ways for additional comparison. The company’s returns are compared with the markets returns (e.g. ASX200 returns) to produce a typically more accurate Beta estimate. Alternatively, the company’s returns are compared with the returns of the relevant GICS Sector Index (e.g. Industrials). I would typically only use Beta estimates produced via Market comparisons but sometimes it might be useful to review estimates produced via Sector comparisons.

Once Beta has been calculated, the cost of equity can be calculated via the Capital Asset Pricing Model (CAPM). The cost of equity is calculated for daily, weekly, monthly and yearly compounding intervals. For example, the weekly cost of equity value represents the rate of return an investor requires to invest in this company for 1 week given the company’s current systematic risk (beta).

The CAPM uses the current Australian government bond yield as the risk-free rate. The particular yield (2, 5, 10 or 15 year maturity) used is based on the user defined investment horizon. For long-term investors this will likely be 15 years, but 2 years might be more suitable for short-term traders. The selected risk-free rate adopted is appropriately scaled according to each holding period duration.

The Dividend Discount Model (DDM) is then applied for both constant growth and zero growth scenarios, where the growth factor is calculated based on the company’s Return on Equity (ROE) and Dividend Payout Ratio.

An alternative method to the DDM is also presented on the first CAPM sheet. This method compounds the user defined, previous years’ annual dividend forward by the growth rate for one to forty years. The present value of each of the expected future dividend payments is then calculated by discounting the cash flow with the appropriate cost of equity given the holding period duration. The intrinsic value is the sum of all discounted future cash flows up to the end of the user’s investment horizon. This is the same concept as the DDM except this method assumes a finite investment horizon (as opposed to the infinite investment horizon assumed by the classic DDM) and might prove more suitable if the growth rate used is greater than the cost of equity calculated.

There is a secondary method presented for calculating the company’s cost of equity and, ultimately, intrinsic value. This method is presented in the CAPM2 sheet and is fundamentally based on how the company’s average yearly returns compare with the markets or sectors average yearly returns. These average yearly returns are a compound average calculated with daily, weekly and monthly compounding periods. Rather than using yearly holding period returns which consider only one data point per year, and perhaps overlook large spikes and/or dips in stock price, this method considers the following:

  • 12 data points for monthly compounding yearly averages;
  • approximately 52 data points for weekly compounding; and
  • approximately 252 data points for daily compounding;

while still considering the covariance between yearly returns. This might be the user preferred method but in my opinion produces less accurate results than those obtained via first CAPM method.

I hope you find this spreadsheet handy and feel free to leave any comments you might have.

Stay tuned for the next spreadsheet which will calculate the variance of your portfolio with only a few clicks and strokes.



%d bloggers like this: