How to Calculate the Intrinsic Value of a Stock + Excel Calculator

Easily Learn How to Calculate the Fair Value & Intrinsic Value of a Stock with our Formulas & Download Our Free Intrinsic Value Excel Calculator

To calculate the intrinsic value of a stock, you estimate a company’s future cash flow, discount it by the compounded inflation/interest rate, and divide the result by the number of shares outstanding. This gives you the fair value price you should pay for a stock. 

The Intrinsic Value or Fair Value of stock estimates a stock’s value without regard for the stock market’s valuation. Firstly, we will uncover how Warren Buffett calculates Intrinsic Value using the Discounted Cash Flow Model (DCF). 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 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 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

This all looks rather complicated, so let’s 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 by 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 NPVs 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, 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 calculation is the price you should pay for a stock, 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)

An easy 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 easily determine the intrinsic value of a company’s stock.

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

The Best Intrinsic Value Calculator

The best intrinsic value calculator is Stock Rover, which automatically calculates Fair Value, Academic Fair Value, Intrinsic Value, Intrinsic Value to Sales, and the intrinsic Value Exit Multiple for all US stocks.

Stock Rover The Best Intrinsic Value Calculator

⚡ Features Value Investing, Fair Value Screener, Charts, News, Watchlists, Broker Integration
🏆 Exceptional Features Screening, Portfolio, 10-Year Financial Database
🎯 Best for Growth, Dividend & Value Investors
🆓 Free Try Stock Rover Free
🎮 Trial 14-Day Free
✂ Discount 25% During Premium Trial Period
🌎 Region USA
📰 Read the Review Stock Rover Review

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. You need to enter the values for each company individually.  If you want to scan through thousands of stocks to find the kind of investments that meet Warren Buffett’s or Ben Graham’s criteria, 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) – A company’s Fair Value (Academic) 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 is 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 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 the intrinsic value 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 Market Investing Training - Liberated Stock Trader Pro

Liberated Stock Trader Pro Investing Course
★ 16 Hours of Video Lessons + eBook ★
★ Complete Fundamental Stock Analysis Lessons ★
★ 2 Powerful Value Investing Strategies ★
★ 4 Dividend/Income Investing Strategies ★
★ Professional Grade Stock Chart Technical Analysis Lessons ★

Go Pro Now

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 intrinsic value 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

A company’s Book Value 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.

Book Value is a good way to think of 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 estimates 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 current 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 indicate 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 valuing and understanding stocks easier.

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 current 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 for 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 by 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 NPVs 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 from year-to-year. Thus, the final figure from this method is guesswork.


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 industry Leading Research Reports
★ 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 or Read the In-Depth Stock Rover Review & Test.


The Limits of Intrinsic Value

Intrinsic Value is nothing but an estimate in the final analysis 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.


Are You Looking For Stock Investing & Trading Software? Here Are My Favorites.

My favorite software for trading is TradingView because it does everything well. It has backtesting, great charts, stock screening, and an active community of over 3 million people sharing ideas, plus a free plan available globally.

My favorite software for investing is Stock Rover, as it specializes in deep fundamental financial screening, research, and portfolio management. It is the ideal platform for dividend, value, and growth investing.

My favorite software for stock market news is Benzinga Pro, with its super-fast real-time news engine, squawk box, and news impact ratings.

My favorite AI trading software is TrendSpider which enables automatic pattern recognition for Trendlines, Candlesticks, and Fibonacci levels. Trade Ideas uses AI to generate high probability daily trading signals for auto-trading.

My favorite stock-picking service is Motley Fool Stock Advisor, which has a proven track record of beating the market with excellent stock research reports.

Read the Full Top 10 Stock Market Software Testing & Review

12 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