A Discounted Cash Flow (DCF) model stands as a pivotal financial tool utilised to assess investments by projecting their future cash flows and bringing them to their current value.
It enjoys popularity among investment bankers, financial analysts, and equity researchers for evaluating companies, projects, and various investment opportunities.
This article steps through the process of building a basic DCF model using Excel.
The DCF Formula
CF = Cash Flow Represents the net cash payments an investor receives in a given period for owning a given security (bonds, shares, etc.)
r = Discount Rate For business valuation purposes, the discount rate is typically a firm’s Weighted Average Cost of Capital (WACC). Investors use WACC because it represents the required rate of return that investors expect from investing in the company. For a bond, the discount rate would be equal to the interest rate on the security.
n = Period Number Each cash flow is associated with a time period. Common time periods are years, quarters, or months. The time periods may be equal, or they may be different.
Example:
DCF vs. NPV
The total Discounted Cash Flow (DCF) of an investment is also referred to as the Net Present Value (NPV).
If we break the term NPV we can see why this is the case:
Net = the sum of all positive and negative cash flows
Present value = discounted back to the time of the investment
DCF Formula in Excel
MS Excel has two formulas that can be used to calculate discounted cash flow, which it terms as ‘NPV’.
Regular NPV formula:
=NPV(discount rate, series of cash flows)
This formula assumes that all cash flows received are spread over equal time periods, whether years, quarters, months, or otherwise. The discount rate has to correspond to the cash flow periods, so an annual discount rate of r% would apply to annual cash flows.
Time adjusted NPV formula:
=XNPV(discount rate, series of all cash flows, dates of all cash flows)
With XNPV, it’s possible to discount cash flows that are received over irregular time periods. This is particularly useful in financial modelling when a company may be acquired partway through a year.
For example, this initial investment may be on August 15th, the next cash flow on December 31st, and every other cash flow thereafter a year apart. XNPV can allow you to easily solve for this in Excel.
What is the DCF Formula Used For?
The DCF formula is used to determine the value of a business or a security. It represents the value an investor would be willing to pay for an investment, given a required rate of return on their investment (the discount rate).
Examples of Uses for the DCF Formula:
To value an entire business
To value a project or investment within a company
To value a bond
To value shares in a company
To value an income-producing property
To value the benefit of a cost-saving initiative at a company
To value anything that produces (or has an impact on) cash flow
What Does the Discounted Cash Flow Formula Tell You?
When assessing a potential investment, it’s important to take into account the time value of money or the required rate of return that you expect to receive.
The DCF formula takes into account how much return you expect to earn, and the resulting value is how much you would be willing to pay for something to receive exactly that rate of return.
If you pay less than the DCF value, your rate of return will be higher than the discount rate.
If you pay more than the DCF value, your rate of return will be lower than the discount rate.
Step-by-Step Guide to Building a DCF Model in Excel
1. Gather Necessary Information
Historical Financials Obtain the historical financial statements of the company.
Projections Gather projections for revenue, expenses, and other financial metrics for the forecast period (5 years).
Discount Rate Assume a discount rate of 10% (input in cell B1).
Terminal Value Assume a perpetuity growth rate of 3% to calculate the terminal value.
2. Setup the Spreadsheet
Spreadsheet Structure Set up the spreadsheet with separate sections for assumptions, income statement, cash flow statement, and DCF analysis.
Time Period Define the time period
3. Income Statement Projections
Revenue Projections Project the revenue for the forecast period using a base revenue of $100 million and a growth rate of 5%
Expense Projections Project the operating expenses using a base expense of $70 million and a growth rate of 4%
EBITDA and Net Income
4. Cash Flow Projections
Depreciation & Amortisation Assume a constant depreciation of 5 million each year in cells E3:E7.
Working Capital Assume a constant change in working capital of $2 million each year in cells F3:F7.
Free Cash Flow In cells G3:G7, calculate free cash flow (FCF) using the formula:
5. Discounted Cash Flow Analysis
Discount Rate Input the discount rate of 10%
Discounted Cash Flows Use the NPV function to calculate the present value of the cash flows from year 1 to year 5
6. Calculate Terminal Value and Discounted Terminal Value
Terminal Value =Closing Free Cash Flow*((1+0.03)/(0.1-0.03))
Discounted Terminal Value Discount the terminal value back to the present value using the formula =PV(Discount Rate, 5, 0, -Terminal Value)
7. Calculate Net Present Value (NPV)
=NPV of Cash Flows + Discounted Terminal Value
What are the limitations of discounted cash flow?
The Discounted Cash Flow (DCF) method, while a powerful tool in financial analysis, does have several limitations. Here are some of them:
Forecast Uncertainty The DCF relies heavily on projections of future cash flows, which can be uncertain and vary significantly based on the assumptions made. Small changes in assumptions can lead to vastly different valuations.
Discount Rate Sensitivity Determining the appropriate discount rate can be challenging. The valuation is highly sensitive to the discount rate used, and small changes in the discount rate can have a significant impact on the calculated NPV.
Terminal Value A significant portion of the valuation often comes from the terminal value, which is calculated based on assumptions about long-term growth rates. This can sometimes introduce a high degree of uncertainty in the valuation.
Complexity and Time-Consuming The DCF method can be complex and time-consuming, especially for companies with unpredictable cash flows or for those in rapidly changing industries.
Historical Data Dependency The DCF often relies on historical data to project future cash flows, which might not always be a reliable indicator of future performance, particularly in industries undergoing significant changes.
Not Suitable for Startups or Companies with No Cash Flows The DCF method is less suitable for valuing startups or companies with no historical cash flows, as it becomes challenging to make reliable cash flow projections.
Ignoring Market Conditions DCF analysis is an intrinsic valuation method and does not consider current market conditions or the valuation of comparable companies, which can sometimes lead to valuations that are out of sync with the market.
Potential for Bias Since the DCF involves making numerous assumptions about the future, there is potential for bias, where the analyst might consciously or unconsciously make assumptions that lead to a desired valuation.
Ignoring Non-Financial Factors DCF focuses primarily on financial factors and might not fully consider non-financial factors such as brand value, customer loyalty, or strategic positioning, which can also have a significant impact on a company's value.
Lack of Consideration for Synergies and Strategic Acquisitions In cases of mergers and acquisitions, the DCF may not fully capture the potential synergies and benefits that might arise from the combination of two companies.
To mitigate these limitations, it is often recommended to use DCF analysis in conjunction with other valuation methods, such as comparable company analysis (CCA) or precedent transactions, and to conduct sensitivity analysis to understand the range of possible valuations based on different assumptions.
Philip Seigel CPA FFIN
Comdex Training
Disclaimer
This DCF sample model is not intended to be used as the sole piece of information in making an investment decision, and is presented here entirely as a training tool. Comdex Training will not be responsible for any and all errors or omissions.
Comdex Training makes no representations or warranties, express or implied, regarding the accuracy, completeness, or reliability of the information contained herein, and shall not be liable for any losses or damages arising from the use of this analysis, including, without limitation, any direct, indirect, incidental, consequential, or special damages.