Excel Formulas for Stock Trading

So you finally want to begin taking your stock trading knowledge to the next level by applying it. From personal experience I can attest to the fact that there is more writing on investing and the philosophy surrounding it than any of us will be able to read in our lifetimes, but due to the elusiveness surrounding stock trading it can be very tough to find solid ground to base your investment decisions on and it can only feel right to want to continually learn just a little bit more. Here are a few formulas you can plug into excel to help you find solid ground when analyzing a company for stock trading:

Price to Earnings Ratio

This is one of the oldest and easiest financial ratios to calculate, all you do is plug in the market price of the stock at any given time and divide it by the company’s annual or quarterly earnings, which you can find in the financial statements of the company. Fortunately, most of the sources found at Stocktrading.net will have the ratio already done for you. In excel it will look like this:

Compound Annual Growth Rate

If you want to measure the average rate that a company is growing at in a particular area over a certain amount of time then the Compound Annual Growth Rate (CAGR) formula is what you will need. This formula will tell you the percentage rate that a company will have to grow at from the first year of analysis in order to get what it has in its final year, and is figured out by dividing the final year by the first year and then exponentiating by one divided by the number of years (You still awake?). In excel it looks like this:

You can find this information by going to the company’s financial statements (The Income Statement, the Balance Sheet, and the Statement of Cash Flows) and plugging in numbers from all the various line items you want to analyze, but keep in mind that the temporal direction will often vary from source to source, some starting, from left to right, in year 5 and going to year 1, or some going from year 1 to year 5 and even some who don’t have all the years!

Future Value

Let’s say you want to project forward by a number of years what a company has been growing at for a previous number of years so you can perform some more advanced financial formulas using the projection. This future value formula can be used with the interest rate you get from the previous CAGR formula to continue compounding forward the particular line item into the future. The future value formula, using the interest rate we got from the previous example (CAGR: 38%), looks like this in excel:

There are many financial ratios that help you to simplify what is actually going on behind the mountains of numbers most of us associate with a public company’s financial statements. Using these formulas gives you a better understanding of the condition that the company is in and will definitely help you become great at stock trading.