What Is Intrinsic Value? Download Stock Intrinsic Value Calculator FREE

Intrinsic Value

This user guide will not demonstrate how discounted cash flow (DCF) or discounted earnings per share (EPS) valuation works. We will not be going through DCF’s complex formulas nor explain the meaning and rationale behind the calculations.

Investopedia has covered well about the method which you can read about it here.

Download DCF spreadsheet from below of the post.

The spreadsheet which you have downloaded is supposed to be “Plug and Play”. Enter the data, and voila… you get the intrinsic value of the stock.

Let’s Get Started!

Once you opened the spreadsheet, you will see 2 tabs – Discounted EPS and Discounted Cash Flow. As a rule of thumb, growth companies usually have higher earnings growth while more established or blue-chips companies have more consistent cash flow growth.

You will need to decide whether the discounted EPS or CF is more suitable for use (based on the financial numbers) If   the   company’s   earnings   or   cash   flow   is   highly   inconsistent,   then   this calculator is NOT applicable.

Below are some terms used in the spreadsheet which you will come across:

CAGR = Compounded Annual Growth Rate
FV = Future Value (Final Year EPS or Cash Flow)
PV = Present Value (First Year EPS or Cash Flow)
DF = Discount FactorDV = Discount Value

Discounted EPS

Step 1:
1.1  You will need to enter the data into the orange boxes.

Current Year
Current EPS
EPS CAGR
Discount Factor

User Guide Modified 0

Step 2:
2.1  To help you get the EPS CAGR, there is a CAGR calculator on the right hand side of the spreadsheet.

2.2  For illustration purpose, I am using BreadTalk Group Ltd as an example. We will use BreadTalk last 5 years EPS as an indication of its earnings growth.

2008: 0.02760
2009: 0.03940
2010: 0.04002
2011: 0.04118
2012: 0.04263
(data from shareinvestor.com)

Final Year EPS = 0.04263 (FV)
First Year EPS = 0.02760 (PV)

2.3    So,   enter   the   above   two   figures   and   n=4   (2008-2012)   into   CAGR calculator:
User Guide Modified 1

2.4  You will find that the earnings grew by about 11.5% annually for the past 5 years.

2.5  So, we will assume the earnings will continue to grow at 11.5% over the next 10 years.

2.6  Then key in all the data as follows:
User Guide Modified 2
2.7  In Singapore’s context, you could use CPF’s 4% risk-free interest rate as the Discount Factor (DF).

2.8  If you enter all the data correctly, it will auto-calculate and will arrive at the intrinsic value of the stock at the bottom of the spreadsheet:
User Guide Modified 3
2.9  In this BreadTalk example, the intrisic value is at 0.638

Discounted Cash Flow

Step 3:
3.1  Basically, it’s very similar to Discounted EPS model as well. Instead of using earnings, we are using cash flow of the company as an indication.

3.2  You will need to enter the following data into the orange boxes:

Current Year
Cash Flow
Cash Flow Growth Rate
Discount Factor
Current Share Price
No. Shares Outstanding

3.3  For illustration purpose, we are using Osim International Ltd as an example. We will use its last 4 years’ cash flow as an indication:

Net Cash Generated from Operating Activities:

2009: 65.2M
2010: 93.5M
2011: 99.5M
2012: 103.1M
(data from shareinvestor.com)

Final Year Cash Flow = 103.1M
First Year Cash Flow = 65.2M

3.4  So, enter the above two figures and n=3 (Year 2009 to 2012) into CAGR calculator:
User Guide Modified 4
3.5  You will find that the cash flow grew by about 16.5% annually for the past 4 years. So, we will assume the earnings will continue to grow at 16.5% over the next 10 years.

3.6  Then key in all the data as follows:
User Guide Modified 5
3.7  In Singapore’s context, you could use CPF’s 4% risk-free interest rate as the Discount Factor (DF).

3.8  Lastly enter the no. of outstanding shares of 723.57M (data from shareinvestor.com)
User Guide Modified 6
3.9  If you enter all the data correctly, it will auto-calculate and will arrive at the intrinsic value of Osim’s stock at $2.80.

Now, download and use this stock intrinsic value calculator free from below and use it. You may distribute this stock intrinsic value calculator free to your friends and relatives, but dont forget to refer them to our site.

Author Bio

Indrajit Mukherjee

Indrajit is a professional blogger and Trading System developer, Amibroker expert, Wordpress expert, SEO expert and Stock market analyst. Trading since 2000, he has started the journey of StockManiacs.net on 2008. He follows Indian and world stock markets closely.

Leave a Reply