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 is the investment beneficial.

Let’s look at the discounted cash flow model in practice.

Formula of Discounted Cash Flow Model

  CF1 CF2  CFn Pn
Discounted Cash Flow=—-+—-……………..+—-+—-
  (1+r)1 (1+r)2  (1+r)n (1+r)n

Where,

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:

NPV Formula

This formula is specifically designed for cash flows received at regular intervals. Intervals may be annual, semi-annual, quarterly, monthly, etc. The formula is –

= NPV (discount rate, series of cash flows)

NPV Excel Formula

Continuing our previous example of Company A, if we want to find the discounted cash flow in excel, we have to put the formula –

=NPV(10%,2.00,2.10,22.20)

& we will receive the answer = 20.23.

But cash flows are not always so consistent, so 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)
2.0031/12/2018
2.1030/12/2020
3.2031/12/2023

Thus we will feed the data in excel in the following manner

Suppose the following table is an excel spreadsheet

 AB
12.0031/12/2018
22.1030/12/2020
33.2031/12/2023
4Discount Rate10%
5Present Value=XNPV(B4,A1:A3,B1:B3)

This formula will give us the value of USD 5.72

XNPV Excel Formula

The calculation is just one part of financial modeling. It is extremely important to understand how to interpret the data that comes up from financial modeling. Following is a small example of how present value derived from discounted cash flow is interpreted:

Discounted Cash Flow Model

Interpreting the Example of Discounted Cash Flow Model

Let us interpret the above Example of Company A

Let’s consider three situations

Today’s market price of a share of company A is USD 18.00

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 it as it will give a higher rate of return than the required rate of return. This will be profitable to the investor.

Today’s market price of a share of company A is USD 22.00

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.

Today’s market price of a share of company A is USD 20.23

This is a no-win, no, loose situation. Whatever the investor is investing, he gets 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. is 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 mistakes at every step. The flip side is that because of such detailed analysis, the analyst may become overconfident in their forecast & might miss obvious red flags. We must consider all these factors before using the discounted cash flow model.

Continue reading – Present Value of Uneven Cash Flows.



Sanjay Borad

Sanjay Bulaki Borad

MBA-Finance, CMA, CS, Insolvency Professional, B'Com

Sanjay Borad, Founder of eFinanceManagement, is a Management Consultant with 7 years of MNC experience and 11 years in Consultancy. He caters to clients with turnovers from 200 Million to 12,000 Million, including listed entities, and has vast industry experience in over 20 sectors. Additionally, he serves as a visiting faculty for Finance and Costing in MBA Colleges and CA, CMA Coaching Classes.

Leave a Comment