Mrinmoy Roy
When purchasing a new share from a secondary market, calculating the intrinsic value of the share is a mandate. Without having a proper idea about the intrinsic value of a share, it’s quite risky to invest in the share market. Thus, today I will show you how to calculate the intrinsic value of a share in Excel.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
When trying to determine the intrinsic value of a share, there are a number of factors to consider.
- Company’s Financial Stability: You can determine it by looking at the company’s balance sheet and determining if it has enough cash on hand to cover its liabilities.
- Earnings Potential of the Company: To calculate this, look at the company’s past earnings and project what its earnings might be in the future.
- Company’s Dividend History: This can be looked at by looking at the company’s past dividend payments and determining if it is likely to continue paying dividends in the future.
The intrinsic value of a share is the sum of all of these factors. When considering whether or not to buy a share, an investor needs to determine if the share is trading at a price that is below its intrinsic value. If it is, then the share is considered to be undervalued and is a good candidate for purchase.
If the share is trading at a price that is above its intrinsic value, then it is considered to be overvalued and is not a good candidate for purchase.
Gordon Growth Model (GGM)
The Gordon Growth Model is a popular method of estimating the intrinsic value of a share.
The model is based on the assumption that a share is worth the present value of all future dividends. This is known as the Dividend Discount Model.
The Gordon Growth Model is a relatively simple model to use. All you need to know is:
- Current Dividend per Share: It refers to the most recent dividend paid by the company.
- Expected Growth Rate of Dividends: It is a bit more tricky to estimate. A good starting point is the company’s historic growth rate. However, this may not be representative of the future, so some judgment is necessary.
- Required Return on Equity: The required return on equity is the minimum return that investors would require to invest in the company. It is also known as the Discount Rate.
Once you have these inputs, you can use the Gordon Growth Model to calculate the intrinsic value of a share as follows:
Intrinsic Value Per Share = Expected Dividend Per Annum/(Required Return on Equity - Expected Growth Rate)Where,
Expected Dividend Per Annum = Current Dividend Per Share x (1 + Expected Growth Rate of Dividends)Suppose, you have the following data about a share.
- Expected growth rate is 8%.
- Required return on equity is 25%.
- Current dividend per share is $0.77.
To calculate the Expected Dividend Per Share,
❶ Insert the amount of the Expected Growth Rate in cell C4.
❷ Then insert the Current Dividend Per Share in Cell C6.
❸ After that, insert the following formula in cell C8.
=C6*(1+C4)
❹ Now press ENTER.
You will get the amount of Expected Dividend Per Annum in cell C8.
Read More: How to Calculate Dividend Per Share in Excel (with 3 Easy Examples)
Now it’s time to calculate the Intrinsic Value of a Share. To do that,
❶ Insert the following formula in cell C10.
=C8/(C5-C4)Here,
- C8 is the expected dividend per annum.
- C4 is the expected growth rate.
- C5 is the required return on equity.
❷ Then press the ENTER button.
Now you will get the amount of the Intrinsic Value Per Share in cell C10.
In this scenario, the intrinsic value per share is $4.89.
Read More: How to Calculate Market Share in Excel (4 Related Examples)
Intrinsic Value Calculator
You will get an intrinsic value of a share calculator in the second sheet of the attached Excel file.
In that calculator, all the formulas are already there. You just need to input the values of the expected growth rate, required return on equity, and current dividend per share. You will get the Intrinsic Value Per Share amount in cell C10.
Conclusion
To sum up, we have discussed steps to calculate the intrinsic value of a share in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
