How to Calculate the Intrinsic Value of a Stock +Excel

Detailed Lesson on How to Calculate the Fair Value & Intrinsic Value of a Stock & Formula + Download Our Free Intrinsic Value Excel Calculator

The Intrinsic Value or Fair Value of a stock estimates a stock’s value without regard for the stock market’s valuation. We will firstly uncover how Warren Buffet calculates Intrinsic Value using the Discounted Cash Flow Model, then I will show you the most effective way to automatically calculate the intrinsic value for all the stocks in the USA.

Intrinsic Value of Stock

The intrinsic value of a stock is calculated purely by estimating the future cash flow (FCF) of a business, usually for the next ten years. This cash flow is discounted in each future year based on variables such as interest or inflation. The total discounted cash flow (DCF) is then divided by the number of shares outstanding to give the per-share intrinsic value.

If, for example, the intrinsic value of a stock is 30% higher than the current market stock price, that essentially means a share of the company has a margin of safety of 30%.

How to Calculate the Intrinsic Value of a Stock
How to Calculate the Intrinsic Value of a Stock

Intrinsic Value Calculation Formula

Intrinsic Value = [FV/(1+d)0] + [FV/(1+d)1] + [FV/(1+d)2] + …..+ [FV/(1+d)p

  • FVx = Net cash flow (inflow or outflow) for the jth period (for the initial “Present” cash flow, x = 0
  • d = Discount Rate – Annual rate of interest or Inflation
  • p = Number of periods to be included

Well, this all looks rather complicated, so let’s take a look at how the calculation works.

How To Calculate Intrinsic Value – Buffett Model

  1. Take the free cash flow of the first year and multiply it with the expected growth rate.
  2. Then calculate the NPV of these cash flows by dividing it by the discount rate.
  3. Project the cash flows ten years into the future, and repeat steps one and two for all those years.
  4. Add up all the NPV’s of the free cash flows.
  5. Multiply the 10th year with 12 to get the sell-off value.
  6. Add up the values from steps four, five, and Cash & short-term investments to arrive at the intrinsic value for the entire company.
  7. Divide this number with the number of shares outstanding to arrive at the intrinsic value per share.

Warren Buffett, the greatest investor of all-time, prefers to use this model, also known as the Discounted Cash Flow Model.

By taking the current and estimated net income for the next ten years, and reducing its value to the present value (Net Present Value) due to inflation, then dividing that by the outstanding number of shares, you get the intrinsic value per share.  This is the value of a stock that you should pay, not what the stock market says you should pay.

Calculate Intrinsic Value Using Excel – Download

Intrinsic Value & Margin of Safety Calculator Excel
Intrinsic Value & Margin of Safety Calculator Excel (Click to Download Virus Checked Excel Sheet)

By far, the easiest way to calculate intrinsic value, and the famous Warren Buffett Margin of Safety is to use an excel spreadsheet.

We have done the work for you; our simple to use Excel Sheet provides all the information & calculations you need to determine the intrinsic value of a company’s stock easily.

Download the Intrinsic Value & Margin & Safety Calculator Excel .xls

The Best Intrinsic Value Calculator

While it is easy and free to use an excel sheet to calculate Intrinsic Value and Margin of Safety for an individual stock, it is not very productive as you need to enter the values for each company individually.  If you want to be able to scan through thousands of stocks to find the kind of investments that meet Warren Buffett’s or Ben Graham’s criteria, then you will need a stock screener.

The Best Stock Screener for Intrinsic Value

Stock Rover automatically calculates Intrinsic Value and Fair Value, a key Benjamin Graham and Warren Buffett criteria, in 5 different ways:

  • Fair Value – using a discounted cash flow analysis to determine the Intrinsic Value
  • Fair Value (Academic) – the Fair Value (Academic) of a company uses a discounted cash flow analysis that forecasts cash flows into perpetuity.
  • Intrinsic Value (Academic) – the intrinsic value of a company is determined by adding the Net Present Value of Cashflows and the Terminal Value (Academic)
  • Intrinsic Value EV to Sales – the company’s intrinsic value is determined by comparing its EV / Sales ratio vs. industry norms.
  • Intrinsic Value Exit Multiple – the intrinsic value of a company determined by adding the Net Present Value of Cashflows and the Terminal Value Exit Multiple

Stock Rover has over 150 pre-built screeners; you will need to have the Premium Plus service to unlock the Warren Buffett & Benjamin Graham metrics.

The Warren Buffett Stock Screener - Completely Configured
The Warren Buffett Stock Screener – Completely Configured With Fair Value, Margin of Safety & Intrinsic Value. Scan the Entire US Market in a Few Clicks.

The best stock screener that automatically calculates Fair Value, Intrinsic Value, Margin of Safety, and hundreds of other important fundamental & financial calculations is Stock Rover, our review winning recommended software.

Related Articles: Finding Great Stocks With Stock Rover

[Related Article: Stock Rover In-Depth Review & Test]

[Related Article: Top 10 Best Stock Screeners – Comparison]

The Margin of Safety – What You Should Pay for A Stock

Now that you know what the intrinsic value is per share, you can compare that to the actual share price.  If the intrinsic value is more than the actual share price, that will constitute a value investment.

Warren Buffett likes a margin of safety of over 30%, meaning the stock price could drop by 30%, and he would still not lose money.

[Related Article: 4 Steps to Build Your Own Warren Buffett Stock Screener – Tutorial]

Stock Rover Simply The Best Tool For Investors

Investing In Stocks Can Be Complicated, Stock Rover Makes It Easy.

Stock Rover is our #1 rated stock investing tool for:
★★★★★ Growth Investing – With Our LST Beat The Market System TM
★★★★★ Value Investing – Find Value Stocks Using Warren Buffett’s Strategies
★★★★★ Income Investing – Harvest Safe Regular Dividends from Stocks

“I have been researching and investing in stocks for 20 years! I now manage all my stock investments using Stock Rover.” Barry D. Moore – Founder: LiberatedStockTrader.com

Get Stock Rover Premium Plus Now & Get My “LST Beat the Market System” Included or Read the In-Depth Stock Rover Review & Test.

Alternative Intrinsic Value Calculations

There are many formulas for calculating Intrinsic Value because Intrinsic Value is a matter of opinion.

To clarify, analysts and investors disagree on what Intrinsic Value is and how to calculate it. For example, value investors look at cash, assets, and cash flows. However, growth investors may examine intangible assets like patents, intellectual properties, strategies, business plans, brand names, reputations, and copyrights.

For example, many people regard copyrighted fictional characters, like Luke Skywalker, Darth Vader, Spider-Man, Iron Man, and Captain America, as part of the Walt Disney Company’s (NYSE: DIS) Intangible Value. In addition, most of Procter & Gamble’s (NYSE: PG) potential value comes from popular brands like Tide.

Intrinsic Value vs. Book Value

The Book Value of a company is the value of all its physical, financial, and legal assets. For example, the money in the company’s accounts, accounts receivable, inventory, real estate, patents, assets, equipment, etc.

A good way to think of Book Value is the number of money creditors could receive if a bankruptcy court liquidates the company. However, Book Value does not consider such factors as management, corporate culture, business plans, and consumer loyalty. Also, Market Capitalization is not part of book value.

The easiest way to estimate Book Value is to subtract a company’s liabilities from its assets. For instance, Apple (NASDAQ: AAPL) had assets of $341.998 billion and liabilities of $93.772 billion in March 2019. Consequently, you can estimate Apple’s Book Value at $248.226 billion.

Comparing Book Value to your estimates for Intrinsic Value can give you an idea of how other people are pricing a company.

Price to Earnings Ratio vs. Intrinsic Value

Many people use the Price to Earnings (P/E) Ratio or Price Multiple, but it is not an estimate of stock value. Instead, the PE Ratio is an estimate of the value of a stock’s earnings.

Generally, analysts use the P/E Ratio to compare the earnings of different stocks. You can also compare present and historical P/E Ratios to learn the consistency of a stock’s earnings.

A classic P/E Formula is the stock price divided by the Earnings Per Share (EPS). You must pay attention to the P/E Ratio because it is the most popular stock analysis formula.

However, the P/E Ratio is a short-term analysis tool that has little effect on Intrinsic Value. On the other hand, speculators watch the P/E Ratio because it can affect short-term market prices. Hence, the P/E Ratio can be an indicator of a stock’s future market performance.

Formulas for Calculating Intrinsic Value

However, several formulas can give you a rough estimate of the Intrinsic Formula. Understanding and utilizing these formulas can make it easier to value and understand stocks.

1. The Dividend Discount Model

The simple idea behind the Dividend Discount formula for estimating Intrinsic Value is that cash is king.

A simple means of calculating the Dividend Discount is to use the Time Value of Money method. To calculate the Time Value add the number of future dividends to the present stock price.

To determine Walmart’s (NYSE: WMT) Intrinsic Value next year, you would add four quarters of dividends to Walmart’s stock price ($100.04 on May 15, 2019). Since Walmart will pay a 53₵ divided on June 3, 2019, its Intrinsic Value could be $102.28.

Hence, a dividend investor will spend $100.04 to get $2.24 in dividends. Thus, you can determine if $2.24 is worth $100.04 if you purchase Walmart stock for the dividend.

The Problems with the Dividend Discount Model

The Dividend Discount Model’s problem is that a company’s management can end the dividend; or change it at any time. For instance, a company that pays a quarterly dividend could switch to annual dividends. Moreover, some companies will occasionally issue big bonus dividends.

Unfortunately, the Dividend Discount Model only works with stocks that pay dividends. Hence, the Dividend Discount Model is useless with companies; such as Alphabet (NASDAQ: GOOG), Amazon (NASDAQ: AMZN), and Berkshire Hathaway (NYSE: BRK.B), which refuse to pay dividends.

2. Discounted Cash Flow Model – How Warren Buffett calculates Intrinsic Value.

Interestingly, Warren Buffett bases his Intrinsic Value calculations on future free cash flows. To explain, Buffett thinks cash is a company’s most important asset, so he tries to project how much future cash a business will generate.

The usual formula for estimating future Free Cash Flows is the Discounted Cash Flow Method. Here is an example of a simple Discounted Cash flow Method

  1. Take the free cash flow of the first year and multiply it with the expected growth rate.
  2. Then calculate the NPV of these cash flows by dividing it by the discount rate.

Note: the NPV refers to the Net Present Value or the present value of money. You calculate the Net Present Value by subtracting the discount rate from the money’s future value and multiplying it by the number of years you are measuring.

  1. Project the cash flows ten years into the future, and repeat steps one and two for all those years.
  2. Add up all the NPV’s of the free cash flows.
  3. Multiply the 10th year with 12 to get the sell-off value.
  4. Add up the values from steps four, five, and Cash & short-term investments to arrive at the intrinsic value for the entire company.
  5. Divide this number with the number of shares outstanding to arrive at the intrinsic value per share.

The advantage of the Discounted Cash Flow Method is that it is simple. The problem with this method is that Free Cash flows can vary dramatically over from year-to-year. Thus, the final figure from this method is guesswork.

The Limits of Intrinsic Value

In the final analysis, Intrinsic Value is nothing but an estimate because there is no guarantee that future numbers will match the ones you use.

Intrinsic Value estimates are often wrong because they exclude outside factors that can change a company’s price. For instance, the oil price determines the value of an oil company like Exxon-Mobil (NYSE: XOM).

Consequently, any change in oil prices can cause Exxon-Mobil’s free cash flow to go up and down. Thus, calculating the Intrinsic Value of oil companies is tough.

Therefore, Intrinsic Value is just one of many tools you should use to analyze stocks. Over-reliance on Intrinsic Value can cost you money by giving you an unrealistic picture of stock values.

Summary: How to Calculate Intrinsic Value

Understanding Intrinsic Value is important for all value investors, but calculating it manually is labor-intensive and counter-productive. Using our Screener Review Winning Stock Screener, Stock Rover will enable you to find the most undervalued companies on the NYSE, or NASDAQ exchanges, simply and effectively.

6 COMMENTS

  1. In the sample excel spreadsheet for MSFT, how did you calculate the “Value” column and what does the “Value” column represent? Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here