Monday, 2 April 2018

Numbers and Narratives: A Simple Discounted Cash Flow (DCF) Model for Equity Valuation

I still remember the day I got into Equity Valuation. It was a drowsy Saturday afternoon and I was browsing through YouTube videos on Finance. I had been feeling quite annoyed with myself, because I was not making use of my free-time productively. You can imagine how difficult that must have been.

It was at this moment that YouTube suggested that I watch a 1:22:28-long video titled 'Numbers and Narratives' (Probably based on my YouTube watching history). The contrast in the title appealed to me and I clicked on it. To my pleasant surprise, it was a lecture delivered by the 'God of Valuation' Prof. Aswath Damodaran to the CFA Society in Zurich, Switzerland.


I had been following the Professor's blog and website for some time earlier, but then I was more interested in the way he approaches Valuation. I hadn't Valued a single company myself (Apart from the few napkin Valuations and assignments I'd done is Business School), at least not in the way it is supposed to be done, as I would realize later.

In the beginning of the video, the Professor summarized beautifully what he was about to discern:

"So if it's (Valuation) not a Science and it's (Valuation) not an Art, what the heck is it? I'll give you the word that I use to describe Valuation. It's a craft."

Then he went on to explain how a good Valuation marries a story to appropriate numbers. He insisted that the story and the numbers validate each other and that one cannot exist without the other. Further in the video, he joked about how he could get a History Major to learn Valuation, but it was difficult to get a Math Major to do the same, because they lacked imagination. I had always thought of myself as someone with a wild imagination and a decent grasp of numbers. I asked myself then, what am I doing with my life when there is something in my own field of interest that would suit my personality and also solve most of my problems? Something moved in my head and clicked into place. I was hooked.

A few months later, I can now confidently call myself comfortable with Equity Valuation. Valuing more than 30 companies in a span of 3 months has changed the way I look at businesses and the way I invest in Equities. Going from anchoring my purchase price to Price Multiples and my conviction to random Equity Research Reports to anchoring with the Value calculated using my own stories and numbers is enlightening to say the least.

In the initial days, I downloaded and used a Discounted Cash Flow Valuation model built by Prof. Aswath Damodaran himself (He recently used one on his blog post about Spotify IPO, in case you want to check that out). Over the days, I understood the moving parts of the model. I dared to build myself a cheap, knock-off version of the Professor's model, which lacked a lot of the moving parts of the original, but retained enough parts to make the model relevant, yet simple. A few more Valuations to knock out the kinks in the model, and it was ready for the big reveal. I actually revealed it in the famous Indian Investing forum ValuePickr. After receiving feedback, I worked on the model again and fixed some bugs. Today, I present to you, Numbers and Narratives, a simple Discounted Cash Flow Model for Equity Valuation (Not made by a God, but an acolyte):

(Download Numbers and Narratives)

Of course, it's not enough that I just provide you with the model. If I'm going to do this right, I should also tell you how to use the model to Value a company. So, grab a cup of your favorite beverage, settle down and let's get started.

The Model is split into 10 different sections, each serving a different purpose:

1. The Header
2. The Narratives
3. The Numbers
4. The Capital Conversions
5. The Assumptions
6. The Diagnostics
7. The Cash Flows
8. The Value
9. The Sensitivity of Value
10. The Attribution

We will try to understand each section in brief and what part they play in the model. The company I choose to Value as an example is KRBL (Of the "India Gate" Basmati Rice fame).

First things first. In the entire model, only the cells highlighted in bright yellow should be edited/modified. Editing/Modifying any other cell may result in an error or permanent impairment of the model. So tread carefully.

What happens if your forget to enter some inputs in the model? Once again, the model will effectively 'hide' itself. You should then check 'The Diagnostics' section to see where the missing/wrong inputs might be found. Once you find and enter the input, the model should promptly show up. You can even delete all the values in the highlighted cells (You may want to do in order to create a nice template that you can copy and use for new Valuations).

1. The Header

This part is a general note on the company: The Name, the Industry, the Country, the Reporting Currency, the Date of Valuation, the Market Capitalization and a link to the latest Annual Report.

A cool feature of the model is that you can choose to display the currency of your choice (Well, the most famous ones anyway). Just click on the drop-down to select a currency or type in the naming convention and the corresponding currency symbol should show up throughout the model (Starting with the immediate next cell - Market Capitalization). In our case, of course it's going to be INR, since KRBL Limited reports its earnings in India.

2. The Narratives

As Prof. Aswath Damodaran says, every good Valuation marries stories to numbers. This is the 'story' part of the Valuation. You should try to describe in brief, what story the company has had so far and what story is likely to play out in the future. Understand that the Narratives you tell makes you responsible for the Assumptions you will make later in the model.

3. The Numbers

This is where you visit the latest Annual Report of the company you are trying to Value. Some generic data (Such as Beta and Indexed Returns) can be obtained from simple online searches instead. You will quickly notice that I have enter all the figures in Crores of Rupees. You can enter in any other unit if you want, but be consistent across the board.

I will explain in short where I obtained all this data:

2017-18 Q4 Report / Full Year Results

  • Sales
  • Previous Sales
  • Operating Income
  • Depreciation
  • Minority Interests (Actual Minority Interests of 4.27 multiplied by 4.5, the Basmati Rice industry's average P/B Ratio)
  • Equity
  • Debt
  • Cash
  • Cash Equivalents
  • Number of Shares

Other Online Sources


  • Tax Rate (Assumed at 25%, the Corporate Tax Rate in India)
  • Indexed Returns (Long-term CAGR of NIFTY chosen over the short-term CAGR which is too high at 25%+)

4. The Capital Conversions

Prof. Aswath Damodaran holds a strong view that R&D Expenses should be Capitalized and not Expensed. This is consistent with the Accounting definition of what makes a 'Capital Expense' (Long term payoff) and what makes an 'Operating Expense' (Very short term payoff). Consider a Pharma company. A bulk of their spending is in R&D, which will boost their Sales and Profits years down the line. So does it make sense to treat them as an expense, as and when they occur, or treat them as an Asset now, to be written off in equal intervals of time in the future? Treating them as an Asset makes the most sense, as their payoffs are delayed. The same explanation goes to Capitalizing Operating Lease expenses. Last but not the least, this section also allows you to calculate the Market Value of Debt, which is different from the Book Value of Debt. But this is the actual amount of Debt the company owes to its lenders. Once again, let's go through how these data points are obtained:

4A. Research & Development Expenditure:

First, you need to make a decision on how long the company's Research will stay relevant before it becomes obsolete. For Tech-based companies, it would be closer to 1-2 years and for Manufacturing firms, it would be around 8-10. If you enter, say 5, like the case here, you need to also enter the previous 5 Years' R&D Expenses. That is exactly what's shown in the screenshot:

The figures are taken from KRBL's Annual Reports here, here and here. Hit CTRL+F and search for 'Research' until to happen upon some numbers. For the current year (2017-18), the R&D Expense has been assumed proportionately based on the Sales of this year and last year (This is because the Q4 Results and Full Year Results report does not provide any details regarding R&D Expenses specifically).

4B. Market Value of Debt

Once again, since the Q4 Results and Full Year Results report did not provide any break-down of Debt and Maturity details, we have to resort to adjustments based on the Annual Report of last year. Interest Expense, 'Debt (Type E)' and 'Maturity (Type E)' are proportionately assumed entries based on the Debt and Maturity levels in 2018 and 2017. The maturities of the older (2017) Debts have been reduced by a year, to account for an year passing since 2017.

Please do not include Working Capital / Short Term Loans. Also, from the actual Interest Expense, remove the part of Interest paid on Working Capital / Short Term Loans. You can do this by finding out the Working Capital / Short Term Loans amount from the Notes to Financial Statements and then finding out the interest rates on those loans (Also found in the Annual Report). Since this is easily done, I'm skipping this part alone. But make sure this is done. Some companies hold a lot of Working Capital and it will be an error to not do this.

The details of those Debt are given slightly below this table as separate notes. Since they were too big and cluttered, I did not include a screenshot. But you can easily find them in the Annual Report.

4C. Operating Lease

Another set of assumed entries, since no break-up is found in the Q4 Results report. Operating Lease details are found in Note #1 of the 2017 Annual Report. Since the 2-5 year Operating Lease commitments are not given explicitly, I have split the full amount of Rs. 26.83 Crores into 4 parts of Rs. 6.71 Crores each.

5. The Assumptions

Whew.. with the previous section, all the mechanical number-searching work is done. We are now entering the creative part of the Valuation exercise.

These all are the assumptions you make which will form part of the Value of the company. Remember the Narrative you detailed earlier? You should try to link your Narrative to respective numbers. If you are confused, look at the company's short-term average for the High Growth Period and the same figures for a mature company in the industry for the Terminal Period estimate. You should get a broad idea of how these numbers evolve. Since these numbers form a crucial part of the Value of the company, make sure you do diligent research and proper justification for each and every entry.

An important entry, select the length of the High Growth Period. The default is 10 and the maximum is 20, with an intermediate option of 15. To understand why this is important, you may go through Prof. Aswath Damodaran's piece on How Long Will Growth Last? For KRBL, I chose 15 years of High Growth.

One thing I'd like to note here is that you have the option of discounting at the 'Opportunity Cost' instead of the 'Industry Beta' (Based CAPM Cost of Capital). Personally, I always discount at my Opportunity Cost. More on this here. Since I am doing this post for everyone to view online, I am using the 'Industry Beta' instead.

If you're worried about making the wrong or overblown assumptions, have no fear. Let's take care of that in the next section.

6. The Diagnostics

There's no need to enter anything in this section. This section acts as a Quality Control for your assumptions. You cannot just make any assumption regarding your company. There are some logical limits. Rest assured, if your assumptions are off somewhere, you will get a warning here.

So as far as possible, justify a 'Maybe' (In 'Link to Narratives') and religiously avoid a 'No', by changing your assumptions as per the Solution given by the model.

7. The Cash Flows

There's no need to enter anything in this section as well, but observe. This is how your company's financials will evolve based on your assumptions.

Cash Flows beyond the Terminal Period are estimated using the Dividend Discount Model (Basically, just a formula).

8. The Value

At last, the final frontier! The fruit of all the hard work.

Here, the 'Probability of Failure' is a nod to probability theory. The odds of any company failing tomorrow is a non-zero figure. Here, I simply made it 1% -- meaning, there's a 1% chance that KRBL can wind down tomorrow for whatever reason.

A simple DCF Valuation finally calculates the Intrinsic Value (Value per Equity Share) of the company. If you enter a Margin of Safety (The more you are unsure with your assumptions, the higher should be your Magin of Safety), the Expected Purchase Price of the share will show up. This is the price at which you should purchase the shares of the company, according to your own estimates.

9. The Sensitivity of Value

This section allows you to see what are the other possible Values for the company. based on varying the Cost of Capital and Sales Growth in the Terminal Period:

Think of this as a Normal Distribution of Values with unsound values in either ends (Here, Rs. 271 and Rs. 1401) and a more plausible value in the middle (Rs. 464.03).

The Win-Loss Ratio will tell you in how many of these scenarios the Value exceeds the current Price of the share. A higher (Say, >1.5) Win-Loss Ratio means that you should have more conviction towards investing in the stock.

10. The Attribution

A homage to the original creator of the model.

If you plan on downloading and using this model, I have a sincere request. Do not edit or remove this section. We owe at least this much to Prof. Aswath Damodaran for single-handedly making Equity Valuation open source for students and investors alike.

To summarize, I have a quote from Prof. Aswath Damodaran himself, from his book 'The Little Book of Valuation':

The value of a firm is a function of three variables—its capacity to generate cash flows, its expected growth in these cash flows, and the uncertainty associated with these cash flows.

If you're able to even roughly predict all of them, you would already be an above-average student of Valuation. Read a lot of books and Annual Reports to improve your decision making skills when it comes to the assumptions. The same goes for the Valuation itself. Practice makes perfect. Let compounding knowledge work its magic for you.

On a lighter note:

The Monte Carlo Simulation

A small caveat, I should mention, is that the 'Sensitivity of Value' tool only considers variances in the Terminal Year. What if the assumptions in the High Growth are also wrong? A Monte Carlo Simulation allows for such possibilities and is a way better tool to account for probability than the simple Sensitivity Analysis.

If you already know how to run a Monte Carlo Simulation, download any of the dozen online tools available as Excel Add-ins (I use this one). A MCS allows you to have a lower and upper limit for the Value, so that you can make Buy/Sell decisions according to your comfort level. This is how a Monte Carlo Simulation's output looks like:

A little bit of excel maneuvering to visualize the data and you end up with this:

If you have an understanding of Probability, the above diagram simply shows that the Values at the far end of the graph (Rs. 245-Rs.315 or Rs. 599-Rs.670) are most definitely incorrect. The ones inside are far better estimates, ranging from Rs. 315 to Rs. 599. Out of those, Rs. 457 is the most likely estimate. This is how you can eliminate the risk of randomness in a DCF (Or any model, really). This way, you don't have to, say, Sell KRBL if it cross Rs. 475. You can hold on until it reaches Rs. 528 or Rs. 599, depending on your risk aversion tendencies. The same goes for the downside. If you're unsure that Rs. 457 could actually be the Value of KRBL, you can buy it at Rs. 386 (If possible) and be a more content investor.

I thought long and hard about including a Monte Carlo Simulation tool in the model, but decided against it. The aim of the model is to be a simple method to enable an investor to anchor his price to a probable Value, not a model with clunky formulas running into pages. If you have a good understanding of how to run a Monte Carlo Simulation and convert it into a Normal graph, feel free to do one yourself every time you Value a company. If you don’t, no worries. The Sensitivity of Value tool is also a decent variant for a simulation.

Disclaimer: This model cannot be used to value BFSI firms. In fact, BFSI firms are like the twilight zone for Valuations in general. Theoretically, it is possible to Value a BFSI firm using a DCF, if you carefully audit every P&L item and Balance Sheet item and arrive at the proper Free Cash Flows. But that will take way too much of time for it to make sense as a regular Valuation tool. Some say the Dividend Discount Model could be used as an easier fix for Valuing a BFSI firm, but that’s also not very accurate. Unfortunately, that’s as close as we can get to logically Valuing a BFSI firm. This is owing to their all-cash and extremely leveraged nature of business.

If you have concerns, queries or improvements that you need to communicate with me, feel free to comment down below or drop a mail at I'm around most of the time and would definitely appreciate some quality chat about Valuation and Corporate Finance.


  1. Dinesh -

    Very nice blogpost. I was wondering how did you calculate free cash flow to equity in your calculation of free cash flow. I was trying to understand how have you put the formula by Prof.damodaran into your model.

    FCFE = Net Income
    - (Capital Expenditures - Depreciation)
    - Changes in non-cash Working Capital
    - (Principal Repayments - New Debt Issues)

    The way I see in in your model is you have assumed sales growth, operating margin and reinvestment and depreciation. Does this reinvestment include net capex and net working capital??

    1. Thank you, Abishek.

      In my model, "Reinvestments" are calculated using the formula 'Sales / (Equity + Debt - Cash).

      Essentially, I'm looking at how much non-cash Assets have changed per unit of sales. So yes, it includes both CapEx and Working Capital.

  2. So, from your formula, 'Capital Expenditures + Changes in non-cash Working Capital' is taken care. Removing this from Net Income and then adding back Depreciation will give me FCFF. Later, I remove the PV of Debt and Leases, along with the Value of outstanding Options (If any) from this figure to arrive at the FCFE. I don't take the '- (Principal Repayments - New Debt Issues)' route.

  3. A number is a concept that arises from the result to have things that are an aggregate or a generalization of this concept.

  4. Hey, I think you have averaged all the existing loans and multiplied them by 0.14 in Type E of debt. Why it is multiplied by 0.14??is it for adjustments based on how much Long Term Debt increased from FY17 to FY18??Is it necessary to put entry in Type E cell??Why have you not done for other companies too??

    1. If I remember correctly, then yes, it's an adjustment based on how much the Type E Debt was based on last year's Debt levels (It was around 14%). I don't do this anymore, since it is too little to matter and is generally confusing to explain. I just stick with the latest figures I get from the AR / Quarterly reports.