How To Calculate Intrinsic Value (Formula – Excel template & AMZN Example)
Made by Sven Carlin Ph.D. for the free Stock Market Investing Course.
Disclaimer: all content is just for educational purposes and can’t be used as advice.
I suggest watching the video on how to calculate intrinsic value because some explanations are hard to make in written, but article continues below.
If you liked this, you might find more value adding learning materials at my FREE STOCK MARKET INVESTING COURSE.
Intrinsic Value Definition and Formula
In his famous 2001 Georgia University speech, Warren Buffett defined intrinsic value as:
“Intrinsic value is the number that you get if you can predict all the cash the business can give you between now and judgement day, discounted at a proper discount rate.”
When asked how to calculate intrinsic value, Buffett also shared his intrinsic value formula in the same speech:
Intrinsic value formula: “Discounted present value of future cash”
If you are not a business graduate, words like discounted present value of cash flows might sound intimidating, but nothing to worry, we are here to explain the above and enable you to learn how to calculate the intrinsic value of any stock.
Calculating intrinsic value is not a precise method of valuing a stock but don’t worry, there isn’t any that is precise. However, intrinsic value is one of the best tools that allows you to compare investment opportunities by comparing their respective intrinsic values and I’ll do my best to explain intrinsic value in the most applicable way possible.
Intrinsic Value – Explaining The Key Factors
When it comes to intrinsic value, the key factors sound simple: future cash and discount rate. However, to estimate future cash flows you need to know the earnings and the future growth in earnings, that is not easy.
They have tried to make finding the right discount rate a very complex endeavour too, but I’ll simplify that for you. Let’s discuss the key factors and subfactors necessary to calculate intrinsic value.
Intrinsic Value – Estimating future cash flows or earnings
The first thing to estimate are the future cash flows the business can give you from today till judgement day or how Buffett calls that: cash available to you, thus available for distribution.
The cash available for distribution is calculated by using the following formula:
If the analysed business is a stable one, then earnings will often be in line with the cash available for distribution. If the company is focused on growth, it will likely reinvest the earnings to achieve even higher earnings in the future while if the company is a cash cow, it will try to keep investment at minimum levels and distribute all in dividends.
For example, Apple’s earnings and free cash flows are not totally equal, but close. By getting to know the business better, you’ll understand what to use.
I would say looking at earnings and then adjusting them to the story of the analysed business should be the first step when estimating future cash flows. But, if you are not there yet from a financial analysis perspective level, earnings will be a good start to use when calculating the intrinsic value of the business you are looking at.
Earnings
Earnings are the oxygen of a business and should be the metric for its evaluation. If you own a business and have no intention of selling it, the only thing you care about is how much money you made this year. The increase in earnings is exactly what reflects the change in book value and therefore is the only objective indicator of intrinsic value. The best way to assess earnings is to use their past averages and adjust them for growth and cyclicality. You can find the earnings of a business in most investor presentations, annual reports or with data providers.
If I sum up the quarterly earnings for AMZN, I get to $34.15. Compared to the stock price of $3,116 you might think it is expensive at a price to earnings ratio of 91, but it is not all about earnings, intrinsic value is also about growth.
Intrinsic Value – Expected future return or growth
The growth component of intrinsic value is derived from the expected future return on retained earnings which is the most subjective component – returns on earnings impact future earnings growth.
In order to estimate future growth, you need to again understand the business, the market, the sector, the competitive advantage, the risks, on top of trying to prepare for the unknows that are always around the corner (think COVID-19).
For example, Amazon’s growth over the past years has been staggering and on average 25% per year over the last years.
To get to the intrinsic value, we have to make an estimation of AMZN’s future growth rate and impact on earnings. As Yogi Berra said that estimating or predicting is hard, especially if it’s about the future, we need to ask ourselves how sure are we about our predictions.
Intrinsic Value – Estimation of certainty
As Buffett says, how sure are you there will be birds in the bush in the future?
So, it is also about how certain you are your estimates will be approximately correct? It is also about what can happen somewhere in the future that might change your estimation – just think of how the COVID situation impacted airlines, cruises, airports, hotel and other industries.
Something that might help us when estimating the degree of certainty is to use scenarios; best case, average case and bad case. This will at least make you think more about the risk and reward of an investment and give you a better comparative basis. In our intrinsic value formula and template, we will apply three scenarios.
By using scenarios, you can at least know what are the risks when investing in a stock by finding the worst case scenario intrinsic value. You can again compare to other opportunities and see whether you are willing or not willing to take the risk.
Intrinsic value – Discount rate
Buffett said the intrinsic value formula is the “Discounted present value of future cash”.
We said above earnings can be used as an initial measure of available cash flows to be adjusted for each specific business, we need to estimate a growth rate which again depends on our familiarity with the business and then we need to find a discount rate to use in order to calculate the present value.
There are endless discussions, papers, articles, researches, Ph.D theses, books, university handbooks and who knows what else that tries to give you the correct discount rate to use.
Some say the best rate to use is the interest rate on the U.S. Treasury note because it is a risk-free rate because the U.S. government will pay its debt with an extremely high degree of certainty (don’t forget it can print as much money as it wishes to pay back the debt). Others say it is the WACC (weighted average capital cost) but I think one needs to keep things simple.
My simple solution is to use always the same discount rate. Let’s say your hoped investment return in 5%, by using a discount rate of 5% all the time, you don’t need to constantly adjust your intrinsic valuations for fluctuations in the Treasury rates and you will get a good indication on how much you should pay for a business. If you with for higher returns and are ready to look at more businesses, then you might even increase the discount rate to 8% or even 10%. As long as you always use the same, you will get value from the comparative intrinsic value calculations. When you know the discount rate, it is time to calculate the present value of the future cash flows.
The above was the art part of the intrinsic value formula, the difficult part. If you master the above, you will be a great investor. The mechanical part of how to calculate intrinsic value, the intrinsic value formula, is the easy part.
Intrinsic Value Calculation Formula
As $100 today is worth more than $100 next year, when it comes to calculating the intrinsic value of a stock, we need to calculate the present value of each future cash flow. For that, we use the present value formula.
For example, the present value of $100 that I’ll get 5 years from now at a 10% discount rate would be:
PV = $100 * 1/(1+0.1)5 = $100 * 1/1.1^5 = $100 * 1/1.61 = $62.11
PV = $62.11
The $62.11 present value means that at my discount rate of 10%, $62.11 would be a fair level I should invest to get $100 in 5 years. Anything above would be too expensive while anything below $62.11 would be a bargain.
As a financial investment is expected to create value over years, we simply sum up the present values received and we have our intrinsic value.
Too complex? Don’t worry, everything is much easier in excel.
Intrinsic Value Excel Template
Before sharing the formula, let me give you another shortcut first.
When defining intrinsic value, Buffett said is that we need to know the cash flows from today till judgement day. In a world where we are all knowing about the future, of course.
As I have no idea when judgement day will be and I don’t know what will the weather be in 3 weeks, to eliminate issues that go beyond any rational future estimations, we can simplify the intrinsic value calculation by using a terminal value down the road. Let’s say 10 years which makes it easier to calculate things.
If we are in 2020 now, from 2030 onward we don’t estimate cash flows anymore, but we estimate a terminal value for the business. (sometimes, especially with miners where the life of mine is known, I do go beyond 10 years for cash flow estimation, but the further you go, the lower is the impact of the present value on the intrinsic value calculation as the present values become smaller and smaller the further in the future you go due to discounting)
Here is the Intrinsic value calculation excel template.
The intrinsic value template has 5 key input values.
First (1), you need to know the current cash flow or earnings. Secondly, you need to estimate the future growth rate where we split it to the first 5 years (2) and subsequent 5 years (3) because it is unlikely a company will keep growing at the same rate. You put in a discount rate (4) and a terminal multiple (5) which is what you estimate will be the valuation or the price to earnings ratio in 2030. Usually, analysts use a current valuation or average market valuation because intrinsic value is about finding the best investment now. Using current averages makes comparisons easier.
When you insert all the above, you should have a very approximate intrinsic value. I say approximate because growth rates will change, earrings will change, market valuations will change and many things will change in the next 10 years, but an intrinsic value calculation will help you with deciding what is the best investment today for the next 10 years.
To help with the uncertainties, you can put a probabilistic number into the probabilistic cell (100% if you don’t want to work with scenarios) and you’ll get the intrinsic value for the worst, normal and best scenario.
The intrinsic value calculations will depend on your estimation of the growth rate, the valuation for the terminal value, your estimated discount rate and the probabilities you attach to different scenarios. Unfortunately, you will not find those answers within any formulas, that is the art part of investing again.
Let’s make an intrinsic value calculation example.
Amazon intrinsic value calculation example
I’ve looked up the earnings and the free cash flows for Amazon on Morningstar.
Earnings have been $17 billion over the last 12 months, which turns into $34 per share. Free cash flows are even a bit higher than earnings but let’s stick to earnings this time to be more conservative.
On the growth side, average growth over the last 10 years was around 25%.
The current price to earnings ratio is 91, but I would argue Amazon will be a much bigger over the coming 10 years, the growth from 2030 onward will be likely slower, so let’s put a price earnings ratio of 30 for 2031.
If Amazon keeps growing at 25%, we use a valuation of 30 and a discount rate of 10%, the current intrinsic value is $3,397 per share which would still have AMZN trade below its intrinsic value.
However, with a 5% discount rate, the valuation would completely change and go to above $5. This is to show the impact low rates have on asset valuations and prices.
With a 5% discount rate, a growth rate of 20% over the first 5 years and 10% over the next 5 years with a 25 terminal multiple, AMZN’s intrinsic value is $1,704.
You can play around with the discount rate, growth and future valuation. Have fun!
If you liked this, you might find more value adding learning materials at my FREE STOCK MARKET INVESTING COURSE.