ExcelInsider Team
In order to determine the attractiveness of an investment, it is crucial to calculate the discounted cash flow for that investment. DCF allows you to find out the potential outcomes of a project in today’s values. In this article, we will learn the discounted cash flow formula in Excel so that you can evaluate your potential investment at ease.
➤ Insert this formula in the destination cell: =NPV(0.1,B3:B14)
➤ Replace 1 with your discount rate, and B3:B14 with the range of cash flow other than the initial investment.
➤ Press Enter.
That was a quick way to calculate the discounted cash flow. But we haven’t yet learned how to calculate the discounted cash flow of each year. Moreover, the function we used in the key takeaways was for NPV, and you might be intrigued to know how we used it for calculating DCF. To learn all of those things and more, continue reading the article.
What is Discounted Cash Flow?
Imagine you have an investment of 1 million dollars. The project you are investing in will give you some return each year for 10 years. However, because of the time value of money, the amount you receive in the future will have a degraded value of 10% each year compared to today’s value of money.
Therefore, if you receive 100$ in the next year, it will be the same as 91$ in this year, and 100$ received in the year after that is the same as 83$ this year. For your project, the amount of money you receive might differ, and you need to calculate the actual value of that cash flow according to today’s value of your money. Calculating the DCF will show you how much the project will return in terms of the present value of money.
Calculating Discounted Cash Flow Using NPV
Excel provides a dedicated function for calculating the net present value (NPV). The difference between NPV and DCF is that DCF does not count the initial investment, and NPV does. If we use the NPV function from Excel and exclude the initial investment, we will get the gross discounted cash flow.
For this tutorial, we have an investment that requires an investment of $10000. Each year, there will be an output from the investment for a total of 12 years. We are going to calculate the discounted cash flow from this dataset.
➤ We have the cash flows in column B. Let’s write the formula in the B15 cell where the gross DCF will show up:
=NPV(0.1,B3:B14)
➤ Press Enter to get the result.
Calculating Yearly Discounted Cash Flow
The previous method calculates the full discounted cash flow at once. But you might want to know the discounted cash flow of each year. Follow the steps below to do that:
➤ Add two columns for Discount Factor (10%) and Discounted Cash Flow ($).
➤ For the rows in the Discounted Cash Flow ($) column, set the Number Format to Currency. To do that, select all the cells in the column first.
➤ Then, from the Home tab, go to the Number section, and open the dropdown to select Currency.
➤ In the C3 cell (as it belongs in the column of discounted factors), write this formula:
=1/(1+0.1)^A3
➤ Autofill from C3 to C14 cell in the column.
➤ While you have C3:C14 cells selected, keep pressing the decrease decimal icon from the Number group of the Home tab until the decimal places are no more than 4. If the decimals are already lower than that, don’t worry about it and don’t reduce the decimals. This step is somewhat for cosmetic purposes.
➤ In the D3 cell, write this formula:
=B3*C3
➤ Autofill the rest of the cells in the column.
➤ In D15 cell, write this formula to calculate the full discounted cash flow:
=SUM(D3:D14)
➤ You might want to change the formatting of D15 cell to currency as well.
Using Variable Discount Rate to Calculate Discounted Cash Flow
For calculating the present value, we usually take a static discount rate, as this rate is extracted from the potential outcome of investment in an alternative project. However, the discount rate can vary if you are, for example, calculating via the inflation rate or a complex alternative investment. In that case, follow the steps below to calculate the discounted cash flow:
➤ Create the same Discount Factor and Discounted Cash Flow ($) columns for calculating DCF.
➤ In the D3 cell, write this formula to calculate the discount factor and autofill other rows:
=1/(1+C3/100)^A3
➤ Write the formula in the E3 cell and fill up the column to calculate the yearly discounted cash flow:
=B3*D3
➤ Write this formula in E15 cell to sum up the discounted cash flow:
=SUM(E3:E14)
Frequently Asked Questions
Is DCF the same as NPV?
No. However, DCF and NPV are very close. NPV includes the initial investment in the calculation, but DCF does not. That is the difference between those calculations.
Are DCF and IRR the same?
No. DCF shows you the actual value of the investment for a specific number of years. IRR calculates the number of years the project will take to break even according to the NPV of the project.
How to calculate monthly DCF?
If your project has a yearly rate, the monthly calculation of DCF is not directly possible. However, you can divide the yearly DCF by 12 to get the monthly DCF, which will be the same for the whole year. If you have the discount rate for each month, you can use the monthly discount rate formula to calculate the DCF. The formula goes like this:
=1/(1+0.1)^(1/12)
Here, we are using a 10% discount rate (indicated by 0.1), and a monthly discount (indicated by 1/12)
Which is better, NPV or DCF?
There is no straight answer to this question, as different investors will prefer different methods. Basically, NPV is the amount of money you will have after the whole project ends, but in present value. DCF is the amount of money the project has to offer you in its lifetime in terms of its present value. It depends on you, as an investor, to choose one for your case.
What if NPV is 0?
That means that the investment will not make any profit, and it should be avoided. Even if the DCF is higher, having a negative or zero NPV means that the investment is not suitable.
Wrapping Up
In this article, we have learned the discounted cash flow formula in Excel. If you have any questions or have a better method of doing this calculation, leave it in the comments below. Download the Excel file used in this article as an example template can teach more than a tutorial ever can. We will be back in another tutorial.
