Don’t forget to share this post

When it comes to investment opportunities, everyone wants an answer to the same question: is it worth it? Unfortunately, there’s no way to be certain. However, there are many financial formulas and analyses that can help you make an educated guess and not just flip a coin to decide.

Given the amount of historical data available on different industries, companies, and markets, it’s possible to make some very educated guesses. However, depending on the type of investment you’re considering, you may not have access to much historical information - for example, investing in a startup in a new market.

In this article, you will learn about one of the most widely used valuation methods: discounted cash flow (DCF) analysis. In addition to learning about the steps involved in DCF analysis, you’ll also learn about the main limitations of this method and how to minimize them. Finally, you’ll learn how to use this valuation method in Microsoft Excel or Google Sheets and how Layer can help you manage and automate the process. To learn more about business valuation methods, check out this article on How to Calculate the Value of a Company.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

What is a Discounted Cash Flow (DCF) Analysis?

Discounted cash flow analysis is a popular valuation method that estimates the value of the investment based on predicted cash flows. The DCF formula provides the present value of the investment, also known as the maximum amount you should pay given the expected growth and interest rates, as well as the duration of the investment.

This method relies on the time value of money, which assumes that the dollar you have today is worth more than the one you will receive tomorrow. The reasoning behind this is that if you have the dollar today, you can invest it and make more money. While the DCF formula can theoretically be used for any kind of investment, it makes some crucial assumptions that may not be acceptable in every situation.

How to Perform a Discounted Cash Flow Analysis?

Discounted cash flow refers to the sum of all future discounted cash flows. In other words, you need to estimate the discounted value for all future cash flows for the investment period. Many possible methods are available to estimate these values, but none can guarantee results.

This is why it’s important to understand the process and the assumptions behind the calculations. If possible, it’s best to get estimates from different analysts for both the expected cash flows and rates, particularly when you don’t have a lot of historical data to work with.

DCF Formula Syntax

The formula used to calculate discounted cash flow is the following:

discounted cash flow = cf1 / (1+r)^1 + cf2 / (1+r)^2 + …cfn / (1+r)^n

Where,

  • cfn = cash flow projection for the year
  • r = discount rate in decimal form
  • n = investment period in years

How to Calculate Discounted Cash Flow?

Follow the steps below to find the value for discounted cash flow.

Step 1. Cash Flows

The first step is to get estimates for projected cash flows. It’s important to remember that these are projections, so it’s not an exact science. There more information you have, the better, but there are no guarantees.

Step 2. Discount Rate

The second step is to determine an appropriate discount rate. Again, this is an estimate, so it might be worth exploring different options. Very often, the value used for the discount rate is the company’s Weighted Average Cost of Capital (WACC).

Step 3. Calculate Discounted Cash Flow

Once you have the cash flows and discount rate estimates, use the formula to calculate the discounted cash flow value. If you’re considering multiple scenarios, calculate the value for each one.

Step 4. Evaluate the Results

Generally speaking, if the DCF value is higher than the cost of the investment, it’s worth considering. If the value is lower, it’s a less attractive investment. However, it may still be worth doing further research and using other valuation methods.

What Is a Cash Flow Statement And How to Prepare It
What Is a Cash Flow Statement (And How to Prepare It)

Everything you need to know about cash flow statements, what they are, how to use them, how to prepare them, and the best methods to do so.

READ MORE

Discounted Cash Flow Valuation in Excel or Google Sheets

As mentioned above, DCF analysis deals with cash flow and discount rate estimates, not actual values, so it’s always a good idea to gather different estimates to conduct a what-if analysis. Additionally, different valuation methods can help you get a more complete picture of the potential investment.

In other words, a thorough analysis requires the use of spreadsheet software to avoid mistakes and save time. You can use tools like Microsoft Excel and Google Sheets to automate the calculations, so you can evaluate different scenarios and run multiple calculations simultaneously.

However, data management and synchronization are especially important in these cases. The data you need is usually stored in different locations and formats, and various people need to access and analyze that data. Once the results are ready, the reports need to be shared with the interested parties. With Layer, you can do all of this and much more: automate your processes from initial data collection to the final sharing of reports.

