Introduction
Absolute valuation is a method of valuation used for finding whether a stock is undervalued or overvalued. It helps the investors with investment decisions. However, the process of calculating absolute value might be hectic. But, we have made it quite simple for you. This article will guide you through the meaning of Absolute value, the difference between absolute valuation and relative valuation, how to calculate the absolute value of a stock through the DCF model in excel, and lastly, a real-time example of the absolute value of Apple inc.
Buying stocks can be a tricky business. If you invest in a company that isn’t doing well, you could also lose your investment. This article will show you how to analyze a stock and thus, determine if it is a good investment or not. You’ll learn what the value of stock actually means. You will also learn how to determine whether a stock is overvalued or undervalued.
What Is Absolute Valuation?
Absolute valuation refers to a method where we try to find the value of the business. In simple words, Absolute stock valuation and relative valuation help the company finds the intrinsic value of a stock through the cash flows projected for the future years. Whereas, the absolute value is that business valuation method that uses discounted cash flow analysis. Also, you can find the value of an investment or stock through this method. Let’s break it down into simple terms. Assume you are looking out for Apple stock and the share price in today’s date is $164.74. And, when you did the business valuation, the absolute value came out to be $153.62. This indicates that the company is valued more by $11.12. Thus, it also helps to compare how much is the stock over or undervalued.
Formula For Absolute Business/ Stock Valuation
Now that you know what Absolute valuation is, let’s look at the formula to calculate the absolute value of the company. In total, there are two formulas. One is for finding the absolute value of a business while the other formula also deals with finding the absolute value of the stock of a company.
The formula for the absolute value of a business:
Absolute value = ∑ [(CFi / (1 + r)i) + (TV / (1 + r)n)]
where, CFi = Cash flow for “i” year
∑ = Total sum
Tv = Terminal Value
r = rate
You need to calculate the cash flows for the future number of years. Take the total sum of the cash flows and then apply the formula. Once you have calculated the Absolute value for a business, you can also calculate the absolute value of a stock. Just divide the absolute value by the number of outstanding shares and you’ll get the value of the stock.
Popular Absolute Stock Valuation Methods
In financial modeling, there are two methods to calculate for performing an absolute valuation of a stock or a business. The first method is through the DCF model and the second method is DD Model. Let’s have a look at both methods.
Dividend Discount Model (DDM)
The dividend discount model from the name itself says the value of a company derives from the dividends. But, how do we calculate that? Well, for that you need to first find whether the company pays dividends to shareholders or not. If, yes, then is it constant or fluctuating. Basically, the companies that are stable and also have a constant outflow of dividends opt for this method. Besides that, companies that are in the blue-chip zone use this method. Blue-chip companies are basically companies with larger market cap. These also include companies like International Business Machines Corp, Chevron, Johnson & Johnson, etc.
The main reason why some analysts DD model is that the dividends are the actual free cash flow. In simple words, free cash flow is the money left and distributed as dividends. So, when you calculate the present value of these cash flows you’ll also get the worth of the shares present in today’s time. The DDM assumes that the future cash flows are equal to the dividends that will get paid in the future.
Discounted Cash Flow Model
The second method is the calculation of absolute valuation through the DCF model. You might be thinking, why is this second method required, if we can calculate using DDM. Well, not all companies pay dividends to the shareholders and investors. In that case, the DCF model comes into the picture. Here, you calculate future cash flows and then also determine the absolute value of a business and stock. In the DCF model, there are many variations, the suitable one is the Two-stage model. In this type, first, the future cash flows are calculated, and then the terminal value. Besides that, there are certain criteria for selecting this method. Firstly, the company should have stable, increasing, and positive cash flows. Secondly, companies that don’t declare dividends can also use this method.
Absolute Stock Valuation Vs Relative Valuation
Stock valuation is a method to find the value of a stock of any company or many companies. Many companies opt for Absolute valuation and relative valuation. Now that you are aware of the popular methods for Absolute valuation, let’s understand the difference between absolute valuation and relative valuation. Let’s check it out.
Absolute Valuation | Relative Valuation |
Absolute value tries to find the intrinsic value of the company. | Relative value basically tries to compare the company or asset’s worth with other assets or companies. |
There is no such comparison seen in this method. | Here, there is a comparison between other similar companies |
In absolute valuation, DCF and DDM are the popular models. | Different ratios such as the P/E ratio, Price to sales ratio, and P/B are used frequently. |
How To Calculate An Absolute Value Of A Stock In Excel?
In order to calculate the absolute or intrinsic value of a company, you need to follow certain steps. Here, are the following steps for doing an Absolute valuation of a business and thus stock valuation through the DCF method. Let’s check it out.
- Calculate FCFF and Change % for 10yrs: The first step involves the calculation of free cash flow for the approx 10 years. Once, you have calculated FCFF, calculate the Change in FCF. For Change in FCF, the formula is the current year’s FCF minus the previous year’s FCF. Then calculate (Change %), the formula is;
Change % = Change in FCF/ Previous year’s Change in NWC - Find 5YGR and LGTR: The next step is to find the 5YGR for the firm, For that, you need to assign certain weights for the calculation. And thus, use the formula in excel, [=AVERAGE.WEIGHTED(G12:N12, G13:N13). Here, G12:N12 are the Change % values whereas G13:N13 are the weights assigned. For LTGR, try to search the GDP and Inflation values of a certain country. And also apply the same formula you used for 5YGR, and you’ll get two values of LTGR. One will be for GDP and the other for Inflation.
- Calculate Terminal value: Now, you can easily find the terminal value. Calculate Terminal value using both the LTGR. But, before that, you need to also find the future cash flows of the firm. The formula for calculating future cash flows in excel is;
Future Cash flow = CF(i) *(1+ 5YGR)
4. Calculate Enterprise value: The next step is to find the Enterprise value. Calculate Enterprise value for both the Terminal values.
5. Find the Absolute value: Finally, you can now calculate the Absolute value or Intrinsic value of a business.
Example Of Absolute Valuation In Excel
This is an example of the calculation of the Absolute value of a stock in excel. Basically, the method used here is the DCF model. The company taken for calculation is Apple Inc. And, the data is taken from Investing guru. The following are the snippet of the calculations.
1. Calculate Change in FCF, Change % and 5YGR
Step 1: The first step includes the calculation of Change in FCF, Change %, and 5YGR. For Change in FCF, you also need to calculate free cash flow for 10 years. Then, also find the change in FCF.
The formula for Change in FCF is;
Change in FCF = Current year FCF – Previous year FCF
= 121218155201 – 74039478827
= $47178676374
The formula for Change % is;
Change % = Change in FCF/ Previous year FCF
= 47178676374/ 74039478827
= 0.6372097308
The formula for 5YGR in Excel is;
5GYR = [=Average.Weighted(Change % cells, Weights)]
= [=Average.Weighted(G12:N12,G13:N13)]
= 0.3193399313
Here, Weights are, therefore, assigned as per the company’s growth in the few years. In covid 19, the company didn’t do well, so the weight was 1 or 2. Whereas if it does good, you can assign 3 or 4.
2. Find LTGR
Step 2: The second step is to find the LTGR. Now, you can find LTGR using the GDP and Inflation of a country. Basically, here we have taken both the metrics and also calculated the Absolute value. You can easily access the GDP and Inflation from the web (Wikipedia). Take the values of the past 10 years. The weights are again also assigned on the basis of performance.
The formula for LTGR in excel is;
LTGR-GDP= [=Average.Weighted(Total GDP cells, Total weights)]
= [=AVERAGE.WEIGHTED(D4:D13,F4:F13)]
= 0.0218125
Similarly, calculate the value of LTGR- Inflation. Thus, the value comes out to be 0.0161875.
3. Calculate Future Cash Flows
Step 3: The next step is a calculation of future cash flows. It is an important step in the calculation of Absolute valuation.
The formula for future cash flows is;
Future cash flow (Year 1/2023) = Current year FCF*(1+ 5YGR)
= 73126155201*(1+0.3193399313)
= 96478256579
Future Cash flow (Year 2/2024) = Future cash flow of 2023*(1+ 5YGR)
= 96478256579*(1+0.3193399313)
= 127287616407
Likewise, calculate the future cash flows of the remaining years. Thus, now you can calculate the terminal value.
4. Finding The Terminal Value
Step 4: The next step is to calculate the terminal value. You can calculate the terminal value using both LTGR-GDP and also LTGR-inflation. Thus, you will get two different values of terminal value. However, it depends upon you by which method do you want to calculate the terminal value.
The formula for terminal value in excel is;
Terminal Value = Future cash flow of last year*(1+ LTGR)/(WACC – LTGR)
= 292318482233*(1+ 0.0218125)/ (0.08119001459 – 0.0218125)
= $5030434183528
Likewise, calculate the terminal value sing LTGR – Inflation. The value comes out to be $4569829175649.
5. Calculate The Enterprise Value
Step 5: Now, the next stage is to calculate the Enterprise value. Just like Terminal Value, here also you’ll have two different values. Because we will use both the terminal values separately. Here, WACC is used for calculation. But, before that, you need to name the future cash flows in terms of years. The formula basically involves the excel function to calculate enterprise value.
The formula for calculating Enterprise value in excel is;
Enterprise value = [=XNPV(WACC, All future cash flows, dates)]
= [=XNPV(E7,D14:I14,D13:I13)]
= 5931225667427
Likewise, you can calculate using the value through Terminal value 2. The value comes to be $5471014528161.
6. Calculating Absolute Value
Step 6: Finally, the last step involves the calculation of the Absolute value of the company. It is also called an Intrinsic value. Now, that you have two Enterprise values, you will get two Absolute values. However, it depends upon you which value you wish to consider.
The formula for Absolute valuation is;
Absolute/ Intrinsic value of a stock = (Enterprise value – Long term debt + Cash & cash equivalents)/ Outstanding shares
= (5931225667427 – 109106000000 + 34940000000) / 16319440000
= $358.9007752 per share
Likewise, the other value can also be calculated. Eventually, compare the current price of Apple (i.e $164.85) and see the difference. Here, as you can see that the company is undervalued. Thus, you can buy this stock as this is an amazing opportunity to invest.
Why Is Absolute Stock Valuation Important?
Absolute valuation is thus, an important metric to find the value of a business or a stock of a company. Most of the investors also use the DCF model to do the absolute valuation of the company. Likewise, it also helps the analysts to determine whether a stock’s value is more or less. Let’s understand it with a simple example. Assume an analyst tried to do the absolute valuation of ABC Ltd and XYZ Ltd, and the following are the results. While computing the value for ABC Ltd, it was observed that the company’s stock is valued more than its actual price. Whereas in the case of XYZ Ltd, the value of the stock was 10 times more than the actual stock price. So, where should the analyst invest, in an overvalued company or an undervalued company? Well, it would be best if he chose an undervalued company.
An overvalued company will be always in a fear of busting the bubble, therefore the analyst won’t invest in it. Whereas if you are investing in an undervalued company, comparatively, there is less risk. Therefore, Absolute valuation helps in identifying the risks around.
Conclusion
Absolute valuation is also a great technique to find whether the stock’s value is more or less. This article discusses various methods of absolute valuation. Likewise, people might get confused between absolute valuation and relative valuation, but in reality, both are different. Also, we have systematically explained how to calculate the absolute value of a stock in excel. We hope this article solved all your queries related to Absolute valuation and calculation of the DCF model in excel. If you have any questions, comment down below.
