dcf model case study

Join 307,012+ Monthly Readers

Get Free and Instant Access To The Banker Blueprint : 57 Pages Of Career Boosting Advice Already Downloaded By 115,341+ Industry Peers.

• Break Into Investment Banking
• Write A Resume or Cover Letter
• Win Investment Banking Interviews
• Ace Your Investment Banking Interviews
• Win Investment Banking Internships
• Master Financial Modeling
• Get Into Private Equity
• Get A Job At A Hedge Fund
• Recent Posts
• Articles By Category

The DCF Model: The Complete Guide… to a Historical Relic?

If you're new here, please click here to get my FREE 57-page investment banking recruiting guide - plus, get weekly updates so that you can break into investment banking . Thanks for visiting!

Cryptocurrencies based on dog memes suddenly spike up or down by 500%, people think that meme stocks are better investments than high-dividend stocks, and growth-oriented tech stocks seem to rise forever, all based on promises of “profits in the future – the distant future.”

In this environment, it’s fair to ask if the discounted cash flow (DCF) analysis and DCF models are still relevant at all.

I’ll address this question at the end of this article, but the short answer is that the DCF model still matters – but perhaps less so for a tiny percentage of overhyped companies and less so in crazed market environments.

But let’s start by describing each step of the analysis and giving you a few simple examples:

DCF Model: Video Tutorial and Excel Templates

If you’d prefer to watch rather than read, you can get this [very long] tutorial below:

Table of Contents:

• 2:29: The Big Idea Behind a DCF Model
• 5:21: Company/Industry Research
• 8:36: DCF Model, Step 1: Unlevered Free Cash Flow
• 21:46: DCF Model, Step 2: The Discount Rate
• 28:46: DCF Model, Step 3: The Terminal Value
• 34:15: Common Criticisms of the DCF – and Responses

And here are the relevant files and links:

• Walmart DCF – Corresponds to this tutorial and everything below.
• Walmart 10-K Excerpts .
• Slide presentation for this tutorial .
• Uber Valuation and DCF – Different DCF model for a high-growth company (sort of).
• Snap Valuation and DCF – Different DCF model for a different high-growth company.

The Big Idea Behind a DCF Model

The big idea is that you can use the following formula to value any asset or company that generates cash flow (whether now or “eventually”):

The “Discount Rate” represents risk and potential returns – a higher rate means more risk, but also higher potential returns.

A company is worth more when its cash flows and cash flow growth rate are higher, and it’s worth less when those are lower.

The company is also worth less when it is riskier or when expectations for it are higher, i.e., when the Discount Rate is higher.

If a company’s Discount Rate and Cash Flow Growth Rate stayed the same forever, then investment analysis would be simple: just plug the numbers into this formula.

But that never happens!

Companies grow and change over time, and often they are riskier with higher growth potential in earlier years, and then they mature and become less risky later on.

Valuation is more than this simple formula because companies’ Discount Rates and Cash Flow Growth Rates change over time.

To represent that change, you divide companies’ lifecycles into two periods:

• Period #1 (Explicit Forecast Period): The company’s Cash Flow, Cash Flow Growth Rate, and potentially even the Discount Rate change over 5, 10, 15, or 20+ years, but the company reaches maturity or “stabilization” by the end.
• Period #2 (Terminal Period): The Discount Rate and Cash Flow Growth Rate stop changing because the company is mature. Its Cash Flow will still change, but the valuation formula above works because it requires only the first year of Cash Flow in this period.

You value the company in both these periods and then add the results to get its total value from today into “infinity” (AKA until the Present Value of its cash flows falls to near-0).

Company/Industry Research

Before you jump into Excel and start entering numbers, you should do a bit of company and industry research to establish the following:

• What are the top 5-10 most important drivers for the company?
• How can you project its revenue beyond a simple percentage growth rate? What about its expenses?
• What do its historical trends look like, ideally going back 5-10 years?

The company’s annual report and investor presentations are the best starting points.

You could also search for industry data from companies like IDC , Gartner , and Forrester , but it’s not necessary for a quick analysis of a mature company.

And if you are dealing with a rapidly changing company or a tech startup (e.g., Uber or Snap), it’s often more useful to get KPIs and financial stats from similar companies that were once growing quickly but have since matured.

