Whether you’re training for a marathon or going on an adventure trip, being ready can make a world of difference.
The same is true for the stock market. It’s important to be prepared with a watchlist of fundamentally sound stocks ready to be bought at the right prices. Whether the market is in rally mode or in a phase of correction, being prepared with a watchlist is key.
Here is Version 4.0 of my Stock Analysis Excel Sheet that will help you with exactly that – identify high-quality businesses to create your watchlist and buy them when the prices are right.
Download this sheet on your computer, read carefully through the instructions to follow a few simple steps, and then analyze not just the past performance of a company but also arrive at its approximate intrinsic value range.
Click Here to Download Stock Analysis Excel Template Version 4.0
Like the previous version 3.0, this latest version feeds in data automatically from Screener.in website, which subsequently feeds into my sheets on financial analyses and intrinsic value calculations. So, please thank Screener’s creators and my friends Ayush and Pratyush before thanking me. 🙂
If you have been into financial modeling in the past, this excel file may seem like a child’s play. But, if my 15+ years of experience as an analyst is anything to go by, this is most of all you require to “quantitatively” analyze stocks…not models running into hundreds of rows and tens of sheets.
I have personally tried my hands at both the kind of models – the simple one that you can download below, and the complex ones that most analysts use in their doomed pursuits of finding the elusive target prices. Without a doubt, simplicity beats complexity hands down.
By the way, the excel that you will download below – and especially the intrinsic value calculations therein – works well with simple businesses that have a good track record of performance. Complex, volatile businesses must not be analyzed anyways, and no spreadsheet can help you there. Also, this excel won’t work for banking and financial services companies.
New additions to this version over the previous one include – explanation of key ratios, new sheet on ratio analysis, and sheet containing few performance charts that will tell you in a snap how the business has done over the past few years.
Finally, as I had mentioned for the previous version, this version also contains a Data Sheet, which is the core sheet from Screener.in site and any changes to it may produce errors if you want to customize the Safal Niveshak excel and upload again to Screener’s site. So, please don’t touch this sheet at all.
Click Here to Download Stock Analysis Excel Template Version 4.0
Words of Warning!
Before you get down to using this excel, remember six critical things…
- It’s just a compass and not a map. So take your next step carefully.
- Don’t look for perfection. It is overrated.
- Focus on decisions, not outcomes.
- Look for disconfirming evidence. Avoid falling in love with the numbers.
- Remember Charlie Munger who said, “All I want to know is where I’m going to die, so I won’t go there.” Depending just on this excel for decision-making can really kill you (financially)!
Let me know if you found this excel helpful. I will try to improve upon this in the future based on your feedback.
Share your thoughts, suggestions, and testimonial for this excel in the Comments section of this post.
Thanks a ton for this wonderful work Vishal – one request, is it possible to automate the FCF calculation to avoid manual entries of cash please ? Thank you
This is a wonderful and truly useful product especially when combined with screener.in
I uploaded the excel you provided in screener.in/excel. Now when i export the excel the data coming in it is blank.
Please ignore my above comment. I had to enable editing in excel so see values.
Thank you for your effort 🙂
How to upload this excel sheet in screener.in
BRUCE GREENWALD EPV NOT IN XCEL. CAN USE FROM 3.0
I am grateful for your tool! It is really helpful.
apart from cosmetic changes, is there any noticeable change from version 3.0 to 4.0?
Also can you incorporate banks and other financial institutions in version 5.0?
Hi,
Great file. About the screener , is there a screener for US stock market as well?
Thanks
Thanks for this wonderful work Vishal. This is a wonderful and truly useful product.
Thank you for revising this excel. I am grateful for your efforts, time and knowledge. Please keep up the good work and I wish your tribe increases by leaps and bounds.
Thanks
Dear Vishal,
Thanks for the extremely useful stock analysis tool. I’ve just used it to download information for couple of companies from screener.in for testing purpose. The information downloaded is extremely valuable. My sincere and heartfelt thanks to you for your time and effort in making this and sharing with everyone. May God bless you for your bigheartedness.
Wonderful tool. Great effort. Excellant aid to leran Financial analysis.
It is a fantastic compass- What is the use of a map , if you don’t have a compass when you are on high seas
Thanks for the wonderful work vishal.
When i saw some websites like simplywall.st, They give pictorial representation of growth revenue and necessary information. Do you have any opinion about these sites?
Please let me know.
Thanks for this work sheet Sir, its great.
Thanks a ton, Vishal!
Dear Vishal,
Commendable work by you to empower small investors. You are one of those rare individuals who care more for the benefit of others than yourself. God bless you.
Regards,
Kaviraj.
Sir,
Please elaborate me in brief how to use this excel data for valuation of the stocks?
Hi Vishal Sir,
In the cell A11 of DCF tab, it is mentioned in the comment that the value of Net Debt level is automatically added to the PV of cash flow below in the sheet.
However, I don’t see it being added to PV anywhere. Can you please clarify the doubt?
Also, how can we calculate intrinsic value per stock using DCF method used?
Thanks in advance!
Regards,
Ashutosh Jain
Thank you very much Vishal Ji for getting this wonderful utility created. It certainly saves a lot of time and effort in extracting the data to an excel sheet, which I otherwise used to do when the utility was not available. Thanks again!
Cash + bank + current investments = share capital + borrowings + investments
Is the above calculation right?
The excel sheet is excellent tool but the data of stand alone and consolidated can be downloaded. Few companies have both details.
Please advise whether consolidated data to be used for the analysis?
FCF is to be manually uploaded from the annual reports which is tedious. What about using change in net assets +depreciation used in this case. Is there any other reliable way?
Excellent work. Made my life easier to remove the froth that sometimes exists with our thinking about great companies that we think we should by buying.But when we see the numbers they tell a different story. Tools like these really help the cause.
Thanks for the tool and sharing it too Vishal.
Very nice effort and I must say that you have a big heart for helping other fellow investors. I have not used this tool yet but after seeing reviews posted here I am 110% sure that tool is really very helpful
Sir, in the DCF calculation, isnt the net debt required to be added to arrive at the overall intrinsic value? I couldnt be sure whether the net debt is getting added in the total PV.
Dear Sir,
Thank you so much for sharing knowledge. May the gods be kind to you and your near and dear ones
Regards
Manu
Hi Vishal,
Thanks for the Sheet, not only that it makes the analysis easier, it actually help us understand the concepts easier. I normally do mine in parallel to check if I understood correctly. Is there any special reason why you dropped EPV ( I think it was present in version 3.0 ). Thanks for your time and patience.
Hello Sir,
Needless to say this excel file is really a very useful tool. Now that we have started receiving 2019 results, could you please share the method to tweak the excel file so that 2019 numbers start flowing in.
Many thanks again for this valuable excel!
Hi this is really very good, however it is not taking 2019 numbers can you incorporate the same… thanks
I getting all detailed data of any company, what a job sir great
Very great hats off sir
Thank you, Sir for valuable and great work. Is it possible to find meaning of cost categories related to US stocks too. I am absolute beginner and have difficulties in interpreting differencies compared to US market.
Best
How to remove excel integration to safal niveshak.I want plain vanilla excel download.
Hi Vishal,
As a student of your guidance, I am immensely grateful to your thoughts and work towards enlightening financially illiterate or less literate guys like me. Your work in Safal Niveshak is super beneficial
I don’t know whether I am going to win or loose or earn more money, it doesn’t matter to me. At the end of the day, I have learned few better things in an area where I was/am in dark.
I am happy that, as Mr.Charlie says, I am going to bed a little wise every day and Safal Niveshak is immensely contributing towards it
Dear Vishal
Thanks a ton for this wonderful work Vishal – My suggestions / queries are:
1. Is it possible to automate the FCF calculation to avoid manual entries of cash please ?
2. Both consolidated and stand alone reports can be down loaded from screener. Which one to be followed?
3.Can both reports be unloaded in one sheet and can be viewed side by side?
4. Can summary sheet include intrinsic values as below. ( I have modified and incorporated in the excel sheet given by you.
5. Further I download consolidated report and past in the modified workbook but due to placement issue the data is not linked properly. I can send you the modified workbook for your view and comment.
Anyhow it is excellent work and I appreciate it.
Best regards
Samant
Thank you
Reply
Dear Vishal,
Thanks for providing this wonderful excel template to kick start the analysis of stocks.This is immensely helpful to see so many numbers in one place.
While using the excel and doing some calculations I found out that there is a typo in one of the formula for Trends –> Sales Growth & PBT Growth for 10 years in Profit & Loss sheet in excel 4.0.
We are calculating 10 years of CAGR but the formula used has exponential value of 1/9 instead of 1/10.
Thanks
Shashank
Dear Vishal,
Thank you so much for this wonderful spreadsheet.
Regards from Indonesia,
Luthfi Ferizqi
Hi Vishal,
Thank you so much for creating this.
In the DCF sheet, column B11 has Net Debt Level and the comment in column A11 mentions that this will be added to the NPV of cash flows. However, when I check the NPV calculations – B27 to B29, I did not see Net Debt Level (B11) being added to here.
Am I missing something? Shouldn’t Net Debt Level be explicitly added to the NPV of cash flows? Or does your comment mean that it gets added implicitly based on how the 1st year FCF is calculated?
Thanks in advance.
This is really innovative and makes investing so much simpler.
Thanks a ton
Dear Sirm
First of all a huge thank you from all of us to make investing so interesting & simple yet cover most avenues of the markets! I have thoroughly enjoyed using Screener.in I had a few questions & I’m hoping you could help me out.
Q1) Certain values on Screener.in do not match with the Values in the Company Annual Report. I have come across several such instances. One such example is that of ITC Consolidated Balance Sheet & P&L Statements the value for “Net Sales” “Other Income” “Net Profit” ” Total NC Assets” & Many other do not tally with that in the consolidated annual reports issued by the company.
I am assuming that every organization has its own method of arriving to the numbers, request you to share a file regarding how your CALCULATING METHODOLOGY.
I am currently a subscribed member on money control, ticker.finology, & value research. I am hoping to switch to Ticker since its beautifully comprehensive yet simplistic. (I have faced similar issues on the former two websites too.)
I appreciate your advice and help! Request you to kindly get back regarding the same!
Thank you very much!
Dear Vishal,
Thank you for providing the valuable insights bout value investing. I tried to use the stock analysis excel sheet version 3 and 4, I uploaded to the screener.in/excel and then whenever i download the sheet for any particular stock, I do not see any information getting populated into the downloaded excel sheet.
Is this any known issue some one else also faced?
Vishal,
I went through your Cash Flow statement video and i must say you provided such a simple and nice example to explain about the Balance sheet and cash flow statements.
I was trying to analyze the Financial statements of Sun TV Network through the stock analysis excel sheet. While reading the cash flow statement, I saw that the “Payment for Property, Plant and Equipment” (which i am considering as Capital Expenditure) is much lower than “Payment for purchase of financial instruments (current investments)” for most of the year. Is that something which could affect Free Cash Flow? Also there are mention of “Proceeds from sale of Property, Plant & Equipment ( PP&E) ” in the cash flow investing activities. Does this affect the Free Cash Flow Calculation?
I m new to market and don’t now the techniques, but reading you and screener and getting knowledge.. please guide and also share the excell 4
Regards
Hi Vishal,
The Net Profit figure is not coming correctly in Profit and Loss Sheet of excel, not matching with Screener numbers.
Could you please look into this?
How can i download the excel.Please guide me.
Thanks for the spreadsheet. Its a goldmine! I have a query w.r.t DCF. I don’t see the Net Debt in B11 being added to the final Present Value of Cashflows. Shouldn’t that be added? Or am I missing something?
There is no link to download the file. I have tried multiple times. If it is a paid subscription to download the file, it should be mentioned clearly rather than wasting everyone’s time. Something which is easily accessible is only useful and we should not be made to run around and subscribe to any service. I spent more than 30 minutes of my precious time to locate the file but of no use.
Hello Vishalji,
Do we have a version 5 of the stock analysis please?
Regards
Ashish
Hi Vishal! Is it possible for us to add data of FY 19-20 by ourselves? If not, request for version 5.
Thanks for your valuable service to fellow investors
Thank you for your help to identify good stocks.