All Industries, Financial Model, General Excel Financial Models

DCF Model, Discount Rate, Excel, Financial Model, Financial Planning, Financial Projections, Financial Reporting, Free Financial Model Templates, Terminal Value, Valuation, WACC (Weighted Average Cost of Capital)

 

The DCF Valuation method is a form of intrinsic valuation and part of the income approach. The method focuses on cash and not on accounting profits. It includes any effects that impact a company’s cash position before considering any debt or equity financing. The DCF model also captures all the fundamental values drivers, such as the EBIT, WACC, growth rate, and ROCE. With that being said, it is the most used and theoretically sound valuation method for determining the expected value of a based on its projected free cash flows.

How to Calculate Discounted Cash Flow in Excel?

This DCF valuation model free excel template allows users to smoothly understand how to calculate Enterprise Value, also known as the Net Present Value, by discounting the forecasted 5-years Free Cash Flows to their present value using the DCF discount rate.

In understanding the company users like to value, the users are suggested to follow the steps below:

Step 1 – The DCF model requires a discount rate that would be used to discount the future cash flows back to their present value. The discount rate should reflect the company’s risk (or the risk of an asset). This DCF model enables users to estimate the discount rate using the weighted average cost of capital (debt and equity costs weighted) formula.

Step 2 – Properly analyze at least three years of a company’s historical financials and input such data into the DCF model excel template.

Step 3 – Input essential assumptions such as revenue growth, EBITDA, and gross margin rate for the projection of the three-statement model – Income Statement, Balance Sheet, and Cash Flow Statement.

Step 4 – Once the projected three-statement model is complete, Free Cash Flows to Firm for the next five years are automatically calculated and are used as a basis for discounted cash flow model calculation. To arrive at the Free Cash Flows to the Firm, the following are added or deducted from EBIT:

• Adjusted Taxes (the applicable income tax rate applied to EBIT)
• Add back Depreciation and Amortization as these are non-cash expenses
• Net Change in Net Working Capital needs to count for any changes in the working capital required to run a company
• Deduct CAPEX, one of the major cash outflows
• Terminal Value

Step 5 – Terminal Value represents the value generated from all the expected cash flows beyond the forecasted period, based on a going concern basis. This DCF excel template estimates the terminal value using ten different ways. Users are to input rates, periods, and exit multiple assumptions that would enable the estimation of the terminal value in the following methods:

• Capitalized Earnings
• Gordon Growth Rate
• H-Model
• Exit Multiple – EV/Revenue, EV/EBITDA, EV/EBIT, P/E, and P/B
• One Stage Value Driver
• Two-Stage Value Driver

There are two versions of the DCF Valuation model:

• Advanced – Includes all the terminal value methods, enabling users to further understand and compare various drivers to estimate the value of a company
• Basic – Includes only the Gordon Growth and the Exit Multiple EV/EBITA. This version allows users to value a company simply and quickly.

Step 6 – The discounted cash flow valuation model will then discount the Free Cash Flows to Firm including the estimated terminal value using the discount factor to their present value.

The total net present value is equal to the Enterprise Value. Equity value is obtained by adding the company’s cash and deducting the company’s financial debt. The equity value is the result of your discounted cash flow valuation model.

What makes this DCF Excel Model Unique?

In calculating the companies’ Enterprise Value, it is essential to calculate the Terminal Value as realistically as possible as this helps estimate the value of a business beyond the explicit forecast period. Capitalized Earnings, Gordon Growth, and Exit Multiples are the most common terminal value methods. These methods are helpful and valuable to users who wish to have a quick valuation of their company by using the DCF model discount rate (WACC), one steady growth rate, and competitors’ readily available exit multiples. However, these methods do not reflect reality as it does not consider drivers such as changes in the discount rate, competitive advantage period, and the effect of ROIC (Return on Invested Capital).

This Discounted Cash Flow calculator offers three other terminal values that consider those drivers:

1) H-Model has high growth in the discount rate for a period, and then decreases to a lower rate:

2) One Stage Value Driver considers the changes in Capital Employed and Growth rate:

3) Two-Stage Value Driver reflects the changes in competitive advantage and ROIC:

Please also read our article 10 ways to estimate Terminal Value.

Download now the DCF Valuation Model Free Excel Template

This solid and simple DCF model excel template is formulated in a way that allows users to analyze their company value by varying the input assumptions. The Executive Summary also provides a quick overview of the whole DCF excel template.

Having a Discounted Cash Flow model ready is very useful for explaining a business’s economics and discussing its impact on the valuation. Our team has created two simple yet detailed DCF models, free of charge, to give you a toolset value for a company.

• Advanced Full Free Excel Template (V4.4) – Capitalized Earnings, Gordon Growth Rate, H-Model, Exit Multiple – EV/Revenue, EV/EBITDA, EV/EBIT, P/E, and P/B, One Stage Value Driver, and Two-Stage Value Driver
• Basic Full Free Excel Template (V4.4) – Gordon Growth Rate and Exit Multiple EV/EBITDA