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
Similar Products
Other customers were also interested in...
Waterfall Profit Distribution Model (up to 4 Tiers...
We are introducing our 4-Tier Waterfall Profit Distribution Model. The waterfall profit distribution... Read more
Budget Template in Excel – Analysis in 4 Ste...
The Purpose of the 4 Step Budget Analysis Spreadsheet Template is to offer a user-friendly solution ... Read more
Private Equity Fund Model (Investor Cashflows)
Private Equity Financial Model to analyze fund cashflows and returns available to Limited Partners (... Read more
Advanced Financial Model with DCF & Valuation
General Overview Advanced Financial Model suitable for any type of business/industry and fully cu... Read more
All My Financial Models, Spreadsheets, Templates, ...
Lifetime access to all future templates as well! Here is a set of spreadsheets that have some of the... Read more
3 Statement Financial Modeling with DCF & Rel...
Financial Modeling Tutorial guides user via step by step approach on how to build financial models w... Read more
Startup Company Financial Model – 5 Year Fin...
Highly-sophisticated and user-friendly financial model for Startup Companies providing a 5-Year adva... Read more
Due Diligence P&L – Exhaustive Revenue a...
Model for in depth understanding of high level profit and loss and revenue analysis. Big-4 like chec... Read more
Franchisor Licensing: Financial Model with Cap Tab...
Build up to a 10 year financial forecast with assumptions directly related to the startup and operat... Read more
Divestiture (Spin Off) Financial Projection Model
User-friendly financial model to project and analyse the financial outcomes (valuation, projected fi... Read more
Reviews
Amazing templetes, recommended, its funtional, the format are very comprensive and works very good.
Help other customers find the most helpful reviews
Did you find this review helpful?
Super nice DCF valuation spreadsheet, very practical and easy to use!
Thank you so much for sharing
21 of 52 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
As a student like me, it’s really hard to find good and reliable templates. However, this site really gives me the best easy-to-understand models, that’s why I like the site. Thank you for the free template!
26 of 55 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
An excellent introduction to the genre. Highly recommended. I this is just the free version, I cannot wait to see the purchased one.
29 of 53 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
Thanks and best regards
Thanks and best regards
Thanks and best regards
Thanks and best regards
Thanks and best regards
116 of 376 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
Really well laid out easy to use template/model. Was very helpful.
312 of 510 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
if company’s financial debt tenor spread for 15 years , how can we deduct the same from the DCF while valuation?
216 of 433 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
Excellent Template. It covers all the aspects of the company on which the valuation is carried out for. I really appreciate the efforts spent in making this template. It is easy to understand and easy to comprehend the results based on the information that is used in the DCF calculation.
I would appreciate It if another option was also included in addition to Multiples of EBITDA, the Growth to perpetuity percentage for the terminal valuation.
256 of 468 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
-
Good news! We have updated the model and it now has 10 methods to calculate the terminal value.
30 of 60 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
Excellent templates..
240 of 461 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
You must log in to submit a review.