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).
Register for the New Delhi DIY Investor meet: April 23rd 2017Get free, yet comprehensive calculators, tools and analysis delivered to your mailbox! Subscribe to get posts via email
Buy our New Book!You Can Be Rich With Goal-based Investing A book by P V Subramanyam (subramoney.com) & M Pattabiraman. Read more about the book and pre-order now!