Use this Excel sheet to automatically determine Nifty valuations with long-term averages of PE, PB and Div Yield ratios along with ROE and rolling one-year EPS growth rate and dual moving averages.
This sheet has been in development (hibernation?) for the past 6 months. When I wanted suggestions for features at FB group, Asan Ideas For Wealth, Neeta Khilnani Upponi suggested that I include a Rolling PE analysis. She promptly sent me an Excel with a worked out example of a Rolling PE calculation. However, I have some doubts with regard to its implementation so I have decided to include this in a latter version.
A Schiller PE sheet is also on the anvil.
Those interested in market valuation metrics can play with this sheet making up their own ratios. If you would like any additional analysis to be included, please feel free to suggest.
The sheet downloads index values from Yahoo Finance and Index ratios from NSE. The NSE server is problematic and sometimes may not allow data transfer. I think I have found a way around this. As long as you use the sheet once a month there will not be any issues (dont ask why?! Too boring to explain.) Also, dont use this sheet daily. That would be an overkill. Once a week or once a fortnight should be good enough.
Here are the features of Nifty Valuation Analyzer:
The moving average duration is user-defined. Since we don’t have enough market history, suggest the maximum duration is 10 years.
Nifty PE vs. 10-year moving average
Nifty PB vs. 10-year moving average
Nifty Div Yield vs. 10-year moving average
Nifty EPS Growth rate (1-year rolling)
The annual growth rate has been the same since the Nifty got back to 5000 in 2009. Meaning, the market is unlikely to move up unless the growth rates increase. Hopefully, the interest rate fall will trigger this, like it did in 2002 (thanks to Sundaram Ananthakrishnan for pointing this out)
Nifty ROE or Book Yield
PB divided by the PE gives Earnings per share divided by book value per share. My understanding is that this is also known as return on equity or book yield (correct me if I am wrong).
The ROE has not increased since the 2008 crash! Hopefully, the interest rate cut will trigger its upward movement.
What is the state of the Nifty?
While we are far away from dangerous valuations, this rally appears to bank on hope and sentiment without actual growth. Only when the earnings growth increases can the market sustain high values at low PE. As a novice, this is as far as my thinking takes me. Can you add or critique this?
Additional feature suggestions are welcome.
Update: Nifty valuation analyzer with rolling standard deviation Note: Nifty PE ratio has to be manually updated from the NSE site
Old version: Download the Nifty Analyzer
(This is a heavy file. Graphs will take a moment to display).
Connect with us on social media
- Twitter @freefincal
- Subscribe to our Youtube Videos
- Posts feed via: Feedburner
- We are also on Google Plus and Pinterest
Do check out my books
Get it now. The Kindle edition is only Rs. 199.
Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You WantMy second book is now only Rs 199 (Kindle Rs. 99) Get it or gift it to a young earner
The ultimate guide to travel by Pranav SuryaThis is a deep dive analysis into vacation planning, finding cheap flights, budget accommodation, what to do when travelling, how travelling slowly is better financially and psychologically with links to the web pages and hand-holding at every step. Get the pdf for ₹199 (instant download)
Free Apps for your Android PhoneAll calculators from our book, “You can be Rich Too” are now available on Google Play!
Install Financial Freedom App! (Google Play Store)
Install Freefincal Retirement Planner App! (Google Play Store)
Find out if you have enough to say "FU" to your employer (Google Play Store)