Learn the stock market in 7 easy steps. Get spreadsheets & eBook with your free subscription!

Stock Spreadsheet Q&A

Many long time readers know about the stock spreadsheet I offer as part of my book package. This is the exact spreadsheet I use when analyzing a company. It’s a simplified tool to help people buy stocks, but even within it there are questions and confusion.

stock spreadsheet

I had a couple of in-depth conversations with some of my readers and customers regarding the questions they had with the Value Trap Indicator Spreadsheet, and if they have these questions chances are many of you do too.

So I’ve included those questions with my answers in this post. Hopefully it will help those of you following along at home. It may just be the trigger you need to find the confidence in using spreadsheets for stocks.

Hi Andrew, I have a question about the seven categories. To calculate the 3 year average growth (category 6) do I use revenue or should I look at net income? And if my VTI is around 350 should I pass on this stock?

For 3 year average growth use net earnings.

A VTI of 350 is close to the strong buy signal of 250, so it’s worth keeping an eye on for a stock price pullback.

So can I use operating income? Or should I subtract interest and taxes?

Use net earnings which can be found on the income statement. Operating income minus interest and taxes won’t give you the same net earnings numbers that most of Wall Street is using (usually).

Do you use google as well to search through the financial statements? Or sec.gov?

I use sec.gov because it is the definitive source. Not to say that other websites will be inaccurate but I prefer to get my data from the source.

In category 6 of the book you implement the growth. But how do I calculate the 3 year average growth in excel? Because at Finviz it isn’t mentioned. I calculated the 3 year average growth like this: Gemiddelde means average (I am from Belgium). But there is something not right about this formula because the outcome should be negative.

value spreadsheet

Great question. Using your example, you’d take earnings from 2014 – 2013 earnings, 2013 -2012, 2012-2011. Then take the average of this sum (divide the sum by 3). That’d be the average growth.

It gets interesting when negative numbers are involved, because it skews the data. I make the calculations in my spreadsheet, but basically I’ll take growth from the last positive earnings. For ex, to take growth of 2014, I’d subtract from 2012 instead of 2013 since 2013 is negative. 

I really recommend the spreadsheet I built since it takes special cases like these into consideration and automatically makes the calculations for you. There’s a special case for every negative you can think of (shareholder’s equity, earnings, growth, etc.)

Thanks for the reply Andrew! I’ve bought the spreadsheet + audio because it would be more handy as you said :). I just have a question about the spreadsheet. What website do you recommend to find the quarters high and low price?

Great! I hope it will provide great value to you!

I use the annual report (10-k) at sec.gov. If you want a complete overview of looking up numbers in a 10-k, I covered it here: How to Read Annual Reports.

The quarters high and low price can usually be found in Item 5. Market for Registrant’s Common Equity, Related Stockholder Matters and Issuer Purchases of Equity Securities of the 10-k. You can also just use current stock price or eye ball the average stock price from a stock chart. Hope that helps!

Hey Andrew. I’m currently on a ferry heading to Sydney and I’m trying to work out how to calculate a 3 year average earnings growth rate. I bought your Value Trap book and have been slowly building my own excel Value Trap Indicator, learning the financial ratios as I go. Things are going well, but i’m stuck on finding the right formula for X6 that can deal with negative values. What do you use? Thanks.

As you probably figured out, a negative growth hurts the average. To make sure that a negative 3 year growth rate doesn’t affect x6, there is an “IF” condition. So, if 3 year average growth is less than 0.01 (I.e. Negative) then x6 = 4.

Yeah, that makes sense, but there seems to be a few methods to calculate the average growth rate over 3 years, some of which don’t cope with negative numbers. What formula do you use to calcualte the average growth rate over 3 years? Thanks for all the effort on the podcasts by the way. Really good to hear someone talk about investing in such a clear practical way – must be the electrical engineer in you (I’m also an electrical engineer).

If a year is negative, I have the spreadsheet calculate growth from the previous year with positive earnings. For ex, say I had positive earnings in 2014, 2012, 2011, and negative earnings in 2013. Growth for 2014 would use 2012 numbers as the initial value. Growth for 2013 would be negative. For average 3 year growth, it’s just growth of 2014 + growth of 2013 + growth of 2012 / 3.

OK. That makes sense for one year of negative earnings. If a company has a few years of negative earnings would you just set the value of X6 to 4? What would you calculate for the following company earnings -$65m 2011, $29m 2012, -$91m 2013 and $27m 2014? One last question.. How do you pick a company to apply your Value Trap Indicator to when there are so many companies listed on the stock market?

To sort through many companies quickly, use the website (FINVIZ) described in this article: https://einvestingforbeginners.com/…/tackling-analysis…/

A company with negative earnings for the year will always have a high VTI because of another “IF” condition. If you want to see the complete spreadsheet and use it for yourself, you can access it with the gold package: https://gumroad.com/l/value-trap-indicator-gold