The Simplest Free DIY Valuation Model Explained

All good investment decisions are grounded in numbers. The need to do back of the envelope calculations, to determine the right price for a potential investment will often arise in our search for value.

Here’s a link from Invest Excel which offers a free and easy to use valuation tool for download. This post is an attempt on how best to use this free tool to time your investments and gauge whether a stock is overpriced or underpriced using the Price Multiple model.

Pros of this Price Multiple Model:

  1. Ease of use. This is the simplest of many valuation methods out there.
  2. It works best in the current bull market where price multiple ratios like P/E are a fair estimate of how a stock is rated or priced by the market.

Cons of this Price Multiple Model:

  1. Price Multiples are forward looking and can be misleading, depending on the stage of the company’s business cycle and whether we are in a bull (high PE) and bear (low PE) phase.
  2. The model does not take growth in cash flows into account for calculating the fair price of a stock.
  3. The model is sensitive to assumptions, increasing the scope of midjudgment.
Table A : The Inputs

This is the “input” table of the excel sheet.

What you put in here are the factors that will determine the numbers you get in the subsequent “output” tables.

EPS in Year 0

This field is the value of EPS, or Earnings Per Share of the company as of today. It is easy to get this number by dividing the current market price (P) by the P/E ratio, both of which are publically available. Simple math.

EPS Growth Assumption 

The first of many assumptions the model requires to calculate fair value. This is an estimate of how much you expect the Earnings Per Share (The E in P/E) from the last step to grow incrementally year on year. There are a few considerations in accurately estimating this percentage growth:

  • EPS can vary due to seasonality and stage of the business cycle – and since this cell requires an “average” value, would make sense to take a conservative estimate of EPS growth.
  • I take 20% above, because I factor in operating leverage that I am expecting to kick in from incremental sales of a software product. For a more mature business, like FMCG, I would take a more reasonable EPS growth value (calculated using a regression line or Log EPS)
  • You can also borrow this number from Analyst or Research Reports that calculate estimated year on year EPS growth.

Forward PE Assumption

In the last phase we assumed a growth in E, now we will make an estimate of the growth in the PE (the Price Multiple) itself.

Note that these are independent assumptions, i.e. the percentage of EPS growth year or year that you assumed in the last step will not impact your assumption of the forward PE, or relative valuation, the company will enjoy 3 years later.

It is called forward PE, because it is an estimate of how you believe the stock will be PE rated going forward, or how it will be valued by the market in the future. Our model uses a 3 year forward PE estimate, i.e. if it is 2017 today, what Price Multiple the market will give this company in 2020.

In the above example, I have assumed the EPS growth to be 20% but forward PE independently to be 30.

There are a few considerations in assuming the forward PE number:

  • The “fair” PE is usually based on how the market rates a particular sector or industry. For instance consumer defensives like FMCG have a “fair” PE rating of 30+ in the current Indian Bull Market.
  • See how the company’s peers are rated by the market in different stages of growth, to arrive at PE rating for your company.

For instance, pure play Software Services companies like Infosys and TCS have a fair PE of about 20 but since I am evaluating a Software Product company – the forward PE would be discounted slightly higher, in line with other product companies like Oracle Financial Services, Ramco Systems etc which enjoy loftier valuations. Therefore, I take the forward PE to be 30 in 3 years.

Current Dividend Per Share

This is the payout per share that you receive from the company in terms of dividend. It is not used in the calculations of the fair price estimates in this model. In my case the company pays no dividends (as it is re-investing all its free cash flows for growth) so I take it to be 0.

Desired Return Per Share

This is what yearly return you desire as an investor, from your investment in this stock.

I am being extra conservative here to be happy with an inflation beating 10%.

Feel free to take a number that matches the % Growth in EPS value or 20% in this case, or alternately, a number closer to the Cost of Equity in India, which is around 15%

Now, having plugged in the inputs in all the above steps, we come to seeing the “output” of the calculations below:

Table 2 : EPS Growth Estimates

Based on the EPS Growth Assumption that we plugged into table 1 we can see the Earnings per share compounding at a growth of 20% over a 3 year period.

In my case the EPS is estimated to double in 3 years. Sweet.

Table 3: The Finale

All our hard work pays off in this table, where we see the price is expected to be after 3 years.

If the dividends grow, then you would see the value of the share bumped up further – with the dividend payouts added as well (Zero in my case, so no such bump expected).

The Present Share Value for Good Value is what we call the “fair” value of the stock as of today.

This is calculated based on the returns we expect (10% in my case) and expected share price in 3 years. Since the stock is currently trading at 129, it is at 49% discount to what the fair price of the stock should be. A discount, or value buy!

Hope it helped. This is a very simple and useful model but remain mindful of other factors like cycles, market sentiment and other leading indicators that may disrupt the assumptions made to arrive at a fair value.


One Reply to “The Simplest Free DIY Valuation Model Explained”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: