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)`

`=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.

Read More: How to Calculate Payback Period in Excel

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:

• The DCF formula is sometimes difficult to perform. The data for DCF analysis is very difficult to obtain.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun 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... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF