DCF Model Training | Excel Tutorial Guide (2024)

DCF Model Basics: Present Value Formula

The DCF approach requires that we forecast a company’s future cash flows and discount them to the present to arrive at a present value for the company. That present value is the amount investors should be willing to pay (the company’s value). We can express this formulaically as the following (we denote the discount rate asr):

So, let’s say you decide you’re willing to pay $800 for the below. We can solve this as:

If I make the same proposition but instead of only promising $1,000 next year, say I promise $1,000 for the next 5 years.The math gets only slightly more complicated:

In Excel, you can calculate this using the PV function (see below). However, if cash flows are different each year, you will have to discount each cash flow separately:

DCF Model – Excel Template

Use the form below to download our sample DCF model template:

How to Build a DCF Model: 6-Step Framework

The premise of the DCFmodel is that the value of a business is purely a function of its future cash flows.Thus, the first challenge in building a DCF model is to define and calculate the cash flows that a business generates. There are two common approaches tocalculatingthe cash flows that a business generates.

1. Unlevered DCF approach
   Forecast and discount the operating cash flows. Then, when you have a present value, just add any non-operating assets such as cash, and subtract any financing-related liabilities such as debt.
2. Levered DCF approach
   Forecast and discount the cash flows that remain available to equity shareholders aftercash flows to all non-equity claims (i.e. debt) have been removed.

Both should theoretically lead to the same value at the end (though in practice it’s pretty hard to get them to be exactly equal). The unlevered DCF approach is the most common and is thus the focus of this guide. This approach involves 6 steps:

Step 1. Forecasting unlevered free cash flows

• Step 1 is to forecast the cash flows a company generates from its core operations after accounting for all operating expenses and investments.
• These cash flows are called “unlevered free cash flows.”

Step 2. Calculating the terminal value

• You can’t keep forecasting cash flows forever. At some point, you must make some high-level assumptions about cash flows beyond the final explicit forecast year by estimating a lump-sum value of the business past the explicit forecast period.
• That lump sum is called the “terminal value.”

Step 3. Discounting the cash flows to the present at the WACC

• The discount rate that reflects the riskiness of the unlevered free cash flows is called the weighted average cost of capital.
• Because unlevered free cash flows represent all operating cash flows, these cash flows “belong” to both the company’s lenders and owners.
• As such, the risks of both providers of capital (i.e. debt vs. equity) need to be accounted for using appropriate capital structure weights (hence the term “weighted average” cost of capital).
• Once discounted, the present value of all unlevered free cash flows is called the enterprise value.

Step 4. Add the value of non-operating assets to the present value of unlevered free cash flows

• If a company has any non-operating assets such as cash or has some investments just sitting on the balance sheet, we must add them to the present value of unlevered free cash flows.
• For example, if we calculate that the present value of Apple’s unlevered free cash flows is $700 billion, but then we discover that Apple also has $200 billion in cash just sitting around, we should add this cash.

Step 5. Subtract debt and other non-equity claims

• The ultimate goal of the DCF is to get at what belongs to the equity owners (equity value).
• Therefore if a company has any lenders (or any other non-equity claims against the business), we need to subtract this from the present value.
• What’s left over belongs to the equity owners.
• In our example, if Apple had $50 billion in debt obligations at the valuation date, the equity value would be calculated as:
  • $700 billion (enterprise value) + $200 billion (non-operating assets) – $50 (debt) = $850 billion
• Often, the non-operating assets and debt claims are added together as one term called net debt (debt and other non-equity claims – non-operating assets).
• You’ll often see the equation: enterprise value – net debt = equity value. The equity value that the DCF calculates is comparable to the market capitalization (the market’s perception of the equity value).

Step 6. Divide the equity value by the shares outstanding

• The equity value tells us what the total value to owners is. But what is the value of each share? In order to calculate this, we divide the equity value by the company’s diluted shares outstanding.

Calculating Unlevered Free Cash Flows (FCF)

Here is the formula for unlevered free cash flow:

FCF= EBIT x (1- tax rate) + D&A + NWC – Capital expenditures

• EBIT = Earnings before interest and taxes. This represents a company’s GAAP-based operating profit.
• Tax rate = The tax rate the company is expected to face. When forecasting taxes, we usually use a company’s historical effective tax rate.
• D&A = Depreciation and amortization.
• NWC = Annual changes in net working capital. Increases in NWC are cash outflows while decreases are cash inflows.
• Capital expenditures represent cash investments the company must make to sustain the forecasted growth of the business. If you don’t factor in the cost of required reinvestment into the business, you will overstate the value of the company by giving it credit for EBIT growth without accounting for the investments required to achieve it.

