Calculate the fair price of stocks with an easy-to-use Google sheet with intrinsic value and margin of safety.
How do you calculate what the fair price of stocks is? Is there an easy-to-use formula to create a fair value for the stock?
The answer is YES, and I will share the same with you here.
Let me help you with a Google sheet for it.
I am sharing a Google sheet, and do not ask for its edit permission. Instead, please copy the above sheet and use it as you like.
I am returning to the original question about how one can calculate the fair price of a share. The critical aspect is the reasonable price of the stock you should invest in.
Understand the Google Sheet
Earnings are one of the common factors to consider in valuations. A common consensus is to use the earnings per share or EPS and price-to-earnings or PE ratio of the companies to determine whether the stock is overvalued or undervalued.
So we will use some of the same to find a fair price.
Warren Buffett mentions cash flow and not earnings or EPS because profits can be manipulated, but cash flow is challenging.
However, we use the business checklist to eliminate companies that can potentially manipulate earnings. So for our calculation’s sake, we can use the earnings or EPS.
- The current EPS – Available on websites like screener and from the company’s quarterly and yearly results.
- Growth (Expected) – What will the expected growth be? We will use the past 10, 5 and 3 years’ profit growth to estimate future growth.
- Expected Return in % – What do I expect from this investment? I like to have a 26% CAGR return which means double every three years or 10x in 10 years.
- Expected PE Ratio in Future – At what price-to-earnings ratio can I sell the stock after ten years?
- The margin of safety in % – Because we are predicting future growth, we also need some margin safety percentage. Again, we will see examples of how to use it.
So using the above values, we derive an earnings and share price chart in excel as follows:
- We apply the compound interest formula for the earnings or EPS to keep growing for the next ten years.
- Then, we apply the back calculation for the share price. So, in the 11th year, we assume the price to be the then EPS times the expected PE ratio.
- Now we apply the back calculation of reverse CAGR on the share price at the expected return to get fair value in the current year.
- Then we apply the margin of safety on the above price to get a fair price.
If it is too much to understand, the examples below will help you.
Asian Paints Example
So, now we have the Google sheet, and all we need to do is put in the parameters from the screener.
- The current EPS is 31.59 for the past year.
- Growth (Expected) – Based on the past growth, we can safely assume the company will be able to grow at 12%CAGR
- Expected PE Ratio in Future – I hope to sell the stock at 90 times its earnings in 2033.
- The margin of safety in % – 20% margin of safety is good enough for my estimates.
When we add the current EPS and expected growth, we get to the EPS the company would do in the next ten years.
When we put in the expected PE ratio in the future, we get the then price and its back calculation to date.
Then we apply the margin of safety on the above price.
And Voila, we have a fair price for Asian Paints.
Pidilite Example
Pidilite has been one of my portfolio stocks since 2017. So I am putting parameters a little backdated.
- The current EPS was 15.66 for the year in March 2016.
- Growth (Expected) – Based on the past growth, we can safely assume the company will be able to grow at 12%CAGR
- Expected PE Ratio in Future – 100 is a promising future PE.
- The margin of safety in % – 10%.
Now we have the EPS of Pidilite in 2026 will be 48.64, and the price at which one should have invested in 2016ish time was around ₹700.
Final Thoughts
Do you invest in stocks at a fair price? What is your preferred margin of safety percentage? Share your thoughts in the comments below.
Future PE Ratio – it seems more – what is the logic behind high future ratio
That is just a calculation based on what has been the PE in the past. When you have some calculation where you are not too sure, you have to apply margin of safety.