In theory, you could spend days, weeks, or months on industry and company research, but that much effort is not necessary.

We recommend reading through the annual report and investor presentation to the extent that you can come up with those 5-10 key drivers .

For Walmart, we came up with the following:

Its annual filing repeatedly cited its total square feet, so we made the total retail square feet the top-line driver and based other numbers on $ per square foot figures.

DCF Model, Step 1: Unlevered Free Cash Flow

While there are many types of “Free Cash Flow,” in a standard DCF model, you almost always use Unlevered Free Cash Flow (UFCF) , also known as Free Cash Flow to Firm (FCFF) , because it produces the most consistent results and does not depend on the company’s capital structure.

Unlevered Free Cash Flow should include:

• COGS and Operating Expenses
• Depreciation & Amortization and sometimes other non-cash adjustments*
• The Change in Working Capital
• Capital Expenditures

*Depreciation & Amortization gets a bit more complicated, especially if you’re analyzing a company that follows IFRS (see the next section).

This list means that you ignore almost everything else: Net Interest Expense, Other Income / (Expense), most non-cash adjustments, most of the Cash Flow from Investing section, and the Cash Flow from Financing section.

For Walmart, many of the items in UFCF are simple $ per square foot figures:

To calculate UFCF, start with Revenue and subtract COGS , OpEx, and Taxes (which are now different since they’re based on Operating Income ).

Then, add back D&A, factor in Deferred Taxes, any other recurring operating activities, and the Change in Working Capital, and subtract CapEx:

In some cases, we recalculate items such as Deferred Taxes because we’re modifying the company’s historical Taxes to make them comparable to future Taxes.

Most of these items should be fairly low as percentages of revenue or the change in revenue.

For example, it would be highly unusual if the Change in Working Capital represented 50% of a company’s UFCF.

For most companies, Working Capital is not a major value driver because it represents simple timing differences.

We also made sure that CapEx as a percentage of revenue stays ahead of D&A as a percentage of revenue in each year because Walmart’s cash flows are growing .

Even if the growth is modest, the company will need to increase its Net PP&E over time to support that growth.

If you don’t know what some of these items mean, please see our coverage of the Change in Working Capital and Unlevered Free Cash Flow for more details.

It would also help to know a bit about the company’s operating leverage to forecast some of the expenses, but it’s not essential for a quick analysis.

But Wait! What About Operating Leases in DCF Models?

Accounting for operating leases has become more complicated with the introduction of IFRS 16 in 2019, which required companies to put Operating Lease Assets and Liabilities directly on their Balance Sheets (see: our full tutorial to lease accounting ).

The equivalent rules under U.S. GAAP aren’t too bad because U.S. companies still record Rent as a simple operating expense on their Income Statements .

Under IFRS, however, Rent is split into an Amortization or Depreciation element and an Interest element, similar to the treatment for Finance Leases.

Over a large portfolio of leases with different start and end dates, the Lease Amortization + Lease Interest is about the same as the Rental Expense under U.S. GAAP.

The goal in a DCF is to reflect the company’s cash revenue , cash expenses , and cash taxes , so we believe the best approach is to deduct the entire Operating Lease Expense in UFCF.

For IFRS-based companies, that means you’ll have to deduct the Interest element in the EBIT and NOPAT calculations:

Also, you should not add back the Operating Lease Depreciation or Amortization because in this case, it represents part of an actual cash expense .

If you follow this treatment, the UFCF number will reflect the deduction for the full Lease Expense.

Some argue that you should add back the entire Lease Expense and count Operating Leases as an item in the Equity Value to Enterprise Value bridge.

We don’t favor that approach because UFCF does not reflect the company’s cash expenses if you do that, and it’s more difficult to compare companies that way.

DCF Model, Step 2: The Discount Rate

Once you’ve projected the company’s Unlevered Free Cash Flows, you need to discount them to their Present Value : what they’re worth today.

That value today depends on how much you could earn with your money in other, similar companies in this market, i.e., your expected, average annualized returns.

(For more on this topic, see our tutorials on Net Present Value , the time value of money , and future value .)

The Discount Rate expresses these expected, average annualized returns, and in an Unlevered DCF, it’s equal to WACC, or the  “ Weighted Average Cost of Capital .”

The name means what it sounds like: you estimate the “cost” of each form of capital the company has, weigh them by their percentages, and then add them up.

