Discounted Cash Flow valuation is the core tool in valuing a company used by Investment Bankers, Equity Analysts and other Financial Professionals everywhere. This blog is going to run through a basic Discounted Cash Flow example in Excel, and the two angles that an Investor can look at a DCF output.
There are two components to a DCF calculation: The Cash Flows and the Discount Rate.
Cash Flows: Can be Interest, Dividends, Free Cash Flow, Principal Repayments, any cash flow that the investor can get paid.
Discount Rate: The required rate of return that the Investor demands on its Investment.
These two components help determine the Net Present Value of the Investment.
We are going to set up a basic Discounted Cash Flow Calculation in Excel using both a full layout and a quicker method.
First of all lets open excel and enter the numbers 1-5 in cells B1:F1 to represent the year numbers for our worksheet as per the example below:
Next we are going to enter the numbers 10, 11, 12.1, 13.31,14.641 in cells B3:F3 to represent the cash flows we are expecting from our investment as per the example below:
Next we will put the labels Year, Cash Flow and Discount Factor in cells A2:A4 respectively as per the example below:
Next, in cell A5 we are going to put the heading Discount Rate, then in cell B5 input the value 10.00% as per the example below:
The next thing we need to do is figure out what our Discount Factors are for each period. Our Discount Factors are numbers that we will divide our corresponding cash flows by in order to calculate their Net Present Value. The Discount Factor is calculated as (1+Discount Rate) to the power of the Period Number.
Go to cell B4 and enter the formula
Then copy cell B4 and paste it into cells C4:F4 as per the example below:
Next we need to calculate what our Discounted Cash Flows are. Enter the heading Discounted Cash Flows in cell A6 then go to cell B6 and enter the formula
Then copy cell B6 and paste this into cells C6:F6 as per the example below:
Lastly we calculate the NPV by summing all the Discounted Cash Flows. Go to cell A7 and enter the heading NPV, then go to cell A8 and enter the formula
As per the example below:
This is the long way of calculating the Net Present Value of a set of cash flows in excel. Luckily, excel has a formula that can speed this process up a bit.
Go to cell A9 and enter the formula
As per the example below:
This formula does exactly the same calculation we have done above but in a much more condensed fashion.
So, now that we have set up our DCF calculation, how do we use it?
In the above example we can use this calculation in two ways. We can either look at this investment and say that based on a Discount Rate of 10.00% the cash flows are worth $45.45... so if I can purchase this investment for less than $45.45 I am getting a good deal (assuming 10% is an appropriate Discount Rate for the investment), or we can say that based on a purchase price of $45.45... I will effectively be receiving a 10% rate of return on this investment, and then I can make a decision to invest or not based on whether I am happy with that return.
In this case we could then change our Discount Rate to 20% if that was our required return, and determine what price we would be willing to pay for these cash flows:
In the example above we can see that if we require a 20% return then our NPV for the above cash flows is only $35.28 compared with $45.45 before when our discount rate was 10%.
We hope you have enjoyed our Introduction to DCF valuation.