Table of Contents
- 1 Discounted Cash Flow Model – Understanding
- 2 Logic behind Discounted Cash Flow Model
- 3 Formula of Discounted Cash Flow Model
- 4 Example of Discounted Cash Flow Model
- 5 How to Calculate Discounted Cash Flow in Excel
- 6 Interpreting the Example of Discounted Cash Flow Model
Discounted Cash Flow Model – Understanding
The discounted cash flow model, also known as the present value model, estimates the intrinsic value of a security in the form of the present value of future cash flows expected from the security. In the process, a discounted cash flow model estimates the future cash flow of security & discounts them using an appropriate discount rate to arrive at a present value.
The present value derived from this model is then used to assess the potential of an investment.
Logic behind Discounted Cash Flow Model
The discounted cash flow model follows the basic economic principle that individuals defer consumption – that is they invest in future expected benefits. Logically, the value of the present investment should be equal to or less than the present value of expected future cash flows. Only then, the investment is beneficial.
Let’s look at the discounted cash flow model in practice.
Formula of Discounted Cash Flow Model
|Discounted Cash Flow||=||—-||+||—-||……………..||+||—-||+||—-|
CF = Cash flow
r = required rate of return on the investment (usually weighted average cost of capital)
P = expected value of principle at the end of the investment period
Example of Discounted Cash Flow Model
For the next three years, the annual dividends of a stock of company A are expected to be USD 2.00, USD 2.10, & USD 2.20. The stock price is expected to be USD 20.00 at the end of three years. In this case, for the investor, the cash flow is as good as dividends and stock price at the end of 3 years. If the required rate of return on the shares is 10%, the estimated value of the share as per discounted cash flow model is as follows –
Discounted Cash Flow = CF1/ (1+r)1 + CF2/ (1+r)2 + CF3/ (1+r)3 + P3/ (1+r)3
= 2.00/ (1+0.10)1 + 2.10/ (1+0.10)2 + 2.20/ (1+0.10)3 +20.00/ (1+0.10)3
= 2.00/ (1.10) +2.10/ (1.21) +2.20/ (1.331) + 20.00/ (1.331)
= 1.818 + 1.736 + 1.653 + 15.026
= USD 20.23 = Present value of the stock of Company A
How to Calculate Discounted Cash Flow in Excel
Basically, there are two formulas in excel by which one can find the present value of future cash flows. Formulas are as follows:
This formula is specifically designed for cash flows that are received at regular intervals. Intervals may be annual, semi-annual, quarterly, monthly and so on. The formula is –
= NPV (discount rate, series of cash flows)
Continuing our previous example of Company A, if we want to find the discounted cash flow in excel, we have to put the formula –
& we will receive the answer = 20.23.
But cash flows are not always so consistent, so for this excel has given us one more tool i.e. Time adjusted NPV formula.
Time adjusted NPV Formula
As the name suggests, time adjusted NPV formula is used when the cash flows are received at irregular intervals. The formula for this is –
= XNPV (discount rate, series of all cash flows, date of all cash flows)
Let’s understand with an example. Suppose an analyst is assuming that XYZ Co. will give future dividends in the following order –
|Dividends (in USD)||Date of Receiving Dividends (DD/MM/YYYY)|
Thus we will feed the data in excel in the following manner
Suppose following table is excel spreadsheet
This formula will give us the value of USD 5.72
The calculation is just one part of financial modelling. It is extremely important to understand how to interpret the data that comes up by financial modelling. Following is a small example of how present value derived from discounted cash flow is interpreted:
Interpreting the Example of Discounted Cash Flow Model
Let us interpret the above Example of Company A
Let’s consider three situations
Here we can see that today’s market price is lower than the present value of future cash flow (USD 18.00 < USD 20.23). This means that the stock is undervalued. When a stock is undervalued, it is good to invest in the stock as it will give a rate of return that is higher than the required rate of return. This will be profitable to the investor.
Here we can see that today’s market price is higher than the present value of future cash flow (USD 22.00 > USD 20.23). This means that the stock is overvalued. When a stock is overvalued, it is not a good investment. In this case, the investment will generate a rate of return lower than the required rate of return. In this situation, the investor is making a loss as by investing USD 22.00, he will get USD 20.23 in the present value after three years.
This is a no-win no loose situation. Whatever the investor is investing, he is getting the same in the present value after 3 years. This is not really an investment, but more of a way to park money if one has a huge amount of extra cash. In making this type of investment, the investor is shielding himself from the effects of future depreciation, thereby maintaining the value of his money.
Points to be Carefully Considered in using the Discounted Cash Flow Model
- We must understand that the discounted cash flow model is only as good as the assumptions that an analyst makes. Every input – future cash flow, discount rate, etc. are all based on assumptions about going concern, profitability, inflation, etc. Thus we must understand that discounted cash flow is a tool to find present value, but the accuracy of present value depends on the accuracy of its assumptions. Furthermore, in this fast-moving environment, the assumptions may also change very fast. This results in a loss of quality in the model.
- It is also important to note that one of the biggest challenges in finding the discounted cash flow is that it is painfully complex and detailed. Such details might result in perfect results, but the chances of pitfalls increase. There is a chance of mistake at every step. The flip side is that because of such detailed analysis, the analyst may become overconfident of their forecast & might miss obvious red flags. We must consider all these factors before using the discounted cash flow model.
- Equity Valuation, The CFA Institute, U.S.A