Discounted Cash Flow Analysis Example

Imagine you are currently considering investing in a project. The investment has a cost of $150000 and a duration of 5 years.

For the sake of simplicity, let’s assume that the cash flows have been carefully calculated using available historical data and the discount rate is reasonable. Follow the steps below to find the DCF value.

Step 1. Cash Flows

In Google Sheets, create a table like the one below. The first row has the year number, and the second has the projected cash flows for those years.

Discounted Cash Flow DCF Analysis PCF Data
Discounted Cash Flow (DCF) Analysis - PCF Data

Step 2. Discount Rate

Add the discount rate, as a decimal, below the projected cash flows.

Discounted Cash Flow DCF Analysis Discount Rate Data
Discounted Cash Flow (DCF) Analysis - Discount Rate Data

Step 3. Calculate DCF

In a new row, type in the formula to discount the first cash flow projection.

Discounted Cash Flow DCF Analysis Apply Rate
Discounted Cash Flow (DCF) Analysis - Apply Rate

Drag the formula to the right to get the discounted cash flow projections.

Discounted Cash Flow DCF Analysis Drag to Apply
Discounted Cash Flow (DCF) Analysis - Drag to Apply

Add the discounted cash flow values to get the final DCF value.

Discounted Cash Flow DCF Analysis Sum Discounted Rates
Discounted Cash Flow (DCF) Analysis - Sum Discounted Rates

Step 4. Evaluate Results

As you can see, the value for DCF ($ 242 423.38) is higher than the cost of the investment ($ 150 000), so it might be worth considering.

Discounted Cash Flow DCF Analysis Assess DCF Value
Discounted Cash Flow (DCF) Analysis - Assess DCF Value

Profitability Analysis: Step-By-Step Guide

Profitability analysis helps to keep track of the performance and optimize the profitability of a business. Here’s how to perform the analysis step-by-step.

READ MORE
Profitability Analysis Step By Step Guide

Is DCF a Good Valuation Method?

The discounted cash flow method is widely used as many believe it provides a fairer and more objective valuation. DCF analysis doesn’t consider stock value or market sentiment, which can be volatile and sensitive to external factors. Instead, it focuses on the projected growth rates, annual cash flows, and discount rate.

However, some assumptions inherent to the DCF method can limit its usefulness in specific circumstances. In any case, you should never rely on a single formula or method when it comes to forecasting values. Below, you have a summary of the main limitations, as well as some recommendations for use.

Limitations of DCF Analysis

As mentioned above, there are limitations to any method, particularly those that aim to forecast values. Below, you have a summary of the main limitations of DCF analysis.

All Estimates

The DCF relies on estimated values for all parameters. In other words, it doesn’t use any real figures directly. The potential usefulness of the method is entirely dependent on how accurate the projections turn out to be.

Increasing Uncertainty

This method can be used to calculate values for very long-term investments. However, as we get further away from the present time, the values become complete guesswork. With enough accurate historical and contextual data, it’s possible to get well-informed estimates, but if you can’t get reliable projections, you should consider alternative methods.

External Factors

Many factors affect the value of cash flows but cannot be easily quantified. This includes, but is not limited to, changes in demand, competitor behavior, technological advances, and unexpected events.

DCF Analysis Tips

  • Use multiple valuation methods: Don’t rely on a single method to make your decision. When it comes to investments, there are many aspects to consider, and no single method covers them all.
  • Gather contextual information: Make sure you gather as much information as possible about whatever you’re investing in, whether a project or a company.
  • Automate whatever you can: Use spreadsheet software to automate calculations and avoid mistakes. By using a tool like Layer, you can easily manage your data and automate repetitive tasks.

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

Many methods are available to help you evaluate a potential investment. Discounted cash flow analysis is a widely used valuation method that relies on the time value of money and the accuracy of cash flow projections. When dealing with predicted values, it’s best to get multiple estimates and not rely on a single method.

You now know about the benefits and limitations of this valuation method, the steps involved in discounted cash flow analysis, and how to use the DCF formula. You also know how to calculate DCF using Microsoft Excel or Google Sheets and how Layer can help you manage and automate the process.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 10 2022, Updated Nov 23 2022