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

[dineshssairam]

My take on Valuation is very simple and coincide with how Warren Buffet explains it. A Discounted Cash Flow model is the ONLY way to Value a stock. I am comfortable making this bold claim. If you have read ‘The Intelligent Investor’, Ben Graham explains the same as well.

Think about what happens when you attempt to do a DCF:

1. If you can project the cash flows comfortably, you have nothing to worry. Discount them. This should give you the perfect intrinsic value of the company.

2. If you think your Valuation numbers look slightly off, replace all the numbers with conservative estimates. It’s better to be roughly right than to be precisely wrong.

3. If you think you are unable to predict the cash flows at all, don’t value the company. Maybe someone else can. You can’t. So let it go.

4. Always use a Margin of Safety, just to avoid the ever-present danger that you overlooked something or some number in your Valuation distorted the Value.

But what should be the discounting rate?

1. In an ideal world, the discounting rate you use for Valuing any Asset should be your “Opportunity Cost”. However, finding out your Opportunity Cost is very difficult. An Opportunity Cost is something you can earn with certainty. In other words, an Opportunity Cost is a Personal Risk-free Rate.

2. Let’s say you own a piece of land. You are very sure that the value of the land will increase by 9% every year. You are not so sure about your other investments increasing by this much. So when you consider investing in a stock, you are foregoing a guaranteed return of 9% (Which you can get by investing in a nearby piece of land). Hence, you should use 9% as the discounting rate.

3. If you are not sure about the returns from any of your investments, you should ideally use the Risk-free Rate (+ a few Basis points, because interest rate don’t stay the same always). Currently, in India, this could be 7.29% (Risk-free Rate) + 50% = 11% (Just an example). You should use 11% as your discounting rate.

What we learn from all this discussion is that – discounting rates should not be the point of focus. You already kind of know the range for the discounting rates – in India it starts from 7.29% and probably stops at 11% or 12%, beyond which the ‘certainty’ factor takes a hit. If you are an investment genius like Rakesh Jhunjhunwala, you might consider a higher Discounting Rate. As a personal note, I stick with Ben Graham. I use “Risk-free Rate + a few Basis Points” as my discounting rate.

Quoting Prof. Aswath Damodaran to understand the importance of Discounting Rates:

“While discount rates obviously matter in DCF valuation, they don’t matter as much as most analysts think they do.”

Quoting Warren Buffet to round things up:

“The trouble isn’t that we don’t have one [a hurdle rate] – we sort of do – but it interferes with logical comparison. If I know I have something that yields 8% for sure, and something else came along at 7%, I’d reject it instantly. Everything is a function of opportunity cost.”

The following article offers key insights into how Charlie Munger and Warren Buffet think about Discount Rates:

Why and how do Munger and Buffett “discount the future cash flows” at the...

The following article is very interesting and explains Discounting Rates easily for investors:

Discount Rates for Value Investors

The following article is written by Prof. Sanjay Bakshi and explains why one shouldn’t use very high discounting rates or P/E, P/B multiples to do Valuation:

October_Quest_2013.pdf

Found this wonderful article, which is a collection of a lot of instances when Warren Buffet has discussed Discounting Rates:

Warren Buffett’s Discount Rate used in DCF

[arvind_aries]

Nice discussions and great quality thread to discuss about valuation.

I slightly have a different view on these valuations, what is more important is having the ability to project the future earnings say for next three or five years., Beyond that is going to be really difficult. Based on the future earnings and the past track record of the management we can project the cash flow from operations considering thier working capital management.

Once the above is done, instead of discounting the cash flow to PV, we can assign a PE multiple (growth phase, conservative, realistic and based on industry) to the earnings after 3 or 5 years and arrive at the market price. Then calculate the CAGR between today’s price and price after 3 or 5 years. If the cagr is more than your minimum expected return, invest in stock or else move to the next one.

So here the PE multiple plays a key role, If the investor has really read the past annual reports, if he has fair idea about the business, after seeing competitors business, he should be able to assign a fair PE to the cash flow.

I am mentioning Cash flow from operations instead of free cash flow since, a business which is growing may not have FCF. Depending on the companies business or capital cycle, we can take CFO or FCF and multiply the multiple accordingly.

Please provide your views

[dineshssairam]

If you read that article written by Prof. Bakshi, it shows how P/E multiples can be wrong. He took Nestle’s actual Cash Flows from several years back, built a conservative Valuation with 12% Discounting and 2% terminal growth. It shows that Nestle should have quoted a 50 P/E multiple, but market was only giving it a 25 P/E. Working backwards, he shows that market in fact assigned a very high Discounting rate - 26%!

