This post contains affiliate links.

In investing, you want to buy a successful company at a very undervalued price that will give you a good return on your investment. But you are unsure if you are paying more for the stock. The stock could be overpriced, and thus you will not make a profit. Calculating the Margin of Safety can significantly reduce risks and increase the chances of high return.

The Margin of Safety is buying a stock at a price below its fair value (Intrinsic Value). Benjamin Graham, the author of the Intelligent Investor, invented the Margin of Safety. It is what Warren Buffett uses as well as numerous other value investors. 

The following is a step-by-step guide to calculating the margin of safety using excel. This method is inspired by Phil Town’s Rule 1 value investing method.

9 Steps To Calculate The Margin Of Safety Using Excel

For this guide, we will use NVIDIA Corporation (NVDA) to calculate the Margin of Safety. You can download a free copy of the excel sheet calculator below:

Warning: Please do not use this as a way to buy NVIDIA stock, as company fundamentals can change between publishing this guide and the company’s current situation; please do your due diligence before purchasing any stock, as the following is for education purposes only.

The calculate the margin of safety, you first need to gather the following numbers:

• Current EPS value
• Estimated Future EPS (growth rate)
• Estimated Future PE

Those numbers are part of the value investing strategy and can be found on the income statement and numerous finance websites; if you are new to these figures, see our Guide To Value Investing Strategy, as it explains why they are important to value investing.

Below is an explanation of how to get each of those figures.

1- Current EPS value

Current EPS value or Earning Per Share can be found on any finance site under EPS (TTM) which stands for Trailing Twelve months EPS. For this example, we obtained the EPS (TTM) from Yahoo Finance. Just type the ticker symbol NVDA into the search bar.

2- Estimating future EPS growth rate

To estimate the future EPS growth rate, we have to consider the following:

• The future value is an estimation of the company’s future earnings. Therefore it is not always accurate.
• The best proxy for future EPS is the equity growth rate we can obtain from the Company Balance Sheet.
• Use the estimated future value that is the lowest of Historical EPS growth or experts’ predictions.

This is How to find each of those values:

2.1- Calculating the Historical EPS value

It is important to note that the more historical data we have, the more accurate our estimation is. It is preferred to use historical data that is at least 5 years old. You can obtain historical data from the annual reports or using finance sites. For this example, we will use quickfs.net and 10 years-old data to find the historical EPS value.

Type the company name or symbol into the search bar, and from the drop-down menu, we select Balance Sheet.

We then get to the company balance sheet. We simply need to subtract the Liabilities & Equity from Total Liabilities to find the equity. We take the oldest value and the news value as shown below:

We subtract the values in the excel sheet as shown below:

To find the Historical Equity, we need to utilise the Rate function in Excel as shown below:

• nper: The total number of payment periods. Which is 10 years
• pmt: The payment made each period. We need to skip this one because we are not making payments each year. Skip by putting a comma.
• pv: The present value. Which is the 2011 equity value because you are finding the historic company EPS (You need to put a negative sign before that old equity value).
• fv: Future Value. The 2021 equity value.
• Skip type and guess, close the bracket and hit enter.

In this example, we get 18% historic EPS

2.2 Analysts’ EPS growth rate

We must compare our Historical EPS to the analysts’ estimation and pick the most conservative. Many finance sites post EPS predictions for the next 3 or 5 years. For this example, we will use Zacks.com to find the next 5 years’ predictions.

We compare that value to the historical EPS and pick the most conservative. In this example, the analyst’s eps is the most conservative, so we put the value in the excel sheet.

3- Estimating future PE

The future Price Per Earnings ratio is found by taking the lowest of the:

3.1 Double the estimated future EPS growth rate

In this example:

2*17.6=35.2 estimated future PE

3.2 Historical PE

Finding the historical PE requires taking the average of the highest and the lowest PE ratio in the last 5 or 10 years. We will use MSN Money to find the historical PE:

After typing the company name in the search box, we click on Analysis.

After that, we click on Price Ratios and take the highest and lowest PE ratios.

We find the average = (75.32+26.530)/2=50.9

Then we compare the Double estimated EPS with Historical PE and take the lowest.

On some websites, it is already available with an average value of a 5 or 10-year P/E ratio, so just take that value and put it in the PE Average in the excel sheet.

4- Rate of return

To find the intrinsic value, we need to specify a minimum rate of return. It is better to select a value above 10%. Because 10% is the average return on most index funds, for this example, we will select a rate of return of 15% because it is higher than the S&P 500 rate of return, and it is not too high that it makes it impossible to gain that return and not too low that it can not keep up with inflation.

5- Selecting the minimum rate of return period

We have to select a return period into the future. For this example, we will select 10 years into the future.

6- Calculate EPS value in 10 years

This formula calculates the EPS value in a specific period into the future. If you use the excel sheet, it will find the value for you if you occupy all the fields until step 6.

7- Calculating Future Market Price

The future market price is the value of the stock in 10 years which is calculated by multiplying Estimated future PE by EPS 10 years into the future:

This will give us a $377.53 share price value in 10 years.

8- Calculating the Intrinsic value

We can use the rule of 72 to find the Intrinsic value or the fair value of the stock price. The rule of 72 is a method to determine the time it takes to double your investment. If we divide 72 by the rate of return (15), it gives us the number of years it takes to double our money.

Therefore it takes 5 years to double our investment once and 10 years to double our investment twice.

If we double $1 twice, it will be $2 in five years and $4 in ten years; this means that the intrinsic value is 1/4 the future market price.

If the rate of return target was 20%:

Therefore, if we double $1 three times, it will be $2 in three years, $4 in six years and $8 in nine years. This means that the intrinsic value is 1/8 the future market price.

The higher the rate of return, the less the intrinsic value. To be able to make that annual compound rate of return, the purchase must be at or below intrinsic value.

9- Calculating the Margin of Safety

Since all our calculations are estimated based on either historical figures or analyst predictions, there is no guarantee that we will get the estimated rate of return every year if we buy the stock at the intrinsic value. Therefore, we need to allow for an error margin, to ensure that even if we are wrong with our estimation, we can be confident that we will not lose our investment.

The margin of safety is a value you can specify to allow for errors in your calculation. For this example, we will allow for a 50% Margin of Safety.

At the time of writing, Nvidia’s stock price was $112.27, which is higher than the calculated intrinsic value ($94). This indicates that the stock is likely to be overpriced. To get 15% annual compound interest, you must wait for the stock price to get close to or below the intrinsic value. However, it is best to buy at the Margin of Safety to allow for errors in our calculations.

The margin of safety makes investing less risky. However, investing is still a risk, especially if you put your money into something you will not see in the next 3, 5, or 10 years. Being long-term focused is the best rational way to compound your wealth in the stock market.

If you are serious about investing, read the 5 Actionable Steps To Start Investing In The Stock Market to set up your finances and mindset for success in your investing journey.