FCFs are ideally driven from a 3-statement model

Forecasting all these line items should ideally come from a 3-statement model because all of the components of unlevered free cash flows are interrelated: Changes in EBIT assumptions impact capex, NWC, and D&A. Without a 3-statement model that dynamically links all these components together, it is difficult to ensure that the changes in assumptions of one component correctly impact the other components.

Because this takes more work and more time, finance professionals often do preliminary analyses using a quick, back-of-the-envelope DCF model and only build a full DCF model driven by a 3-statement model when the stakes are high, such as when an investment banking deal goes “live” or when a private equity firm is in the later stages of the investment process.

2-Stage DCF Model Structure

The 3-statement models that support a DCF are usually annual models that forecast about 5-10 years into the future. However, when valuing businesses, we usually assume they are a going concern. In other words, the assumption is that they will continue to operate forever.

That means that the 3-statement model only takes us so far. We also have to forecast the present value of all future unlevered free cash flows after the explicit forecast period. This is called the 2-stage DCF model. The first stage is to forecast the unlevered free cash flows explicitly (and ideally from a 3-statement model). The second stage is the total of all cash flows after stage 1. This typically entails making some assumptions about the company reaching mature growth. The present value of the stage 2 cash flows is called the terminal value.

Calculating the terminal value

In a DCF, the terminal value (TV) represents the value the company will generate from all the expected free cash flowsafter the explicit forecast period. Imagine that we calculate the following unlevered free cash flows for Apple:

Apple is expected to generate cash flows beyond 2022, but we cannot project FCFs forever (with any degree of accuracy). So how do we estimate the value of Apple beyond 2022? There are two common approaches:

1. Growth in Perpetuity
2. Exit EBITDA Multiple Method

Growth in Perpetuity Approach

The growth in perpetuity approach assumes Apple’s UFCFs will grow at some constant growth rate assumption from 2022 to … forever. The formula for calculating the present value of a cash flow growing at a constant growth rate in perpetuity is called the “Growth in perpetuity formula”:

If we assume that after 2022, Apple’s UFCFs will grow at a constant 4% rate into perpetuity and will face a weighted average cost of capital of 10% in perpetuity, the terminal value (which is the present value of all Apple’s future cash flows beyond 2022) is calculated as:

At this point, notice that we have finally calculated enterprise value as simply the sum of the stage 1 present value of UFCFs + the present value of the stage 2 terminal value.

Exit EBITDA Multiple Method

The growth in perpetuity approach forces us to guess the long-term growth rate of a company. The result of the analysis is very sensitive to this assumption. A way around having to guess a company’s long-term growth rate is to guess the EBITDA multiple the company will be valued at the last year of the stage 1 forecast.

A common way to do this is to look at the current EV/EBITDA multiple the company is trading at (or the average EV/EBITDA multiple of the company’s peer group) and assume the company will be valued at that same multiple in the future. For example, if Apple is currently valued at 9.0x its last twelve months (LTM) EBITDA, we can assume that in 2022 it will be valued at 9.0x its 2022 EBITDA.

Growth in Perpetuity vs. Exit EBITDA Multiple Method

Investment bankers and private equity professionals tend to be more comfortable with the EBITDA multiple approach because it infuses market reality into the DCF. A private equity professional building a DCF will likely try to figure out what he/she can sell the company for 5 years down the road, so this arguably provides a valuation via an EBITDA multiple.

However, this approach suffers from a significant conceptual problem: It incorporates current market valuations within the DCF, which arguably defeats the whole purpose of the DCF. Making matters worse is the fact that the terminal value often represents a significant percentage of the value contribution in a DCF, so the assumptions that go into calculating the terminal value are all the more important.

Discounting Free Cash Flows (FCFs) by WACC

Up to now, we’ve been assuming a 10% discount rate for Apple, but how is that quantified?

Quantifying the discount rate, which in this case is the weighted average cost of capital (WACC), is a critical field of study in corporate finance. You can spend an entire college semester learning about it. We’ve written a complete guide to WACC here, but below are the basic elements for how it is typically calculated:

The WACC formula

Where:

• Debt = market value of debt
• Equity = market value of equity
• r_debt = cost of debt
• r_equity = cost of equity

Calculating Equity Value: Adding the Value of Non-Operating Assets

Many companies have assets not directly tied to operations. Assets such as cash increase the value of the company (i.e. a company whose operations are worth $1 billion but also has $100 million in cash is worth $1.1 billion). But up to now, the value is not accounted for in the unlevered free cash flow calculation. Therefore, these assets need to be added to the value. The most common non-operating assets include:

