Calculating Intrinsic Value in Excel. Below we will provide examples of how to calculate the intrinsic value in Excel using the two methods described above. In the screenshot below, you can see how this approach is taken in Excel. Intrinsic Value Calculation Formula FVx = Net cash flow (inflow or outflow) for the j th period (for the initial “Present” cash flow, x = 0 d = Discount Rate – Annual rate of interest or Inflation p = Number of periods to be included.
Example-- Let’s say you want to interested to invest in Maruti Suzuki.
The current share price of Maruti is Rs 7463.95(Sept 2018). During the trailing twelve months, the EPS of the company was Rs 260.86.
The first thing you need to do is to find the projected growth. The analysts are saying that Maruti will grow at a rate of 16.56% per annum for the next five years. (Source- Yahoo finance - Go to the stock page and check the ‘Analysis’ tab)
Next step is to find the terminal growth rate. Let’s say that you researched and found out that its terminal growth rate is 2%. Finally, you need to find the return rate. Assume you calculated rate of return using the CAPM model and it turned out to be 10%. Overall, here are the values to be used in the calculations:
Intrinsic Value Calculator Excel File
- Earnings per share = Rs 260.86
- Return rate (%) = 10%
- Growth rate (%) = 16.56%
- For the period (years) = 5
- Terminal growth rate (%) = 2%
From the above calculations, you will get the intrinsic value of Maruti Suzuki equal to Rs 5997.98.
Intrinsic Value Per Share Calculator
However, you also need to have a margin of safety on your calculations. After all, the future of any company is unpredictable and these calculations are made on few assumptions. Therefore, let's consider a margin of safety of 15% on the calculated value.
Intrinsic Value Stock Calculator
Intrinsic Value Calculator Excel Download
Overall, the intrinsic value per share for Maruti after the margin of safety of 15% is Rs 5098.28. (Quick note: The higher the margin of safety, the safer is the investment.)
As the fair value of the Maruti is lower than the current market price, it may be overvalued right now.