DCF Model in Excel: How to calculate Discounted Cash Flow?

Today, we’ll explore one of the key financial tools: Discounted Cash Flow (DCF). What is DCF? How to build DCF Model Excel structures? Which formulas to use? All included here with examples and real life case studies.

Table Of Content

1. What’s DCF Model?
2. Someka DCF Calculator
3. What’s DCF Formula?
4. How do you calculate DCF in Excel?
5. How to make your own DCF Model?
6. Where to use DCF Model Valuation?
7. What’s the difference between NPV and DCF?
8. Conclusion

DCF helps in estimating the value of an investment based on its future cash flows. Let’s start with the definition of this method, which is widely used by finance professionals and investors.

1. What’s DCF Model?

The Discounted Cash Flow (DCF) model is a valuation method, used to estimate the value of an investment based on its expected future cash flows.

So, this model calculates the present value of expected future cash flows using a discount rate. As a fundamental concept in finance and investing, the DCF model Excel helps in making informed investment decisions. It is particularly useful in valuing companies, projects, or assets.

So, this principle states that a dollar today is worth more than a dollar in the future due to its potential earning capacity.

Therefore, the model reflects the time value of money, a core principle in finance.

2. Someka DCF Calculator

We have created a unique template to ease your DCF valuations. This DCF Calculator Excel Template will let you to input your cash flow and then analyze your discounted cash flow valuations.

– This is the dashboard section of DCF Calculator Excel Template by Someka –

So, it’s user-friendly and tailored for both professionals and beginners.

How to calculate DCF with Someka template?

Firstly, There are three sections in this tool to calculate Excel DCF model: 1. WACC, 2. Cash Flows, 3. Charts

• Step 1: Calculate WACC

WACC (Weighted Average Cost of Capital) is the calculation of a company’s cost of capital, proportionately weighing its use of debt and equity financing, it take into account capital costs from all sources, such as common stock, preferred stock, bonds, and other debt types.

This template offers you two different equity cost calculation methods: CAPM and Actual Dividends.

1. First, please select the Equity Cost Calculation Method in the Assumptions table and define the income tax rate. The tax rate will affect both WACC and the free cash flow calculations.
2. Then, if you chose using CAPM for calculating the cost of equity, you need to enter the parameters of the model like Risk-Free Return Rate, Market Equity Risk Premium; Beta and Size Risk Premium.
3. Lastly, fill in historical data for Equity Value, Dividends, Market Value of Debt, and Interest Expenses.

Now WACC will be calculated as the average cost of equity and debt, weighted on equity and debt market values respectively.

• Step 2: Input income statement data

Firstly, you should enter historical financials from the income statement along with forecasted figures for the current year.

Thus, you should input at least three sequential years of data, including the most recent forecast.

• Step 3: Calculate the discounted cash flows

Now the present value of expected cash flows for the next 10 years is arrived at by applying WACC. Thus, the terminal value represents the present value of cash flows beyond the 10-year period.

Key Featues of Someka Discounted Cash Flow Template:

• Ready-to-use with automated calculations
• On-sheet instructions to apply DCF
• Optional calculation methods
• Editable and printable
• Sleek-design worksheet

3. What’s DCF Formula?

The DCF formula is central to the discounted cash flow model. It calculates the present value of an investment’s future cash flows. The formula is:

• Cash Flow_Year n: The cash flow in the year n
• Discount Rate: The rate of return used to discount future cash flows

The discount rate often reflects the risk associated with the investment. The formula sums up the present values of all future cash flows.

This total sum is the estimated present value of the investment. Understanding this formula is crucial for anyone performing a DCF Model Excel analysis.

4. How do you calculate DCF in Excel?

Now, it’s time to make this calculation in Microsoft Excel.

Firstly, calculating DCF in Excel involves a few straightforward steps:

• Step 1: List all projected future cash flows in a column.
• Step 2: Determine your discount rate based on investment risk.
• Step 3: Use the NPV Function of Excel to bring cash flows to back to present values
• Step 4: Add the initial investment cost, usually as a negative value, to calculate the total DCF

Excel’s built-in functions make these calculations easy and accurate. Regular practice in Excel enhances proficiency in performing DCF analyses.

DCF Model Excel Calculation Example:

As we have seen the steps of calculating DCF in Excel, now we’ll assume a project with projected cash flows over five years and a specific discount rate.

Our assumptions:

• Initial Investment (Year 0): -$100,000 (negative because it’s an outflow)
• Projected Cash Flows for Years 1 to 5: $20,000, $25,000, $30,000, $35,000, and $40,000
• Discount Rate: 10%

Solution:

For the Discounted Cash Flow (Column D), starting from Year 1:

This formula calculates the value in cell C4 divided by the sum of one plus the discount rate, raised to the power of year number.

So, this formula is used for calculating the discounted cash flow for the first year in a DCF model. It adjusts the projected cash flow in cell C4 to its present value, considering a discount rate of 10%. You can also use NPV function here.

Afterwards, we will copy this formula for the next years. Then, we’ll calculate the total DCF as the sum of all the discounted cash flows from Year 0 to Year 5.

This simple model will give you the net present value of the cash flows from the project, considering the time value of money at a given discount rate.

5. How to make your own DCF Model?

Creating your own DCF model in Excel requires a systematic approach:

– This image is from Someka’s Excel Discounted Cash Flow Template –

• Firstly, you should start by gathering financial data, like cash flow projections and discount rates.
• Secondly, you will input this data into Excel in an organized manner. Use separate columns for years, projected cash flows, and discount rates.
• Then, you will apply the DCF formula to calculate the present value of future cash flows. Here, you can incorporate Excel’s formulas, such as NPV and PV, for accuracy.

Lastly, you may customize your model by adding variables like growth rates or variable discount rates. A well-structured DCF model in Excel is a powerful tool for financial analysis.

6. Where to use DCF Model Valuation?

The valuation of the DCF model is flexible and useful in a range of financial situations. Firstly, it is widely utilized for company valuation in corporate finance. Also, businesses use it to estimate the costs associated with undertakings, investments, or purchases.

DCF is a helpful tool in personal finance for assessing stock investments. It is used by investors to calculate a stock’s fair value based on anticipated future cash flows. Additionally, DCF Model Excel structures are also used by real estate investors to evaluate real estate investments.

Let’s list the main application areas of DCF Models:

• Investment decisions
• Company valuation
• Corporate finance
• Budgeting
• Real estate investments
• Stock investment assessments
• Financial Planning

Anyone who makes financial decisions will benefit from knowing the DCF model.

7. What’s the difference between NPV and DCF?

Net Present Value (NPV) and Discounted Cash Flow (DCF) are closely related concepts, but they have key differences.

NPV is a calculation used to determine the value of an investment by discounting future cash flows to their present value.

• NPV provides a single figure representing the net value of an investment today.
• DCF, on the other hand, is a broader valuation method that involves calculating the NPV of future cash flows to determine an investment’s overall value.

While NPV is a part of DCF analysis, DCF encompasses a more comprehensive evaluation, including projections of cash flows and assessments of the risk reflected in the discount rate.

8. Conclusion

To sum up, a key instrument in financial analysis is the DCF model. Taking the time value of money into account, it helps in assessing the value of investments.

Excel DCF model building is a must-have skill for investors and finance professionals. So, whether a finance student or a investment banking candidate, it’s always a positive sign to have powerful Excel skills in addition to financial knowledge.

Recommended Readings:

How to Create a Profit and Loss Statement in Excel? Step-by-Step Guide

How To Create a Cash Flow Statement in Excel?

Excel IRR Formula: How to calculate Internal Rate of Return in Excel?