How to analyze stocks in Excel?

Before we understand ‘how to analyze stocks in Excel’, we must know ‘why to analyze stocks‘ in first place. Why we cannot simply buy a stock and expect returns? Because stock is a ‘speculative asset’.

What is a speculative asset? It is an asset type whose market price has a tendency to become overpriced. How we can call an asset as overpriced? Asset is said to be overpriced when its market price is trading significantly higher than its “fair price‘.

Why to do stock analysis? Stock analysis is a way to ‘estimate fair price‘. Once fair price of a stock is known, it can be compared with its market price to understand if the stock is ‘overpriced‘ or not.

But there is a problem. The problem lies in estimating fair price of stock. What is the cause of the problem? To estimate fair price of stocks, one must know how to read and comprehend ‘financial statements’ of companies. Which are those financial statements?

  1. Balance Sheet.
  2. Profit & Loss Account.
  3. Cash Flow Statement.

Steps necessary to analyze stocks

How to analyze stocks in Excel - Relation Between Financial Reports and Fair Price Intrinsic Value

I’will try to explain the essential steps necessary to analyze stocks in simple colloquial language. This is an important understanding. If one can understand the correlation between a company’s ‘financials’ and its ‘fair price’, this will be a big-leap towards learning stock analysis.

  • Step #1. Financial Statements: Learning how to read financial statements is key. When I say reading, I also mean understanding. One must not only read the financial reports, but should also develop the skill to frame a bigger picture about the underlying company. Why bigger picture? Because it will help us to gauge its business fundamentals. Read more about reading a balance sheet.
  • Step #2. Business Fundamentals: Learning to read financial statements is necessary to understand how strong (or weak) are the company’s fundamentals. What factors decide fundamentals? Future growth prospects, management’s efficiency, profitability, current financial health etc. While reading a financial report, one must also simultaneously comprehend the fundamentals. Read more about fundamentally strong stocks.
  • Step #3. Mathematical Model: In the above two steps we have seen what it means by reading and comprehending financial statements. But to estimate ‘fair price’ (intrinsic value) of stock, one must know how to convert the numbers into stock’s fair price. To do this one must also master a mathematical model. One such famous ‘model’ is called discounted cash flow model.

[Check more valuation models at the bottom]

I am sure you must have figure out by now that why stock analysis (specially estimation of intrinsic value) is not a widely practised skill.

But I will also like to add here for those people who really like to learn stock analysis, irrespective of the complexities involved, that the key lies in developing the skill to read financial reports. Once this skill is developed, the balance seems to fall in place automatically.

MS Excel and Stock Analysis

How to analyze stocks in Excel - Process used by my Stock Analysis Worksheet2

If you wanted an easier alternative of stock analysis than what is explained in the above three steps, I have a solution. You can use an Excel Sheet. Yes, a simple excel sheet can make ‘stock analysis process’ a lot easier. How?

There are two ways of doing it:

  1. Easy way: The easier way is like a crash course of stock analysis using MS Excel sheets. This will help us to estimate fair price of stocks without going into too much detailing about the business fundamentals of the company. As a beginner, I started my stock analysis journey like this.
  2. Detailed way: Here you can use my excel based stock analysis worksheet to analyze stocks. Why I describe it as detailed? Because it asks ‘its user’ to feed all financial data of stock into the worksheet. Feeding data will take approximately 15-20 minutes for a beginner. But the balance calculation are done be the worksheet automatically. Know more about my stock analysis worksheet here.

In this article, we will be introduced to the easier way of stock analysis. Using this method, one can fairly estimate if the current price of a stock is ‘fairly priced’ or not.

Stock Analysis in Excel – Easy Way

How to analyze stocks in Excel - Easy Way

What is shown above is the process of checking if the current price of a stock is fairly priced or not. How it is done? In the following 3 steps:

  • Step #1. Expected PE after 3 Years: First note down monthly price of stock posted in last 3 years. Then, note down its quarterly EPS (EPS Q). Next, calculate last four quarter EPS for each month (EPS-L4Q). Next, use this formula to calculate EPS for each month (P/E=(Price)/(EPSL4Q)). Finally, calculate Average PE for last 3 years. The calculated ‘average PE of last 3 years’ will be our expected future PE (3 years from now). Please see the snapshot of calculation of an example stock done in MS EXCEL.
How to analyze stocks in Excel - Easy Way - PE Calculation
  • Step #2. Expected EPS after 3 Years: To calculate this, one must use the Future Value (FV) formula of MS Excel. Three parameters must be fed in excel to calculate future EPS: growth rate, holding time, current EPS. Please see the snapshot of calculation of an example stock done in MS EXCEL. The parameters used for Future EPS calculation are: growth rate = 8%, holding time = 3 years, current EPS = 80.17.
How to analyze stocks in Excel - Easy Way - Future Growth
  • Step #3. Compare: This is the third and final step. In this step we will check if the current price of stock is fairly priced or not. How to do it? Simply by using the data collected in the above two steps. We will use the above data to estimate potential future price, and its growth rate. If future growth meets our expectations, we can say that current stock price is fair.
How to analyze stocks in Excel - Easy Way - Fair Price Analysis

How we can conclude the stock analysis as explained above? We can follow the sequential thought process as explained below:

  1. What will be the future price? Expected future price (after 3 years) of our example stock is Rs.1,552. We have arrived this by using the P/E formula (PE x EPS = Price).
  2. What is the current price? The current price of the stock is Rs.2,155 (see snapshot used in PE calculation).
  3. What will be the price growth rate? As current price (Rs.2,155) is higher than the expected future price (Rs.1,552), it means its growth rate will be negative (-10.2% p.a.). Use this formula for growth rate calculation: [(future price/current price)^(1/ years) – 1].
  4. Does the price growth rate meets the expectation? If the investor expected the stock price to grow at 12% p.a. in next 3 years, -10.2% actual growth is much below the expectation. Hence, the current price of the stock can be said to be overpriced (not fairly priced).

Conclusion

If you can access the price data, and financial report of a stock, you can use the above method to check if the stock is currently fairly priced or not. It is not a detailed way of analysis, but it works.

My suggestion is that, one must start with this step of stock analysis and then gradually transform self to a more detailed approach.

I will not misguide you in believing that the detailed approach is easy. But it is worth taking the effort. Why?

Because if you can master this process of stock analysis, stock can make you good money in times to come.

One good example of a person who has mastered this art is Warren Buffett. Today his net worth is $89 Billion. He is one of the world’s richest man.

Yes, this is the potential of mastering the art of stock analysis. How you can start? See how a detailed stock analysis works by trying your hands on my stock analysis worksheet. Stock report generated by my worksheet looks like this:

How to analyze stocks in Excel - report of SAW

Handpicked Articles:

Be the first to comment

Leave a Reply

Your email address will not be published.


*