He concludes by saying that while you should definitely discount at a rate significantly higher than the Risk free Rate, you shouldn’t let it run too much. And the second conclusion being Price Multiples are useless. Price Multiples come from a public opinion poll (The share market) and that cannot equal original research.

[Yogesh_s]

Great work @dineshssairam. Good to see some useful valuation tools being presented here.

I noticed that you are taking capitalization rate as discount rate minus sales growth after 10 years while calculating terminal value. Present value is extremely sensitive to this rate.

If I change sales growth to 7% from 5%, present value jumps from 511 to 782. On the other hand, if I reduce the sale growth to 4%, fair value drops to 449. I am wondering what is the basis for using sales growth as terminal growth rate of free cash flow and how do you determine this sales growth? It is almost impossible to know what will be sales growth of a company 10 years from now but it is the most important input to this model.

I also feel as the company matures and slows down, its riskiness (hence beta) will drop and market should demand a lower discount rate. If you incorporate that in the model, terminal growth rate (or the capitalization rate) becomes even more sensitive.

[arvind_aries]

Precisely that’s the whole reason I am saying discounting cash flows cannot be very appropriate because we do not know what are all the factors market is considering for discounting and we normally take some risk free or bench mark rate which is common for all stocks. A wrong discounting rate can completely change the value of s company.

But relatively, I feel, we can have a fair idea of what PE to assign to each stock.

This idea is not from any text book and this is my thought process…so pls take with pinch of salt

[Yogesh_s]

@arvind_aries

assign a PE multiple (growth phase, conservative, realistic and based on industry) to the earnings after 3 or 5 years

Hi Arvind, this is the most widely used valuation technique I have seen and I feel it is also most abused. There is no reasonable basis for arriving at PE ratio after 3 to 5 years. Most people use current PE ratio and adjust it up or down to arrive at PE ratio after 3 to 5 years. Most of these adjustments are driven by opinion polls and sentiments. Analysts spend great deal of time in determining EPS for next 3 to 5 years and just use an arbitrary multiple to arrive at terminal value when that exit multiple is in fact the most important input in this type of valuation technique .

When EPS for next 3 to 5 years can be accurately estimated, market will already price that and hence current PE will reflect expected earnings growth. Taking such PE ratio and applying it to EPS after 3 to 5 years effectively results in double counting of earning growth.

DCF is logically correct way but it requires many inputs and it is sensitive to some of these inputs which cannot be estimated with a reasonable degree of accuracy.

I have attempted one such model that use dividends as a measure of cashflow rather than free cash flow to address some issues in determining these sensitive inputs. My model is presented here.

Spreadsheet Model for Valuation of a Company

There is a lot of discussion about art of valuation and various techniques to be used in valuations on this site. However, there is no tool available where all these techniques and guidelines can be put together to calculate intrinsic value and expected returns. Several investment books teach discounted cashflow model as the logically correct way to value an asset. However, DCF is difficult to use if you do not use correct inputs. DCF is also highly sensitive to growth rates and discount rates …

[dineshssairam]

@Yogesh_s

I noticed that you are taking capitalization rate as discount rate minus sales growth after 10 years while calculating terminal value. Present value is extremely sensitive to this rate.

No. I use the current growth rate of the economy (Or lower) as the Terminal Growth Rate. Prof. Aswath Damodaran uses the Risk-free Rate as the upper bound, so my model adapts his ideas. Of course, this is iffy, but the concept being if a company grows at a rate higher than the GDP growth rate, given enough time, the company will become bigger than the country itself. That is an absurd picture. So, no company can grow faster than the economy (Here–7%–your proposition) “forever”. The Inflation Rate in an economy is another upper limit that can be used.

I’m not sure what you mean wrt Discounting Rate. The mode has 4 Options: Risk-free Rate, CAPM Beta-based WACC, Bottom-up Beta-based WACC and Opportunity Cost, which is an option that allows you to enter any figure (I just made the figure 50% higher than the Risk-free Rate by default - it can be changed manually).

I have to note here that the model is a combination of numbers and stories. It also has a Diagnosis tool in order to check if the estimates are within bounds. Of course, a Valuation exercise is no exam and nobody is going to grade the assumptions. But by forcing the investor to be objective about the numbers, it appeals to their logical mind. This is far better than typing out a bunch of numbers and have another number thrown out, with no idea of what happened in the middle or if that makes logical sense.

@Yogesh_s

It is almost impossible to know what will be sales growth of a company 10 years from now but it is the most important input to this model.

