How to Apply Discounted Cash Flow Formula in Excel

The term Discounted Cash Flow is a very common one in the field of finance and accounting. It determines the decision to buy or sell a company in the business sector. Microsoft Excel has made our work easier with the Discounted Cash Flow Formula. It is used to determine the value of a business or security.  It represents the value of an investor and his/her willingness to pay for an investment, with a rate of return on their investment. In this article, we will explore the discounted cash flow formula in excel with 2 ideal examples.


Download Workbook

Download the sample file and practice it.


What Is Discounted Cash Flow (DCF) Formula?

The Discounted Cash Flow (DCF) formula is a valuation method that helps to determine the fair value by discounting future expected cash flows. Under this method, the future cash flows are assumed according to the company’s life or asset which is unlimited. It also includes a discount rate that discounts the aforementioned cash flows to reach the present value. The formula states this:

DCF=CFt/(1+r)t

Here,

CFt = Cash flow in period t (time)

r = Discount rate

t = Period of time (1,2,3,……,n)


Discounted Cash Flow (DCF) vs. Net Present Value (NPV)

The discounted cash flow (DCF) is often mixed with the concept of net present value (NPV). Though both of their objectives are the same, there is a certain difference. The NPV formula in excel will make it clearer.

=NPV(discount rate, series of cash flows)

Here, the formula states that all the cash flows received are distributed in equal time or periods, whether years, quarters or months.

On the other hand, the DCF formula is applicable for different periods of time.


2 Examples of Applying Discounted Cash Flow Formula in Excel

We can apply the discounted cash flow (DCF) formula in excel to calculate the free cashflow to firm (FCFF) and the free cash flow to equity (FCFE) in excel. For this, here is a dataset defining the values of the cost of equity, debt rate and tax rate. It also shows the value of equity and outstanding debt.

Discounted Cash Flow Formula in Excel


Now, let’s see 2 examples below of applying the discounted cash flow formula in excel.

1. Apply Discounted Cash Flow Formula in Excel to Calculate Free Cashflow to Firm (FCFF)

In this example, we will calculate the free cashflow to firm (FCFF) with discounted cash flow (DCF) formula. Follow the steps below:

  • Firstly, insert this formula in cell C11 to calculate the Total amount of equity and debt.
=C8+C9
  • Hit Enter.

Apply Discounted Cash Flow Formula in Excel to Calculate Free Cashflow to Firm (FCFF)

  • Secondly, insert this formula in cell C12 and click on the Enter key to find out the Cost of Debt.
=C6*(1-C7)

Apply Discounted Cash Flow Formula in Excel to Calculate Free Cashflow to Firm (FCFF)

=C5*(C8/C11)+C12*(C9/C11)
  • Click on Enter.

Apply Discounted Cash Flow Formula in Excel to Calculate Free Cashflow to Firm (FCFF)

  • Now, take a new worksheet and insert each period of time in cell range B5:B9.
  • Next, apply this formula to calculate FCFF for each year in cell range C5:C9.
FCFF = Cash Flow From Operations + Interest Expense * (1 – Tax Rate) – Capital Expenditures (CAPEX)

  • Then, insert the value of WACC in cell C11.
  • Finally, insert the DCF formula in cell C12.
=C5/(1+C11)^B5+C6/(1+C11)^B6+C7/(1+C11)^B7+C8/(1+C11)^B8+C9/(1+C11)^B9

Apply Discounted Cash Flow Formula in Excel to Calculate Free Cashflow to Firm (FCFF)

  • Press the Enter key.
  • That’s it, here is the final output of FCFF for the total time period with the DCF formula.

Discounted Cash Flow Formula in Excel

Read More: How to Calculate Present Value of Future Cash Flows in Excel


2. Calculate Free Cashflow to Equity (FCFE) Using Discounted Cash Flow Formula in Excel

In this section, we will calculate the free cashflow to equity (FCFE) with the discounted cash flow (DCF) formula. Here, we will work on the same dataset as above. Let’s see the process below:

  • First, add the Interest Expenses on the previous output in cell range D5:D9 in a new worksheet.

  • Then, insert this formula in cell E5 to find out FCFE for the 1st year.
=C5-D5

Calculate Free Cashflow to Equity (FCFE) Using Discounted Cash Flow Formula in Excel

  • After that, use the AutoFill tool to calculate FCFE for each year in cell range D6:D9.

  • Now, insert the value of Cost of Equity from the Dataset in cell C11.
  • Next, apply the DCF formula in cell C12 and hit Enter.
=E5/(1+C11)^B5+E6/(1+C11)^B6+E7/(1+C11)^B7+E8/(1+C11)^B8+E9/(1+C11)^B9

Calculate Free Cashflow to Equity (FCFE) Using Discounted Cash Flow Formula in Excel

  • Finally, we have our final result.

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)


Pros and Cons of Discounted Cash Flow (DCF) Formula in Excel

The discounted cash flow (DCF) formula is a very popular one, yet has some pros and cons during the work procedure.

Pros:

  • It is an extremely detailed process that requires information on the growth rate, equity and overall balance sheet of a certain year.
  • The DCF formula helps to find out the nearest exact value.
  • It is very helpful to understand present business conditions and predict future investment.
  • The main advantage of the DCF formula is that it calculates the Internal Rate of Return (IRR).

Cons:

  • The DCF formula is sometimes difficult to perform. The data for DCF analysis is very difficult to obtain as it is a lengthy process.

Read More: How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)


Conclusion

Concluding this article with the hope that it was a helpful one for you on the discounted cash flow formula in excel with 2 ideal examples. Let us know your feedback on this. Follow ExcelDemy for more excel blogs.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo