Discounted Earnings Model

A standardized Valuation Method

Recently I valued Microsoft, an American Software company with their fingers in every pie.

During that valuation I referenced a custom Discounted Earnings Model that I developed that would provide a standardized way to value companies, making use of publicly available data.

Today we’re going to go through that model, see how to use it, and how it works, and at the end I will provide you with a copy of the valuation files for your own use.

The Context

As you know if you’ve been a long time reader of the blog, for some time I’ve been making use of the same valuation models and formulas in order to come up with a valuation for companies.

Some of these models and formulas have remained the same over time, and some have changed, with a small minority being added and removed as well.

Naturally these constant changes make it difficult to compare companies, not to mention that although I have tried to use the same formulas I have made mistakes and either mis-copied them, or didn’t use the same amount of input data, or did any number of small mistakes that might have changed the final result.

This happened even in well established models, where I made mistakes that would have had a major impact on the final result. A good example would be my AFL valuation using Ben Grahams models, which understated the growth rate by a factor of 100!

In order to avoid this I wanted to create a standardized file that I would put in the inputs, no matter the company, and it would return the valuation to me. This way, since I wouldn’t be touching the model, formulas or assumptions, at the very least I would maintain a comparable valuation between companies.

The Outputs

The first and most important thing about the model is of course the outputs it gives.

In order to access them you simply need to go to the outputs tab:

If this looks confusing to you, don’t worry!

On the left side you have a number of key ratios that I find important to judge a company. These include the profit margins, revenue and earnings growth, as well as some per share data. and Tax rates.

It also tells you a few things about what you get when you buy the company now, including its PE ratio, Dividend yield, payout ratio, etc…

Finally it gives you “Risk Premium” and a “Discount Rate” variable, these will be used later in the Discounted Earnings model. In general, the lower these are, the better.

On the right side you have the standard valuations on top, which include the historical expected return, the book value, Ben Grahams formulas as well as my standard Margin to PE formula.

In these cases, if they are highlight red, that means that the current price is higher than what the model sees as fair, and vice verse if they are highlighted green.

Please be aware that just because it is green, doesn’t mean you should invest. You always need a margin of safety, and so you should assign the appropriate discount.

Finally you have the results of the Discounted Earnings Model on the bottom right, with the expected final results at the end of the model (9 years forward).

It provides you the estimated Book Value, Earnings per share, Dividends Received, and Dividend per Share, both in absolute terms, as well as their discounted value.

Additionally, it provides an estimate of the final price, at one of 2 multiples, either the Historical Market Average PE ratio, which for the US market is around 15, or using the current market average PE ratio.

Using that, and the estimated dividends received over the time period, we can estimate the total return of the company during that time, and see if it is in line with our goals.

The Inputs

In terms of inputs you need to put in, here they are:

I reduced the number of different inputs as much as i could, so that the amount of work needed to set up the models is reduced.

There are 3 main types of data, which you can get from 3 different free websites:

• Income statement and balance sheet

• Outstanding Shares

• Dividend and pricing information

The income statement and balance sheet data can be most easily acquired from SimFin, a website where you can download bulk data regarding US companies.

The Outstanding shares data is best taken from MacroTrends.

Finally the Dividend and pricing information is best taken from Yahoo Finance.

Of course you can always pick and choose your data sources if you don’t feel those are reliable. The first 2 points can also be taken from the annual reports, and your broker should have dividend and pricing information as well (or the investor relations website of the company in question should have it as well!).

Simply replace the numbers in the Green cells with the values you get from those 3 locations, and you should be good to go!

How the Discounted Earnings Model Works

The way the model works is very simple, in general we take the following parameters:

• Pre-tax Earnings

• Outstanding Shares

And we “simulate” them for the following 10 years, assuming that the long term change will be in line with the previous 10 years.

So for example, if the Pre-Tax earnings have grown 10% per year over the last decade, we assume they will continue to grow at that pace.

We also assume that the company will continue to pay dividends, and that such dividend will grow in line with the earnings growth.

We use the results of that to derive the Shareholder equity, payout ratio, EPS, and so on.

The Discounting

The problem that we are then faced with is then, “How do we appropriately discount these earnings?”.

Selecting the right discount rate is a key problem, because that will meaningfully change the terminal value of the company.

The solution I found is simple, yet elegant.

It relies on two things:

• Inflation rate

• Divergence from the Mean

The inflation rate is the “baseline” here, and is intended to account for the decreased value of future earnings due to the earnings being generate simply being worth less than they are now.

For this value, since I primarily invest in European and American companies I chose to use the central banks inflation target of 2%, because I believe that over the long run the central banks will adjust their policies to “tilt” the playing field towards that rate.

If you have good reason to believe a different value is more appropriate (or you are valuing a company in a country with different central bank inflation policies), you may change that rate in the Assumptions Tab.

That “Inflation” value I then multiply by the “Risk Premium” value we discussed before.

The way that is calculated is also easy to understand. I simply take the current PE ratio, and divide it by the historical market PE ratio, which for the US market is around 15 PE.

The reason I call this the “Risk Premium” is very simple, higher PE companies have significantly higher growth prospects attached to them, furthermore, they are disproportionately harmed by Inflation, since they need to grow more in order to “hit” the targets set out for them.

The higher the expectations, the higher the likelihood of failure, furthermore companies can be very dynamic, and it is not unusual to see “PE Compression” happening over a decade long period.

After all, just 10 short years ago Microsoft was trading at a 10 PE ratio, and now it is at 36. I fully expect that high PE ratios will over the long term simply revert to the mean, and so I am accounting for that both in the discount rate, and in the final expected Price.

Summary

So now that you understand why I made these models, and how they work it’s time for you to make use of them yourself!

You can download the file from here.

Feel free to play around with it, and give me feedback on how it can be improved!

Let me know what you think!

And as always, if you have any questions or comments, shoot them on Twitter @TiagoDias_VC or down below!

And of course, don’t forget to subscribe!