How to Apply the Discounted Cash Flow Formula in Excel

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.

This is the formula:

DCF=CFt/(1+r)t

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) and the net present value (NPV) have common objectives.

The NPV formula shows their difference.

=NPV(discount rate, series of cash flows)

In the formula, all cash flows received are distributed in equal time or periods: years, quarters or months.

The DCF formula is applicable to different periods of time.

In the sample dataset the discounted cash flow (DCF) formula is used to calculate the free cashflow to firm (FCFF) and the free cash flow to equity (FCFE).

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

Enter this formula in C11 to calculate the Total amount of equity and debt.

=C8+C9

Press Enter.

Enter this formula in C12 and press Enter to find the Cost of Debt.

=C6*(1-C7)

Go to the Dataset worksheet.
Enter this formula in C13 to calculate the Weighted Average Cost of Capital (WACC).

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

Press Enter.

In a new worksheet, enter each period of time in B5:B9.
Use this formula to calculate FCFF for each year in C5:C9.

FCFF = Cash Flow From Operations + Interest Expense * (1 – Tax Rate) – Capital Expenditures (CAPEX)

Enter the value of WACC in C11.
Enter the DCF formula in C12.

=C5/(1+C11)^B5+C6/(1+C11)^B6+C7/(1+C11)^B7+C8/(1+C11)^B8+C9/(1+C11)^B9

Press Enter.

This is the final output of FCFF for the total time period with the DCF formula.

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

Add the Interest Expenses from the previous output in D5:D9 in a new worksheet.

Enter this formula in E5 to find FCFE for the 1st year.

=C5-D5

Use the AutoFill to calculate FCFE for each year in D6:D9.

Enter the value of Cost of Equity from the Dataset in C11.
Use the DCF formula in C12 and press Enter.

=E5/(1+C11)^B5+E6/(1+C11)^B6+E7/(1+C11)^B7+E8/(1+C11)^B8+E9/(1+C11)^B9

This is the output.

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

Pros:

It is an extremely detailed process that requires information on the growth rate, equity and overall balance sheet of a year.
The DCF formula helps to find 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: