Calculate The Margin of Safety Using Excel and the Rule of 72

In trading, you want to buy a successful company but, but you are not sure if you are paying more for the stock. The stock could be overpriced, and thus you will not be able to make a profit. Therefore, there must be a way to value the stock to ensure that we are not buying a stock at a high price.

The Margin of Safety is buying a stock at a price below its true value (Intrinsic Value). The Margin of Safety was invented by Benjamin Graham the author of the Intelligent Investor. It is what Mr Buffett use as well as numerous other investors. 

The Margin of Safety for this guide will be calculated using the Rule 1 Investing method by Phil Town, and it requires the following numbers:

  • Current EPS value
  • Estimated Future EPS growth rate
  • Estimated Future PE
  • Minimum accepted rate of return

Step by step guide to calculate the Margin of Safety using Excel

For this guide, we will use NVIDIA Corporation (NVDA) to calculate the Margin of Safety.

1- Current EPS value

Current EPS value or Earning Per Share can be found on any finance site under EPS (TTM) which stand for Trailing Twelve months EPS. For this example, we obtained the EPS (TTM) from Yahoo Finance

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 future earnings. Therefore it is not always accurate.
  • The best proxy to future EPS is the equity growth rate that we can obtain from the Company Balance Sheet.
  • The estimated future value consider the most conservative value of Historical EPS growth or experts predictions.

How to:

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 to find Future EPS. It is preferred to use historical data that is at least 5 years old. You can obtain the historical data from the annual reports or using finance sites. For this example, we will use and 10 years-old data to find the historical EPS value.

From the drop-down menu, we select Balance Sheet

We then get to the company balance sheet. To find the equity we simply need to subtract the Liabilities & Equity from Total Liabilities. 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.

  • 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 doing payments each year. Skip by putting a comma.
  • pv: The present value. Which is the 2011 equity value (You need to put negative sign before that old equity value).
  • fv: Future Value. The 2021 equity value.
  • Skip type and guess, close the braket and hit enter.

2.2 Analysts EPS growth rate

We need to 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 to find the next 5 years predictions.

Warning: You should check multiple sites to compare the analysts’ predictions and pick the most conservative value.

Source: Zacks

We compare that value to the historical EPS and pick the most conservative

3- Estimating future PE

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

3.1 Double the estimated future EPS growth rate


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.

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 minimum rate of return

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

6- EPS value in 10 years

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:

8- Calculating the Intrinsic value

We can use the rule of 72 to find the Intrinsic value. 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, 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 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 bought the stock at the intrinsic value. Therefore, we need to allow for error, 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 stock price was $195, 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 have to wait for the stock price to get below close to or below the intrinsic value. However, to allow for errors in our calculations, it is best to buy at the Margin of Safety.

Recent Posts