Automated Return on Equity Analysis with the Dupont Formula

The 10th version of the freefincal automated stock analyzer includes an easy-to-understand graphical analysis of the factors that drive return on equity (ROE) using the five-step Dupont Analysis.

Return on equity is defined as

ROE = Net income/shareholders equity.

It is the profit for each unit of shareholders equity or the earning power of the company. If there is a gradual increase or decrease in ROE, it is beneficial to dig deeper and find out the reasons behind the trend.

Deconstructing the ROE into multiple factors helps us understand the factors that drive the ROE change. This was first done by DuPont explosives salesman Donaldson Brown in 1912 and hence the name Dupont formula.

In its modern form, the ROE is written as a product of five ratios.


Thus the ROE is the product of

  1. Financial leverage
  2. asset turnover
  3. operating margin
  4. interest burden
  5. tax burden.

1. The financial leverage is a measure of a companies debt. Higher the leverage, higher the borrowing and it could result in an increase in ROE! Therefore, if a fall in ROE is driven by a fall in financial leverage it is probably a good development.

2. Asset turnover is a measure of how well assets (including debt) are used to generate profit. An increase in ROE coupled with an increase in asset turnover is desirable. Decreasing asset turnover but steady/increasing revenue could imply the presence of unproductive assets.

3. Operating margin can be thought of as the profit left over after paying for production costs. A healthy operating margin is necessary to pay back debt. An increase in financial leverage should soon result in an increase in operating margin!

4. Interest burden represents the extent interest that has to be paid out to lenders. When financial leverage increases, interest burden will also increase.

5. Tax burden is a measure of the taxes that has to be paid. When interest burden increases, tax burden will usually decrease.

As with all financial ratios and balance sheet entries, these are heavily industry dependent.

Here is an example screenshot (click to enlarge) of the Dupont analysis page. The financial data is automatically downloaded from Morning Star.

Dupont Analysis of Bharti Airtel

The ROE has dropped significantly.  The main ROE driver appears to be a fall in operating margin and net income. However, financial leverage has increased and therefore so has the interest burden.

The operating margin seems to have picked up last FY which is important considering the higher interest burden. While I do not know the reasons behind the fall, there are indications of a recent increase in profitability. Excuse me if this is not a very effective example for demonstrating ROE drivers, I am only a student. In any case, I had a wonderful time trying to understand about ROE drivers behind Colgate, Berger, Emami  and other companies.

Other Features

The automated stock analysis sheet

  • pulls financials from morningstar and analyzes them
  • pulls adjusted stock price history from money control, and
  • calculates intrinsic value six different ways!

It also pulls annual (standalone/consolidated) and quarterly financials from Value Research online.

Valuation models available:

1) Price Multiple Model

2) Sustainable Growth Rate

3) Book Value Growth Rate (Buffett’s approach to valuation)

4) Discounted Cash FlowOpens in a new windowOpens in a new window (DCF) 

5) Reverse DCF Valuation

6) Graham formula  and Graham number

7)  Piotroski Score for the last 9 financial yearsOpens in a new window

8) Earnings Growth Estimate.

Download the freefincal stock analyzer V10 with DuPont analysis

15 thoughts on “Automated Return on Equity Analysis with the Dupont Formula

  1. Thanks for including the Du-pont analysis in the stock screener. You have driven me down the memory lane ( almost 15 years back) when our Finance team in Rane Madras developed the complete Excel sheet working and analysed data for 5 years and also used it for the Business Plan for next 5 years highlighting the action points for driving the profit upwards. I remember using this sheet was later used for simulation as part of a Training Programme onFinance for Non-Finance EXecutives

  2. Hi ,Thanks ,Very useful and made painstakingly . Can we add 03-2016 data in same file ? Any update for the same ? Please suggest ..thanks

  3. Dear ,
    Can you please explain what the stockpricedata graph is showing. Something wrong there? Why date format is different in first column?
    Just FYI if something need to be fixed.

  4. There is a bug in the excel when generating StockPriceData sheet on the basis of Moneycontrol sheet. If we write the name of stock in A3 and then select it in A7 it will generate wrong data in the StockPriceData sheet But if we Follow A12 and paste the URL in A16 then data generated is correct.
    Stock Name: Sunteck Realty
    Data from option one(selecting stock)
    Date Open High Low Close Volume

    30-08-2016 23.2 23.2 23.2 23.2 2400
    22-08-2016 29 29 29 29 2400
    08-08-2016 25 32.95 24.25 28.45 475200
    16-06-2016 29 30.25 29 30.2 441600
    15-06-2016 27 30 27 28.4 19200
    14-06-2016 26 26 26 26 2400
    20-05-2016 24 24 24 24 2400
    27-04-2016 25.15 25.15 25.15 25.15 2400
    26-04-2016 25.1 25.1 25.1 25.1 2400
    13-04-2016 27.35 27.35 27.35 27.35 2400

    Data from URL method
    Date Open High Low Close Volume

    07-09-2016 232 241 231.4 236.9 62775
    06-09-2016 231.05 232.7 225 228.4 9785
    02-09-2016 233 234.45 227.5 228.2 33295
    01-09-2016 236.9 236.9 227 229.6 23363
    31-08-2016 217.2 240 217.2 232.35 12221
    30-08-2016 240.95 240.95 234 235.5 17652
    29-08-2016 231 240 228.5 236.05 18608
    26-08-2016 237.65 237.65 227.15 230.4 18019
    25-08-2016 236.65 236.65 230 234.3 11376
    24-08-2016 230.9 237.05 230 232.8 11811


    1. This is not a bug. As mentioned in the sheet, MOneycontrol does not use consistent stocks codes. So the user has to verify the stock price data downloaded via A5. And if not write, use A16.

  5. Can you explain more about the Valuation sheets so that we can verify the result generated.
    As for the same stock Proected price after 10Y as per Valuation 2 sheet is 9600 CMP is 246.65 and Reverse DCF Valuation 5 sheet DCF value per share with margin of safety B53 is 724.85 and as per Valuation 4 sheet M40 M45 M50 M55 all are negative so am little bit confused about the Valuation being calculated.
    Can you help me in this regard either by explaining in any article about the valuations or either by attaching here an excel sheet with any stock and valuation being verified by you.

    1. Valuation sheets crucially depend on inputs and assumptions. So only those who can make intelligent guess can use them. I have included example links in the sheet itself in the notes page. I wish I could write about them myself. I am not competent enough to do that.

  6. Thanks
    I will go through those articles myself.
    Can we collectively make an excel watchlist where in just by click of button it gets the data required by us for all the Stock Names and there morningstar id being kept in column A and B respectivley.
    Let me know if you are in we can discuss again.

  7. I just saw it, it will also help my case.
    Are you going to upload the xlsx file also as mentioned in the article so that it can be directly uploaded to screener.

  8. Hello Team,

    Thank you for the wonderful sheet. While this has worked for a majority of the stocks, on OmkarSpecality the morning star tab is not getting populated.

    Request you to kindly help .

Leave a Reply

Your email address will not be published. Required fields are marked *