• Cash
• Marketable securities
• Equity investments

Below is Apple’s 2016 year-end balance sheet. The non-operating assets are its cash and equivalents, short-term marketable securities, and long-term marketable securities. As you can see, they represent a significant portion of the company’s balance sheet.

Unlike operating assets such as PP&E, inventory, and intangible assets, the carrying value of non-operating assets on the balance sheet is usually fairly close to their actual value. That’s because they are mostly comprised of cash and liquid investments that companies generally can mark up to fair value. That’s not always the case (equity investments are a notable exception), but it’s typically safe to simply use the latest balance sheet values of non-operating assets as the actual market values.

Getting from Enterprise Value to Equity Value (Subtracting Debt and Non-Equity Claims)

At this point, we need to identify and subtract all non-equity claims on the business to arrive at how much of the company value belongs to equity owners. The most common non-equity claims you’ll encounter are:

• All debt (short-term, long-term, bonds, loans, etc..)
• Capital Leases
• Preferred stock
• Non-controlling (minority) interests

Below are Apple’s 2016 year-end balance sheet liabilities. You can see it includes commercial paper, the current portion of long-term debt, and long-term debt.

These are the three items that would make up Apple’s non-equity claims.

As with the non-operating assets, finance professionals usually just use the latest balance sheet values of these items as a proxy for their actual values. This is usually a safe approach when the market values are fairly close to the balance sheet values. The market value of debt doesn’t usually deviate too much from the book value unless market interest rates have changed dramatically since the issue or if the company’s credit profile has changed dramatically (i.e. a company in financial distress will have its debt trading at pennies on the dollar).

One place where the book value-as-proxy-for-market-value can be dangerous is with “non-controlling interests.”Non-controlling interests are usually understated on the balance sheet. If they are significant, it is preferable to apply an industry multiple to better reflect their true value.

The bad news is thatwe rarely have enough insight into the nature of the non-controlling interests’ operations to figure out the right multiple to use. The good news is that non-controlling interests are rarely large enough to make a significant difference in valuation (most companies don’t have any).

Net debt formula

When building a DCF model, finance professionals often net non-operating assets against non-equity claims and call it net debt, which is subtracted from enterprise value to arrive at equity value:

Enterprise value – net debt = Equity value

The formula for net debt is simply the value of all nonequity claims less the value of all non-operating assets:

• Gross Debt (short term, long term, bonds, loans, etc..)
• + Capital Leases
• + Preferred stock
• + Non-controlling (minority) interests
• – Cash
• – Marketable securities
• – Equity investments
• Net debt

Using Apple’s 2016 10K, we can see that it has a substantial negative net debt balance. For companies that carry significant debt, a positive net debt balance is more common, while a negative net debt balance is common for companies that keep a lot of cash.

Calculate Equity Value Per Share

Once a company’s equity value has been calculated, the next step is to determine the value of each share. To figure this out, we have to determine the number of shares that are currently outstanding. We have written a thorough guide to calculating a company’s current shares but will summarize the key steps here:

1. Take the current actual share count from the front cover of the company’s latest annual (10K) or interim (10Q) filing. For Apple, it is:

2. Next, add the effect of dilutive shares. These are shares that aren’t quite common stock yet, but that can become common stock and thus be potentially dilutive to the common shareholders (i.e. stock options, warrants, restricted stock, convertible debt, and convertible preferred stock).

Assuming that we calculated 50 million dilutive securities for Apple, we can now put all the pieces together and complete the analysis:

Key DCF Assumptions

We have now completed the 6 steps to building a DCF model and have calculated the equity value of Apple.

What were the key assumptions that led us to the value we arrived at?

The three key assumptions in a DCF model are:

1. The operating assumptions(revenue growth and operating margins)
2. The weighted average cost of capital (WACC)
3. Terminal value assumptions: Long-term growth rate and the exit multiple

Each of these assumptions is critical to getting an accurate model. In fact, the DCF model’s sensitivity to these assumptions, and the lack of confidence finance professionals have in these assumptions, (especially the WACC and terminal value) are frequently cited as the main weaknesses of the DCF model.

Nonetheless, the DCF model is one of the most common models used by investment bankers and other finance professionals, and the DCF output is almost always presented using a range of terminal value and WACC assumptions, as well as in context to other valuation methodologies. A common way this is presented is by using a football field valuation matrix.

We wrote this guide for those thinking about a career in finance and those in the early stages of preparing for job interviews. This guide is quite detailed, but it stops short of all corner cases and nuances of a fully-fledged DCF model.