“Capital” means “a source of funds.” So, if a company borrows money in the form of Debt to fund its operations, that Debt is a form of capital.

And if it goes public in an IPO, the shares it issues, called “Equity,” are also a form of capital.

The exact formula is:

WACC = Cost of Equity * % Equity + Cost of Debt * (1 – Tax Rate) * % Debt + Cost of Preferred Stock * % Preferred Stock

The Cost of Equity represents potential returns from the company’s stock price and dividends, or how much it “costs” the company to issue shares.

For example, if the company’s dividends are 3% of its current share price (i.e., the dividend yield is 3%), and its stock price has increased by 6-8% each year historically, its Cost of Equity might be between 9% and 11%.

The Cost of Debt represents returns on the company’s Debt, mostly from interest, but also from the market value of the Debt changing.

For example, if the company is paying a 6% interest rate on its Debt, and the market value of its Debt is close to its face value, then the Cost of Debt might be around 6%.

You also multiply that by (1 – Tax Rate) because Interest paid on Debt is tax-deductible. So, if the Tax Rate is 25%, the After-Tax Cost of Debt would be 6% * (1 – 25%) = 4.5%.

The Cost of Preferred Stock is similar because Preferred Stock works similarly to Debt, but Preferred Stock Dividends are not tax-deductible, and overall rates tend to be higher, making it more expensive.

The Discount Rate in Real Life vs. Simple Approximations

The calculations for the Cost of Debt and Preferred Stock are straightforward, but the Cost of Equity is more challenging because it’s subjective and depends on how other, similar companies have performed relative to the market.

In many DCF models, you’ll see a sheet dedicated to this calculation, where the modeler “un-levers Beta” for each peer company to estimate its risk/volatility independent of its capital structure and then re-levers it for the subject company:

The problem with this approach is that you need quick access to data for comparable companies, which may be tricky without Capital IQ, FactSet, or similar services.

Luckily, there is a “shortcut method” as well, which involves using the same formula but simplifying the last input:

Cost of Equity = Risk-Free Rate + Equity Risk Premium * Levered Beta

The Risk-Free Rate (RFR) is what you might earn on “safe” government bonds in the same currency as the company’s cash flows (so, U.S. Treasuries here).

The Equity Risk Premium (ERP) is the percentage the stock market is expected to return each year, on average, above the yield on these “safe” government bonds.

And Levered Beta tells you how volatile this stock is relative to the market as a whole, factoring in both business risk and risk from leverage (Debt).

If it’s 1.0, then the stock follows the market perfectly and goes up by 10% when the market goes up by 10%; if it’s 2.0, the stock goes up by 20% when the market goes up by 10%.

Rather than finding comparable companies and un-levering and re-levering Beta, you could just look it up for the company on Yahoo Finance:

You can then combine it with easy-to-find data on 10-year U.S. Treasury yields and the Equity Risk Premium from Damodaran’s collection (or other sources – there are plenty of estimates for the current ERP in different markets):

The Discount Rate is around 4.0% with this approach (assuming ~90% Equity and ~10% Debt for Walmart), close to the 4.37% in the full model.

Sure, you could make it more complicated, but I would argue it’s a waste of time in a case study or modeling test unless they specifically ask for it.

The important part is that the company’s Discount Rate is closer to 5% than 10% or 15%, so we can use a range of values with 5% in the middle.

