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.

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.

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.

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

1. Using 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.

Secondly, insert this formula in cell C12 and click on the Enter key to find out the Cost of Debt.

=C6*(1-C7)

Following, go to the Dataset worksheet.
After that, insert this formula in cell C13 to calculate the Weighted Average Cost of Capital (WACC).

=C5*(C8/C11)+C12*(C9/C11)

Click on Enter.

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

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

2. Calculating 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

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

Finally, we have our final result.

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.

Download Practice Workbook

Download the sample file and practice it.

Discounted Cash Flow Formula.xlsx

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.

<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!