Nifty Valuation Analyzer: PE, PE, Div Yield, ROE, EPS Growth Rate

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-analyzer-3

Nifty PB vs. 10-year moving average

Nifty-analyzer-4

Nifty Div Yield vs. 10-year moving average

Nifty-analyzer-5

Nifty EPS Growth rate (1-year rolling)

Nifty-analyzer-2

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).

Nifty-analyzer-1

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).

Install Financial Freedom App! (Google Play Store)

Install Freefincal Retirement Planner App! (Google Play Store)

book-footer

Buy our New Book!

You Can Be Rich With Goal-based Investing A book by  P V Subramanyam (subramoney.com) & M Pattabiraman. Hard bound. Price: Rs. 399/- and Kindle Rs. 349/-. Read more about the book and pre-order now!
Practical advice + calculators for you to develop personalised investment solutions

Thank you for reading. You may also like

About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete  the entire comment or remove the links before approving them.

33 thoughts on “Nifty Valuation Analyzer: PE, PE, Div Yield, ROE, EPS Growth Rate

  1. Ravi Vooda

    Pattu ji, I was actually working on the concept that i read"when the market is in extremes it is usually two standard deviations away from the mean and the such data is usually 5% of the data". For Indian markets with Nifty data I have observed that we actually are near to 2.5/3 standard deviations away from mean during bubbles and -2 standard deviations away from mean near bottom. Do you think it is a good indicator to track along with PE, PB and Div yield?

    Reply
    1. pattu

      One can do this but the question is, is the standard deviation and the average itself valid?! I will plot the rolling standard deviation in this chart itself.

      Reply
  2. Ravi Vooda

    Pattu ji, I was actually working on the concept that i read"when the market is in extremes it is usually two standard deviations away from the mean and the such data is usually 5% of the data". For Indian markets with Nifty data I have observed that we actually are near to 2.5/3 standard deviations away from mean during bubbles and -2 standard deviations away from mean near bottom. Do you think it is a good indicator to track along with PE, PB and Div yield?

    Reply
    1. pattu

      One can do this but the question is, is the standard deviation and the average itself valid?! I will plot the rolling standard deviation in this chart itself.

      Reply
  3. Alok Jha

    Sir, a combination of PE, 10yr GOI bond yield and Dow/gold ratio could be the better indicator of Sensex valuation and also the asset class allocation metrics. please comment

    Reply
  4. Alok Jha

    Sir, a combination of PE, 10yr GOI bond yield and Dow/gold ratio could be the better indicator of Sensex valuation and also the asset class allocation metrics. please comment

    Reply
  5. Narayan Khatri

    Pattabiraman Murari When I press button"Step 2: Click to update index ratios". I found message "Run Time Error '9' Subscript out of range' and it shows me options like "End", "Debug" and "help"

    Reply
  6. Narayan Khatri

    Pattabiraman Murari When I press button"Step 2: Click to update index ratios". I found message "Run Time Error '9' Subscript out of range' and it shows me options like "End", "Debug" and "help"

    Reply
  7. Santosh

    Sir I am confused ...just let me know simple steps like whether nifty is bullish or Bearish and Wat exactly I need to check to get this...It would sound silly but please guide

    Reply
  8. Santosh

    Sir I am confused ...just let me know simple steps like whether nifty is bullish or Bearish and Wat exactly I need to check to get this...It would sound silly but please guide

    Reply
  9. Venkatesh

    Sir, these blogs are really great. Thank you. When i click step 2, i get following error message:
    "We can't connect to 'http://www.nse-india.com/content/indices/histdata/CNX%20NIFTYall01-05-2015-To-15-07-2016.csv'. Please make sure you're using the correct web address".

    I guess the NSE web site some updates. I tried to fix it by changing initial part of the url to "https://www.nse-india.com/products/content/equities/indices" which is the updated url in NSE site. Seems I am doing something wrong. can you please guide?

    Thank you.

    Reply
    1. freefincal

      I am afraid the NSE server is always a problem. I will try and change this. In the meantime, you can manually update.

      Reply
  10. Sowmi

    Dear Sir,

    I'm a big for your Mutual fund analysis tools. Now i thought of updating my analysis into Stocks also.

    IF you can tell us how to interpret the stock using the ratios it'll be more helpful, like you have done for Mutual funds.

    I want to learn regarding all the ratios to analyse the stocks, Kindly advice me sir.

    Reply

Do let us know what you think about the article