Also, you can now use this Discount Rate to take the Present Value of each UFCF (PV = UFCF / ((1 + Discount Rate) ^ Year #):

DCF Model, Step 3: The Terminal Value

The Terminal Value goes back to the “big idea” behind a DCF model.

Put simply, the “Company Value” in this formula:

IS the Terminal Value – assuming that each input represents the Terminal Period in the DCF model.

To calculate it, you need to get the company’s first Cash Flow in the Terminal Period and its Cash Flow Growth Rate and Discount Rate in that Terminal Period.

In an Unlevered DCF, this formula becomes:

Terminal Value = Unlevered FCF in Year 1 of Terminal Period / (WACC – Terminal UFCF Growth Rate)

And you can estimate the UFCF in Year 1 of the Terminal Period like this:

Terminal Value = UFCF in Final Year of Explicit Forecast Period * (1 + Terminal UFCF Growth Rate) / (WACC – Terminal UFCF Growth Rate)

This “Terminal Growth Rate” should be low : below the long-term GDP growth rate, especially in developed countries.

You could also estimate the Terminal Value with an EBITDA multiple based on median multiples from the comparable companies, but we don’t recommend that as the primary method.

It’s too easy to pick multiples that imply ridiculous Terminal FCF Growth Rates, so it’s safer to start with the growth rates and then check their implied multiples .

Once you have the Terminal Value, you can discount it back to Present Value and add it to the Sum of the Present Values of the Free Cash Flows:

And then, you can back into the Implied Equity Value and Implied Share Price from there:

You can also set up a sensitivity analysis in Excel to assess what the company’s valuation looks like with different assumptions for the Terminal Growth Rate, Terminal Multiple, Discount Rate, and so on:

One Final Note: This Terminal FCF Growth Rate should be fairly close to the UFCF growth rate in the final year of the explicit forecast period.

You don’t want UFCF to grow at 10% or 20% and suddenly drop to 2% in the Terminal Period.

If it does, you need to re-think your assumptions or extend the analysis.

Because of this problem, we extended the explicit forecast period to 20 years in the Uber valuation .

Conclusions from This DCF Model

Overall, Walmart seems modestly undervalued because its implied share price in most of the sensitivity tables is above its current share price of ~$140.

There is one problem with this analysis, though: we’re assuming that Walmart keeps growing its retail square feet, even though that number has been declining in recent years.

Therefore, if we had more time and resources, we might create a few operating scenarios, similar to the Uber and Snap models, to assess the results in “growth” vs. “stagnant” vs. “decline” cases.

Common Criticisms of the DCF Model – and Responses

People often criticize the DCF model for the following reasons:

• “But how can you possibly predict a company 5, 10, or 15 years into the future? No one can!”
• “The DCF is too sensitive to small changes in assumptions, such as growth rates and margins.”
• “A DCF ignores market conditions and comparable companies, so it might not give you the accurate market value.”
• “The DCF is no longer applicable because stocks are valued based on memes / crypto / Reddit! No one cares about cash flow.”

My response to the first three objections is similar: it’s not about the exact numbers but ranges, scenarios, and sensitivities .

No, you don’t know whether the Year 10 growth rate will be 10% or 8% or 12%, but you should have an idea of whether it will be closer to 10% or 20%.

And if you don’t, it’s fine to build a DCF with a wide valuation range that reflects high uncertainty.

The complaint about a DCF being “too sensitive” raises other questions: for example, is the FCF growth rate in the final year of the explicit forecast period close to the Terminal FCF Growth Rate?

If not, you need to re-think your assumptions or extend the projections.

And the critique about ignoring market conditions conveniently ignores that the Discount Rate is always based on current market conditions, no matter how you calculate it.

The DCF is indeed less reflective of the current market than comparable company analysis (for example), but it still reflects some market conditions.

And finally, for the crypto/meme/Reddit objection: yes, I agree that certain stocks seem to defy all logic and cash flow-based analysis.

That said, these stocks represent a tiny fraction of all the public companies worldwide.

The media gives them excessive attention, but they ignore the hundreds of thousands (millions?) of other companies that follow some semblance of logic.

And as for crypto, I agree that you cannot use a DCF to value Bitcoin, Ethereum, or Dogecoin.

But this is nothing new: a DCF only works for assets that generate cash flow , whether now or in the future.

No one has ever suggested valuing gold or silver with a DCF, and I’m not sure how crypto is any different in this regard.

DCF Models: Further Learning

If you want to learn more about DCF models and get a step-by-step walkthrough in more detail, sign up for our free financial modeling tutorials .

These tutorials provide a 3-part series on the valuation of Michael Hill, a retailer in Australia and New Zealand, and they go into each step in more depth than we did above.

And if you want in-depth case studies backed by real-world data and research, the Core Financial Modeling course delves into valuation/DCF analysis in even greater detail:

Core Financial Modeling

Learn accounting, 3-statement modeling, valuation/DCF analysis, M&A and merger models, and LBOs and leveraged buyout models with 10+ global case studies.

A few modules are dedicated to valuation and DCF analysis, and there are example company valuations in other industries.

If you want even more complex examples, the Advanced Financial Modeling course might be more appropriate since it deals with topics like the mid-year convention, stub periods, a normalized terminal year, and net operating losses in a DCF:

Advanced Financial Modeling

Learn more complex "on the job" investment banking models and complete private equity, hedge fund, and credit case studies to win buy-side job offers.

About the Author

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street . In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.

Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews

Learn Valuation and Financial Modeling

Get a crash course on accounting, 3-statement modeling, valuation, and M&A and LBO modeling with 10+ global case studies.

• Members - Resources
• Internal Relations
• Practice Case Study

CYBER MONDAY: Save 25% on all templates! Use code CYBER25 at checkout. Valid only for 24 hours! Dismiss

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

• Updated on January 11, 2024

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.

• 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.
• 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.
• 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%

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
• 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 v alue 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?

Related Posts

A complete guide to build your own crm tool. How to create a CRM in Excel? Step-by-step instructions. Free templates included.

A complete guide to make RACI matrix for your projects. How to create a RACI Chart in Excel? Free template included.

A quick guide for beginners: How to make a Personal Financial Statement? Create your own PFS in Excel easily. Steps, images, and template.

Valuation Resources

• Career Guides
• Interview Prep Guides
• Free Practice Tests
• Excel Cheatsheets

ALL COURSES @ADDITIONAL 50% OFF

Discounted Cash Flow (DCF)

Publication Date :

06 Jun, 2018

Blog Author :

Priya Choubey

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

What is Discounted Cash Flow (DCF)?

The Discounted Cash Flow (DCF) valuation model determines the company’s present value by adjusting future cash flows to the time value of money. This DCF analysis assesses the current fair value of assets or projects/companies by addressing inflation, risk, and cost of capital, analyzing the company’s future performance.

In other words, the DCF valuation model uses a company's forecasted free cash flows and discounts them back to arrive at the present value estimate, which forms the basis for the potential investment now.

Key Takeaways                                                                

• The Discounted Cash Flow (DCF) valuation model determines the present value of a company by adjusting future cash flows to account for the time value of money. 
• It assesses the current fair value of assets or projects/companies by considering factors such as inflation, risk, and the cost of capital and predicts the company's future performance. 
• The seven steps involved in DCF analysis include projecting financial statements, calculating free cash flow to the firm, determining the discount rate, calculating the terminal value, performing present value calculations, making necessary adjustments, and conducting sensitivity analysis.

Discounted Cash Flow (DCF) Valuation Analogy

Let us take a simple discounted cash flow example. Suppose you can choose between receiving $100 today and obtaining $100 in a year. Which one will you take?

Here the chances are more that you will consider taking the money now because you can invest that $100 today and earn more than $100 in the next twelve months. So, you thought about the money today because it is worth more than the money in the future due to its potential earning capacity ( time value of money concept ).

Now, apply the same calculation for all the cash you expect a company to be producing in the future and discount it to arrive at the net present value. You can have a good understanding of the company’s value.

• The thumb rule states that if the value reached through discounted cash flow analysis is higher than the current cost of the investment, the opportunity would be attractive.
• Please note that the DCF model entails thinking through factors that affect a firm, like future revenue growth and profit margins, cost of equity and debt, and a discount rate that largely depends on the risk-free rate . These factors drive the share value and thus enable the analysts to put a more realistic price tag on the company’s stock.

Assuming that you understood this simple DCF stock example, we will move to the practical discounted cash flow example of Alibaba IPO .

7 Steps of Discounted Cash Flow Valuation Model

As a professional investment banker or an Equity Research Analyst . You are expected to perform DCF comprehensively. Below is a step-by-step approach to discounted cash flow analysis (as done by professionals).

Here are the seven steps to Discounted Cash Flow (DCF) Analysis -

• #1 - Projections of the Financial Statements
• #2 - Calculating the Free Cash Flow to Firms
• #3 - Calculating the Discount Rate
• #4 - Calculating the Terminal Value
• #5 - Present Value Calculations
• #6 - Adjustments
• #7 - Sensitivity Analysis

Step #1 - Projections of the Financial Statements

The first thing that needs your attention while applying discounted cash flow analysis is determining the forecasting period, as firms, unlike humans, have infinite lives. Therefore, analysts' have to decide how far they should project their cash flow in the future. The analysts' forecasting period depends on the company's stages, such as early to business, high growth rate, stable growth rate, and perpetuity growth rate.

IMPORTANT - Look at this step-by-step guide to Financial Modeling in Excel .

The forecasting period plays a critical role because small firms grow faster than more mature firms and thus carry a higher growth rate. So, the analysts do not expect the firms to have infinite lives because the small firms are more open to acquisition and bankruptcy than the larger ones. The thumb rule says that DCF analysis is widely used during a firm's estimated excess return period in the future. In other words, for a company that stops covering its costs through investments or fails to generate profits, you need not perform a DCF analysis for the next five years or so.

Image Source: Financial Modeling And Valuation Course Bundle

Forecasting is done professionally using  Financial modeling . Here you prepare a three statement model and all the supporting schedules like the  depreciation , working capital, intangibles, shareholder's equity schedule, other long term items schedule, debt schedule , etc.

Projecting the Income Statement

• Here, the analysts have to forecast the sales or revenue growth over the next five years, considering that the company will produce excess returns in the next five years. After that, the analysts calculate after-tax operating profits. At the same time, they estimate the expected  CAPEX  and an increase in networking capital over the forecasted period.
• Thus, the top-line growth or revenue growth becomes the most important assumption in the analysts' discounted cash flows about the company's future cash flows.
• Therefore, in forecasting top-line growth, we need to consider various aspects like the company's historical revenue growth, the industry's growth rate, and the economy's or GDP's development. Many analysts call it a top-to-bottom growth rate, wherein they first look at the economy's growth, then the industry, and finally, the company.
• However, another approach called the internal growth rate formula comprises return on equity and growth in retained earnings . Thus, we will use a combined growth rate to forecast future revenue, including the top-to-bottom and internal growth rates.

Projecting the Balance Sheet

• Forecasting the financial statements are made in sequence in discounted cash flows. All three statements are interconnected. You may find that while you predict from the income statement , you may have to move to the balance sheet and then to the cash flows, etc.
• Below is a snapshot of Alibaba's balance sheet forecasts.

Projecting the Cash Flow Statements

• You do not have to project each item on the Cash Flow Statements . Sometimes it becomes practically impossible due to a lack of data.
• Here, only the necessary items from the discounted cash flow valuation point of view are forecasted.

Step #2 - Calculating Free Cash Flow to Firm

The second step in discounted cash flow analysis is calculating the firm's free cash flow.

Before we estimate future free cash flow, we must first understand what free cash flow is. Free cash flow is the cash left out after the company pays all operating and required capital expenditures. The company uses this free cash flow to enhance its growth by developing new products, establishing new facilities, paying dividends to its shareholders or initiating  share buybacks .

Free cash flow reflects the firm's ability to generate money out of its business, strengthening the financial flexibility it can use to pay its outstanding net debt and increase shareholder value.

Calculate FCFF  is as follows -

Free Cash Flow to Firm or FCFF Calculation = EBIT x (1-tax rate) + Non-Cash Charges + Changes in Working capital – Capital Expenditure

After projecting the financials of Alibaba, you can link the individual items below to find the free cash flow projections for Alibaba.

Having estimated the free cash flows for the next five years, we have to figure out the worth of these cash flows at the present time. However, to get to know the present value of these future cash flows, we would require a discount rate that one can use to determine the  net present value or NPV  of these future cash flows.

Step 3- Calculating the Discount Rate

The discounted cash flow valuation analysis's third step is calculating the discount rate.

Several methods are being used to calculate the discount rate. But, the most appropriate way to determine the discount rate is to apply the concept of the weighted average cost of capital, known as WACC. First, however, you have to keep in mind that you have taken the right figures of equity and the  after-tax cost of debt , as the difference of just one or two percentage points in the cost of capital will make a vast difference in the fair value of the company. So, let us find out how the cost of equity and debt is determined.

Cost of Equity

Unlike the debt portion, which pays a set interest rate, equity does not have an actual price it pays to the investors. However, it does not mean that equity does not bear a cost. We know that the shareholders expect the company to deliver  absolute returns  on their investment in the company. Thus, from the firm's viewpoint, the required rate of return from the investors is the cost of equity. If the company fails to deliver the necessary rate of return, the shareholders will sell their positions in the company. As a result, it will hurt the share price movement in the stock market.

The most common method to calculate the capital cost is apply the capital asset pricing model or (CAPM). As per this method, the cost of equity would be (Re)= Rf + Beta (Rm-Rf).

• Re= Cost of equity
• RF= Risk-Free rate
• Rm= Market Rate

Cost of Debt

The cost of debt is easy to calculate compared to equity. The rate implied to determine the cost of debt is the current market rate that the company pays on its current debt.

For simplicity in the context of the discussion, I have taken the WACC figures directly as 9%.

IMPORTANT - You can refer to our detailed WACC guide , wherein we have discussed how to calculate this professionally with multiple examples, including that of Starbucks WACC.

Step 4 - Calculating the Terminal Value

The fourth step in discounted cash flow analysis is calculating the terminal value.

We have already calculated the critical components of DCF analysis, except the terminal value. Therefore, we will now calculate the terminal value, followed by the discounted cash flow analysis calculation. There are several ways to calculate the terminal value of cash flows.

However, the most commonly known method is to apply a perpetuity method using the Gordon Growth Model to value the company. The formula to calculate the terminal value for future cash flow is:

Terminal Value = Final Year Projected Cash Flow * (1+ Infinite Growth Rate)/ (Discount Rate-Long Term Cash Flow Growth Rate)

Step 5 - Present Value Calculations

The fifth step in discounted cash flow analysis is to find the present values of free cash flows to the firm and terminal values.

Find the present value of the projected cash flows using NPV formulas and XNPV formulas.

The projected cash flows of the firm are divided into two parts: -

• Explicit Period (the period for which FCFF was calculated – till 2022E)
• Period after the explicit period (post 2022E)

Present Value of Explicit Forecast Period (the year 2022)

Calculate the Present Value of the Explicit Cash Flows using the WACC derived above.

Present Value of Terminal Value (beyond 2022)

Step 6- Adjustments

The sixth step in discounted cash flow analysis is to adjust your enterprise valuation.

Adjustments to the discounted cash flow valuations are made for all the non-core assets and liabilities that have not been accounted for in the free cash flow projections. To find the adjusted fair  equity value , one may adjust valuation by adding unusual assets or subtracting liabilities.

Common discounted cash flow valuation adjustments include: -

Adjust your valuation for all assets and liabilities. For example, cash flow projections do not account for non-core assets and liabilities. As a result, the enterprise value may need to be adjusted by adding other unusual assets or subtracting liabilities to reflect the company's fair value. These adjustments include: -

DCF Valuation Summary

Step 7 - Sensitivity Analysis

The seventh step in discounted cash flow analysis is calculating the output's sensitivity analysis .

It is important to test your DCF model with the changes in assumptions. The two of the most important beliefs that have a major impact on valuations are: -

• Changes in the infinite growth rate
• Changes in weighted average cost of capital

We can easily do Sensitivity Analysis in excel using DATA TABLES.

The below chart shows the sensitivity analysis of Alibaba’s DCF valuation model.

• We note that the base case valuation of Alibaba is $78.3 per share.
• When WACC changes from 9% to, say, 11%, then the DCF valuation decreases to $57.7.
• Likewise, if we change the infinite growth rates from 3% to 5%, the fair DCF valuation becomes $106.5.

We know that discounted cash flow analysis helps calculate the company's value today based on the future cash flow. That is because the company's value depends upon the sum of the cash flow that the company produces in the future. However, we must discount these future cash flows to arrive at the present value.

Frequently Asked Questions (FAQs)

The DCF valuation model offers several benefits. It provides a systematic approach to evaluating the intrinsic value of an investment. Incorporating the time value of money enables a more accurate assessment of future cash flows and adjusts them to their present value. This allows investors to compare and make informed decisions based on their present value between different investment opportunities. 

The DCF valuation model finds applications in various areas. It is commonly used to value companies and determine their fair market value. By projecting future cash flows and discounting them back to the present, the model helps assess the worth of investment projects and evaluate their potential returns. It is also utilized in analyzing mergers and acquisitions, as it helps estimate the target company's value. 

While the DCF valuation model offers valuable insights, it has certain limitations to consider. One limitation is its reliance on future projections, which are subject to uncertainty and can be challenging to predict accurately. The accuracy of inputs, such as cash flow estimates and the discount rate, can significantly impact the final valuation.