It is almost impossible to know what will happen to a company tomorrow, let alone 10 years from now. But an investor needs baseline assumptions in order to anchor his purchase price. All Valuation models have these assumptions. Even if you take the best model created by the biggest investments bank in the world, you’d still see a list of assumptions that need to be doled out. As Warren Buffet puts it, “It’s better to be grossly right than to be precisely wrong.” I’d already mentioned the caveats of the DCF and how to make up for them (Very conservative assumptions and a Margin of Safety).

@Yogesh_s

I also feel as the company matures and slows down, its riskiness (hence beta) will drop and market should demand a lower discount rate. If you incorporate that in the model, terminal growth rate (or the capitalization rate) becomes even more sensitive.

As I mentioned earlier somewhere, I don’t believe in Betas. Volatility does not represent Risk. Volatility is Risk for a trader, not an investor. And Risk is not and never should be common for every investor (As is the case with Beta-based WACC calculations). Risk differs from person to person. Only that specific individual can come up with an appropriate Discounting Rate representing that Risk. You may buy a stock at Rs. 150 and I may buy it at Rs. 200 and we’d both be correct. Richard Thaler won the Nobel Prize because he was able to prove that different investors have different Risk tendencies towards different types of investments. Betas don’t allow for all this and that’s wrong.

But I get the general question you are trying to ask: With so many assumptions and some of them very sensitive to the value, how can we be sure that we’re not wrong? We can’t be exactly right, but “grossly right”. How do we do that? By accounting for the elephant in the room – randomness. No assumption in any valuation model is set in stone. It is subject to a lot of variability over time. So, a better way to be sure about the Value is to do a Monte Carlo Simulation (Link to a MCS Excel Add-in), using a variation limit, say 25%. In Excel, this would be “=RANDBETWEEN(-25,25)/100”

Again, going with the same KRBL valuation, I am applying a 25% variation to the following:

1. Sales Growth: Randomized between 3.75% and 6.25% (Careful not to exceed the upper limit of Risk-free Rate)
2. Target OPM: Randomized between 15% and 25%
3. Depreciation/Sales: Randomized between 1.56% and 2.61%
4. Capital Turnover: Randomized between 0.87 and 1.44
5. Discounting Rate: Randomized between 7.29% and 11.94% (I put an IF condition, limiting downside of Discounting Rate to Risk-free Rate, so if a randomized event is 9.55%*(1-0.25) = 7.16%, the model will return 7.29% instead)

This is how the output looks like:

After some Excel maneuvering, you can present this data in a neat diagram:

If you have an understanding of Probability, the above diagram simply shows that the Values at the far end of the graph (Rs. 50-Rs.294 or Rs. 783-Rs.1028) are most definitely incorrect. The ones inside are far better estimates, ranging from Rs. 294 to Rs. 783. Out of those, Rs. 539 is the most likely estimate. This is how you can eliminate the risk of randomness in a DCF (Or any model, really).

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 tool to enable an investor to anchor his price to a probable value, not one 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 use the link I have provided above for the MCS Excel Add-in. If you don’t, no worries. Using the model, keeping in mind its constraints would suffice too.

[dineshssairam]

@arvind_aries

Precisely that’s the whole reason I am saying discounting cash flows cannot be very appropriate because we do not know what are all the factors market is considering for discounting and we normally take some risk free or bench mark rate which is common for all stocks. A wrong discounting rate can completely change the value of s company.

But relatively, I feel, we can have a fair idea of what PE to assign to each stock.

Why should an investor adhere to Mr. Market? An investor makes money by betting against he market and being correct. It does not help an investor to consider market Averages, which includes the Price Multiples like P/E, P/B or EV/EBITDA. Again, I urge you to read this article by Prof. Sanjay Bakshi. It provides well thought-out examples of why Multiples Valuation could be disastrous.

[VP_amit]

I am using the Annual report link in your excel. Have you picked up the sales and debt numbers from there?

It will be great if you could make a step-by-step post on all the entries (inputs) in the model, providing a link to the source of each entry(input).

Thanks
Amit

[dineshssairam]

The problem is, while companies are required to produce TTM P&L figures, they don’t produce TTM Balance Sheet figures in their quarterly reports (At least not in India). If I use all the figures from the 2016-17 Balance Sheet, I would be valuing KRBL as on April 2017. Let’s wait around for April 2018 and I will most definitely produce a step-by-step post on how to Value using the model.

By the way, I made some minor changes in the model and re-uploaded the excel, to make it more consistent. Notable change is calculation of Terminal Year reinvestment based on Marginal Growth and Marginal Return on Capital.

[amit.hardwired]

Dear Dineshji,
keep up the good work. Can you do replicate this exercise for some coMplex firms like pharma cos.(sun pharma,glenmark etc.) and also for some banks?
this would be really helpful. since consumer staples have a slightly visible cash flow as compared to these cos.

