Nifty Valuation Analyzer: PE, PB, 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).

Want to conduct a sales-free "basics of money management" session in your office?
I conduct free seminars to employees or societies. Only the very basics and getting-started steps are discussed (no scary math):For example: How to define financial goals, how to save tax with a clear goal in mind; How to use a credit card for maximum benefit; When to buy a house; How to start investing; where to invest; how to invest for and after retirement etc. depending on the audience. If you are interested, you can contact me: freefincal [at] Gmail [dot] com. I can do the talk via conferencing software, so there is no cost for your company. If you want me to travel, you need to cover my airfare (I live in Chennai)

Connect with us on social media


Do check out my books


You Can Be Rich Too with Goal-Based InvestingYou can be rich too with goal based investing

My first book is meant to help you ask the right questions, seek the right answers and since it comes with nine online calculators, you can also create customg solutions for your lifestye!Get it now.  It is also available in Kindle format.

Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You Want

Gamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you want My second book is meant for young earners to get their basics right from day one! It will also help you travel to exotic places at low cost! Get it or gift it to a youngearner

The ultimate guide to travel by Pranav Surya

Travel-Training-Kit-Cover This 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)

Create a "from start to finish" financial plan with this free robo advisory software template


Free Apps for your Android Phone

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

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.

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

  1. 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?

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

  2. 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?

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

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

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

  5. 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"

  6. 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"

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

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

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

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

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

Comments are closed.