New Changes in Excel – Discount Rate & Fair Value Uncertainty

In the upcoming of my stock analysis, you will see some massive changes in my excel. The significant changes impact four areas:

  1. Determination of Discount Rate
  2. Determination of Fair Value Uncertainty – The concept is similar to Margin of Safety, but presentation will be very different.
  3. Application of new Discount Rate and Fair Value Uncertainty in DCF valuation
  4. Application of new Discount Rate and Fair Value Uncertainty in Absolute EY% valuation

These changes are based on the research done by the MorningStar. I have been studying the MorningStar’s research for 3-4 years, and I always wanted to incorporate their systematic methodology. The main issue with the MorningStar’s research is their research papers are scattered and not organised properly, and this makes me very hard to study their papers.

One of the very good source of the MorningStar’s research is “The Five Rules for Successful Stock Investing: Morningstar’s Guide to Building Wealth and Winning in the Market”.

The book and research papers didn’t complete few pieces in the puzzle until I read the new book: “Why Moats Matter: The Morningstar Approach to Stock Investing”.

Some changes that I made in the new excel are entirely “not new” because I had been working on them for a year or so. But, I didn’t publish the works to public due to missing pieces in the puzzle. Since I am able to complete the puzzle recently, I am more confident to release the new calculation.

Let me share with you the current issues in the current method of determining Discount Rate and Margin of Safety. The current method of determining Discount Rate and Margin of Safety is conceptually and theoretically correct, and so far working fine. However, the current method came with drawbacks.

  1. Overhead in maintaining some data:
  2. It may not appropriate to use Dividend Yield and Estimated Earnings Growth to calculate Margin of Safety for 5 to 10 years DCF. However, this method works well with Absolute EY% valuation.
  3. Business Risk and Financial Risk – Both risk factors aggregated few risks, such as leveraging and revenue cyclicality. I think I should segregate them.
  4. Many people (including my family members) have difficulties to understand Margin of Safety. I have been thinking how to present Margin of Safety in more friendly way.

New Changes

The MorningStar’s methodology is much more complicated if compare to what I implemented in my new excel. I applied their concepts and principals in my excel.

  1. Introduction of new risk factors
    • Revenue Cyclicality
    • Operating Leverage
    • Financial Leverage
    • IP/Patent Expirations Risk
    • Service Disruption Risk
    • Changing Competition
    • Price Taker in a volatile market
    • Unpredictable Management
    • Questionable Capital Allocation
  2. Determining Uncertainty Risk and default Discount Rate by using Systematic Risk: Revenue Cyclicality, Operating Leverage and Financial Leverage. You can unhide “Systematic Risk Buckets” worksheet. Click here for details. imageimage 
  3. Determining Adjusted Discount Rate by evaluating Nonsystematic/Company-Specific Risk.


  4. Determining Fair Value Uncertainty – For more details, please here, here and here. “Why Moats Matter: The Morningstar Approach to Stock Investing” provides more detailed explanation.


  5. Presentation of Fair Value Uncertainty (alternative to Margin of Safety) in DCF valuation and Absolute EY% valuation
    • Price/Fair Value (%) – If the current price is above fair value, the yield will be more than 100%; if the current price is below fair value, the yield will be less than 100%.
    • Star Rating (to replace Margin of Safety) – I use five buckets of fair value uncertainty: Low (5 Stars), Medium (4 Stars), High (3 Stars), Very High (2 Stars), and Extreme (1 Star), each with a corresponding margin of safety. A 1-star rating indicates that I think a company is significantly overvalued, and I would consider selling it if I happened to own it.
      For situations where our uncertainty is low, meaning our confidence in forecasting the possible outcomes is relatively high, a 5-Star rating is triggered when the stock trades at a 20% discount to fair value. If our uncertainty is very high, on the other hand, we require a 50% discount to fair value to award 5 stars. In between, if uncertainty is medium, the required discount is 30% to award 5 stars, and for high uncertainty, it’s 40%to award 5 stars. So, for example, if we have determined that a reasonable base-case fair value estimate for a stock is 20 per share, we would consider buying at 16 with a low uncertainty rating, 14 with medium uncertainty, 12 with high uncertainty, and 10 with very high uncertainty.


  6. Absolute EY% valuation – I changed “Buy Under” to “Downside”. Honestly, so far, I rarely use “Buy Under” as an indication to buy a stock, but I use it to check possible downside risk.


The list is not exhaustive. Some research papers I downloaded before are either no longer available online, or are secured. I cannot upload these research papers because I received one warning from the document owner before.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s