thanks in advance,
amit agarwal

[dineshssairam]

Sure, Amit. I might just. But again, let’s wait a month more to get the new Annual figures and then go at it.

As far as Banks are concerned, it is very difficult to value BFSI firms. Some say a Dividend Discount Model can be used, but that is not too accurate as well. This is simply owing to their leverage nature of operations and how they don’t need a lot of reinvestment. BFSI firms are the twilight zone of Valuation.

Theoretically, it is possible to value a BFSI firm using a DCF, but it requires way too much time. You need to audit every P&L and Balance Sheet item in order to arrive at the correct Free Cash Flow figures.

Personally, I myself use a Dividend Discount Model to Value BFSI companies, because it’s just way easier. In fact, now that you mentioned it, I have added a Disclaimer in the first post and linked @Yogesh_s ji’s DDM as an alternative and easier way to Value BFSI companies. Hope it helps.

[dineshssairam]

Made some improvements over the weekend and re-uploaded the Excel.

Changelog

4. Fixed the formula for Terminal Year Discounting logic

Now, Terminal Year Discounting is capped at Risk-free Rate*1.50. All companies’ Cost of Capital usually converges on ‘a little above’ the Risk-free Rate (This is common across all countries). Even if you are considering using your Opportunity Cost as the Discounting Rate, it’d be very difficult for your to earn more than a ‘little above the Risk Free Rate’ as time progresses. Your investments would simply get too big. I read several articles and research papers before committing to this change. Incidentally, this coincides with @Yogesh_s ji’s logic of allowing for lesser Risk in the Terminal Year (Although I still don’t believe in the CAPM mumbo-jumbo).

5. Added entries and formulas to calculate the Value of outstanding Stock Options using the Black-Scholes-Merton Option Pricing Model

This was long due. I was putting this off because of the sheer amount of careful inputs required. A wrong bracket and the entire formula could return a very wrong number. messing up the Value. It was especially difficult because I had to contain the entire length of the BSM Option Pricing Model into a single cell. Just for fun, this is how the formula looks like:

=IFERROR((((NORMSDIST(LN((H2/B12)/F12)+(B11+((J12^2)/2))/(((J12^2)^0.5)(H12^0.5))))(H2/B12))-((EXP((-B11)*H12))*F12)*D12),0)

6. Added attribution text at the end

I also added an attribution text at the end, recognizing Prof. Aswath Damodaran as the inspiration for building and improvising this Valuation tool.

[dineshssairam]

Sorry to be posting thrice in a row. But over the weekend, I have, dare I say ‘perfected’ the model (Well, sort of). You won’t be seeing many changes to the model here on out. The key addition this time is the ‘Capital Conversion’ section. It allows for wrongly attributed R&D Expense and Operating Lease to be capitalized. It also allows for the calculation of Market Value of Debt. These were the key missing pieces from Prof. Aswath Damodaran’s original model and I’ve included them.

Besides that, I finally got around to making a step-by-step to-do list on how to use the model to Value a company. If you plan on downloading and using the model, please do not forget to read this:

Numbers and Narratives: A Simple DCF Model for Equity Valuation

[inonix]

Thanks for sharing a fantastic tool. I have one query regarding the excel. I am trying to use it for analysing Cupid Limited.The problem is company has so much cash in bank that Capital TurnOver and RoE is coming as negative in the sheet. Is that intended or an error? Or may be am I doing something wrong?

[dineshssairam]

Are you sure you are considering the correct numbers?

These are the figures I can see in Screener and I’ve calculated Capital Turnover as such:

[inonix]

Formula on B34
Sales/equity+(value of debt+lease expense)-cce&noa+r&d adjustments)
82.11/(11.12+(0+0)-19.04+0.05) = -10.44

[dineshssairam]

You did not add Reserves. Reserves is basically just Equity. Well, Equity is paid up capital and Reserves is Retained Earnings, but they all belong to the Equity Holders.

Adding that, your formula becomes, 82.93/(11.12+40.21-19.04+0.05) = 2.56.

Also, instead of taking a single period’s Ratios for any of the inputs, I would suggest, look at a 3 or 5 years average just to make sure you are not taking a number on the highest end of the scale. It doesn’t hurt to be a little conservative, especially in Valuation.

[inonix]

Thanks, I thought we need to consider only the share capital.Are you suggesting to take 3 year average for all values like sales/income/opm etc?

[dineshssairam]

Yes, if the current Value looks like an extraordinary figure. Just looking at the average should tell you if your assumptions are logical or not. That’s it.