Is your company planning to invest in real estate, the stock market, assets, a project or another company? If so, you may have done discounted cash flow modelling for it. Discounted Cash Flow (DCF) modelling is a crucial tool in finance for figuring out the value of investments. With it, you can estimate future cash flows and discount them to present value.

This DCF model tutorial aims to demystify how to calculate discounted cash flow in Excel, making it accessible to finance professionals and enthusiasts. DCF models are fundamental for decision-making. It helps investors determine the value of an investment and assess its potential for profitability.

We will cover setting up a discounted cash flow calculator in Excel. But first, let's answer the question "What is a discounted cash flow?" and look at scenarios in which it would be helpful.

What is a discounted cash flow?

We will cover setting up a discounted cash flow calculator in Excel. But first, let's answer the question "What is a discounted cash flow?" and look at scenarios in which it would be helpful.

Discounted cash flow (DCF) is a way to estimate the value of an investment based on its expected future cash flows. With DCF analysis, you can calculate the value of an asset today based on projections of how much money that investment will generate in the future.

It helps to estimate the money you would get from an investment, adjusted for the time value of money.

It's great if your organisation is considering getting another company or buying securities. DCF can help you make sound, data-driven decisions, especially on capital budgeting or operating expenses.

How to calculate discounted cash flow

We share a step-by-step guide on building a discounted cash flow calculator. The guide helps you look at internal reports and external benchmarks and how to set up the formula on a spreadsheet like in Microsoft Excel. You can also book and sit the Excel Financial Modelling Course for Beginners course.

Step 1: Gather Data and Assumptions

The first step to DCF modelling is to gather accurate data. This is crucial to making well-informed DCF model assumptions and calculations. Start by identifying the historical financial data of the company or project you're evaluating. This data includes income statements, balance sheets, and cash flow statements. Consider external factors like market trends, economic conditions, and industry benchmarks. Reliable data sources may include financial reports, databases, and market research.

You must be diligent and ensure your estimates are as updated and solid as possible. The estimated future return on investment (ROI) can look too good to be true if based on hyped-up data. This can result in over-promising. Or if your benchmarks are too conservative, your forecasted cash flow can look too low. So, your investment will seem unbackable when, in fact, it does hold potential. This can result in a missed opportunity.

Step 2: Set Up the Spreadsheet

To set up a DCF model, you need a well-organised spreadsheet. Begin by opening a new worksheet and labelling it properly. Create sections for historical financial data, projections, discount rate assumptions and sensitivity analysis.

Utilise formulas for calculations and link cells to ensure consistency. Consider using Excel, a popular spreadsheet software. Below is a simplified example from SmartSheets on how to structure your spreadsheet.

You can enter details like year-on-year income, fixed and variable expenses, cash outflow, net cash and discounted cash flow. These are your present and cumulative present values. From there, you will come up with the net present value of your company, project or investment.

Step 3: Set Up Your Cash Flow Projection

The heart of the DCF model lies in projecting future cash flows. Financial forecasting with DCF involves estimating the expected cash inflows and outflows over a specified period.

For accurate projections, consider historical trends, industry growth rates, and any potential changes in the business environment. Here's the discounted cash flow formula:

· DCF= CF1/(1+r)1 + CF2/(1+r)2 + CF3/(1+r)3

· In this computation,

· CF1 - Your cash flow for year one

· CF2 - Your cash flow for year two

· CFn - Your cash flow for succeeding years

· r - Your discount rate

As an example, if you were to invest in a private small business earning $6 million a year on average, your discounted cash flow formula could look like this:

DCF= 6.2M/(1+r)1 + 6.5M/(1+r)2 + 6.3M/(1+r)3

DCF= 6.2M/(1+r)1 + 6.5M/(1+r)2 + 6.3M/(1+r)3

Alternatively, Investguiding recommends the following discount rates for SaaS companies doing DCF modelling to calculate a more accurate customer lifetime value (LTV):

· 10% for public companies

· 15% for private companies scaling predictably (e.g. above $10m in ARR, and growing more than 40% year on year)

· 20% for private companies that have not yet reached scale and predictable growth

· As an example, if you were to invest in a private small business earning $6 million a year on average, your discounted cash flow formula could look like this:

· DCF= 6.2M/(1+20%)1 + 6.5M/(1+20%)2 + 6.3M/(1+20%)3

Step 4: Discounting Cash Flows

Discounting is a crucial step in DCF modelling. You can reflect future cash flows back to their present value through it. The discount rate shows the opportunity cost of capital and the risk associated with the investment.

The Weighted Average Cost of Capital (WACC) is typically used as the discount rate. But the discount rate may vary depending on the investment.

Choosing a suitable discount rate is essential. You can do it based on variables like the type of business, industry and company size.

Investguiding recommends the following discount rates for SaaS companies. These rates use DCF to calculate a more accurate customer lifetime value (LTV):

· 10% for public companies

· 15% for private companies scaling predictably (e.g. above $10m in ARR, and growing more than 40% year on year)

· 20% for private companies that have not yet reached scale and predictable growth

If we go by the private small business example, your DFC computation can look like this:

· DCF= 6.2M/(1+20%)1 + 6.5M/(1+20%)2 + 6.3M/(1+20%)3

This is also where getting the latest industry benchmarks and most accurate statistical data comes into play. They will help you adjust for risk accordingly. You should adjust for risk by incorporating a risk premium.

· Present Value= Future Cash Flow/(1+Discount Rate)Number of Periods

Step 5: Run a Sensitivity Analysis

When you do sensitivity analysis in DCF modelling, you assess the impact of changing assumptions on the final valuation. It helps identify the variables that most significantly influence the model's output.

The goal is to study how various sources of uncertainty contribute to the model's overall uncertainty. You can do this using a mathematical model. It helps you uncover what and how external factors interact with a specific undertaking. It also gives you insights into the different elements impacting a project.

Sensitivity tests involve altering one variable at a time while keeping others constant. Here's an example from Indeed:

Jane is a sales manager. She wants to understand how the increase in holiday shoppers affects total sales for her department. Using last year's holiday sales data, Jane learned that total holiday sales are a function of transaction volume and price. She determines that sales increase by 10% when holiday shoppers increase by 5%. Jane can build a financial model and use "what if" statements of sensitivity analysis using this information. Jane now understands that if the increase in holiday shoppers is 50%, total sales should increase by 25%.

Step 6: Interpret Results and Make Decisions

So, you've completed your DCF model. From there, interpreting the results is crucial for making informed financial decisions. To invest or not to invest?

Analyse the calculated present value and compare it to the current market value. A positive difference indicates potential undervaluation, while a negative difference suggests overvaluation. Remember that DCF analysis should not be the sole factor in decision-making. Review it alongside other valuation methods and qualitative factors.

Mastering the art of creating a discounted cash flow model is a valuable skill for business leaders and anyone involved in finance. Individuals can make more informed investment decisions. You just need to gather data, create a spreadsheet, and conduct analysis. You can practise and apply these principles to your investments, big or small. This fosters continuous learning in financial modelling.

For further learning, consider signing up for an online training course on financial modelling and Excel proficiency, such as those offered by Nexacu. Gain hands-on experience and expert guidance to enhance your skills in practical applications.