DCF Model in Excel – Your Complete Guide To DCF Valuations

Table of Contents

Introduction

Nowadays, the DCF model is widely used in excel by companies. But, have you wondered what would be the present value of the investments that will give you a certain amount of return in the future? Well, the calculations of discounted cash flows will determine the present value of your investments. By using DCF valuations, you can perform financial modeling of a company.

The Discounted Cash Flow model is one of the most powerful tools that a company can use to predict its value. It tells us how much a company is worth by discounting the cash that the company can make in the future. In this article, we look at the DCF model in-depth and show you how to use it to value your company and make better business decisions.

What Is DCF Model?

Image showing a man performing financial modelling of a company and making a dcf model in excel
Source: Photo by Photomix

DCF stands for Discounted Cash Flow. DCF is basically used to calculate the present value of the cash flow of the company. It can guess the value of an investment based on expected cash flows. In other words, the DCF model tries to predict the value of investment today. It is basically based on methods that will determine how much money the investment will make in the future. In Excel, you can use the DCF model. Here, we try to use the future value of an investment to calculate the present value of the investment. If the DCF is above the cost of the investment, then this would give positive returns. Similarly, the DCF might give negative or no returns. This happens when the present value is less or equal to DCF.

What Is Free Cash Flow?

Free cash flow is the money left out after paying all the expenses and taxes. In other words, it tells the company how much cash gets generated after paying all the expenses. The company is free to use this cash for any of its purposes. Investors use this method to see if there is any fraud happening in the numbers. It also gives an idea to investors how much money will be eventually distributed as a dividend. You can use the DCF model in excel to calculate these methods.

Formula To Calculate Free Cash Flow

Image showing the calcualation of Discounted Cash Flow
Source: Photo by Nothing Ahead

There are various ways to calculate free cash flow. Many times, companies produce financial statements. To calculate FCF, investors can use income statements, cash flow statements, and balance sheets. Therefore, investors use proper methods to calculate free cash flow. The formula for calculating Free cash flow is;

FCF = Operating cash flow – Capital Expenditures 

or

FCF = Net income + DD – CC – Capital expenditures

Here, Operating cash flow refers to the amount of cash a company generates from the money it brings in. Whereas, Capital expenditure is the money spent on the assets for their repairs and costs. At times, a company’s share price can be low and FCF can be high. This indicates that the earnings will also rise in the future. A healthy FCF means that the company has enough money to pay its bills. This is a positive indication of business health. Sometimes the FCF of the company might be low. This states that the company might fall short of money or use debt in the future.

What Is Time Value Of Money

An image showing the dcf model in excel
Source : Photo by Olia Danilevich

Time value of money (TVM) means that the present value of money today is more than that in the future. In other words, if you invest your money today only then your money will grow. Otherwise, the chance gets lost. It is also known as Present discounted value. Compounding is an important element of TVM. Another reason why the value falls down is due to inflation. The time of money is a very important concept in the DCF model in Excel.

For example, a person has a choice to choose $1200 now or 2 years later. If he receives it now, he can invest it and earn more money till the end of 2 years. Or else if he rejects, he will not make any money now. The DCF model is useful in excel to carry out the Time value of money. The formula to calculate the Time value of money is; 

FV = PV x [ 1 + (i / n) ] (n x t)

For example, assume you invest $5000 at a compound interest of 10% annually for one year. The future value of that investment is;
FV= 5000 [1+ (10%/ 1)] ^ (1×1) = $5,500

Similarly, you can do the same calculation and find out the present value of your investment.

How Can DCF Valuations Help Investors?

Image showing investors looking at the financial modeling of a company
Photo by RODNAE Productions

Dcf valuations prove to be a huge support for Investors. In other words, investors can use the DCF financial modeling whenever they try to invest their money. For example, if you want to buy real estate property, buy shares, or make an investment, use DCF model valuations. This will help you to predict expected cash flows from that investment. Likewise, if the investment amount is below the DCF model, then your investment may get undervalued. Similarly, if the investment amount is high, then your investment gets overpriced. You can take proper steps and reinvest your money.

You can apply the DCF model to value a stock, company, project, and many other assets or activities. Thus it is widely used in both the investment industry and corporate sector. This DCF model is suitable for larger companies with slow growth. Insurance companies can use this DCF financial modeling to calculate the expected cash flows of the company.

Formula for DCF Financial Modeling

Image showing a man doing the calculations for dcf financial modeling and making the dcf model in excel
Source: Photo by Oleg Magni

You might be wondering, why is there a need to calculate the DCF model in Excel, when we have NPV in the market. Well, both NPV and DCF are almost similar, but there is a slight difference between both. The Net present value tells you the current worth of your investment. Whereas, the DCF model helps to determine the value of predicted cash flows in today’s time. Now that you have learned about what the DCF model means, let’s have a look over the formula of the DCF model in excel. The formula for calculation of the DCF model used in excel is; 

DCF = CF₁  + CF₂CF₃ + …….. CFₙ
          1+r₁   1 + r₂   1+ r₃          1+ rₙ

Types Of DCF Techniques

An image showing the dcf valuations techniques
Source : Photo by Pixabay

The DCF model gives an almost accurate present worth of an investment. Financial analysts widely use the DCF models. They use it to compute the present value of the investment by using projected cash flows. There are various types of DCF valuations methods used, here is the list below.

The Internal Rate of Return (IRR) Method

The Internal rate of return or IRR is the rate of return at which the NPV comes out to be equal or becomes zero. In other words, IRR is basically an annual growth rate that an investment gets expected to generate. While using IRR, the formula might seem the same as NPV but it differs slightly. IRR is the annual rate of return that makes NPV equal to zero. It is usually considered that a higher IRR is the best. This metric is also useful to determine which investment option is the best. For example, if there are many similar investment options, you can choose an investment that has a higher IRR. 

The Net Present Value (NPV) Method

Under the Net present value method, you need to find out the present value of the expected cash flows. In simple words, NPV is the difference between the present value of inflows and the present value of outflows. The name itself says finding the present value of the investment today. It also shows the present value of all the cash flows until now. You can use the DCF model in capital budgeting and investment planning. The formula for calculation of NPV is;

NPV = Present value of Cash Inflow – Present value of Cash outflow

How To Do Discounted Cash Flow Model In Excel

Image showing a woman doing dcf calcualtions
Source: Photo by Mikhail Nilov

The DCF financial modeling is an important tool for financial analysis. Analysts also use the DCF model to perform analysis in Excel. You can also do the calculation of DCF finance modeling through some easy steps.

  1. Build Up Cash flow statement: The first step in the calculation of discounted cash flow is creating a cash flow statement of your company. Once you have created it, start analyzing the items in the statement. And eventually, create a DCF financial modeling of the cash flow statement.
  2. Calculate WACC: After finding out the terminal value, you need to now calculate WACC. It basically means Weighted Average Cost of Capital.
  3. Find out Terminal Value: The next step for the calculation of discounted cash flow is finding out the terminal value. Terminal value basically assumes that the company will grow at the same rate after the predicted period.
  4. Calculate Enterprise value and Intrinsic value: Using WACC, you need to now calculate enterprise value. Also, calculate the intrinsic value. It is basically the cost needed to acquire a business. This will also help in the DCF financial modeling of the company.

How To Make DCF Model In Excel

You might be thinking this has a tough job, but that’s not the case. If you want to do DCF valuations in excel, it can be done easily. You only need to follow the steps properly. There are four steps to do the DCF model in excel.

  1. Open Google sheets on your desktop and also create a spreadsheet.
  2. Calculate WACC on the first sheet page, using the steps above
  3. Click on the [+] tab below and create another sheet. Calculate ETR and IR needed for calculation of WACC.
    Calculate FCFF (Free Cash flow to the firm). The formula is ;
    FCFF = NOPAT + D&A – Capital exp + – Change in Working capital
  4. For this, in a separate sheet also calculate NOPAT and CWC.
  5. Create a separate sheet, name it 5YRGR and calculate 5 years Grow rate. Also, calculate Long term growth rate using GDP and Inflation rate.
  6. By using the formula, calculate Enterprise value.
  7. Lastly, calculate the Terminal value using the formula that is also explained in the example below.

Example Of DCF Model Of Apple

Image showing apple products
Source: Photo by Nana Dua

Apple has been one of the largest tech companies in the world. The craze for Apple products is increasing day by day. The Business Strategy for Apple is also gaining popularity. The profits of Apple company are rising high. Thus, it is necessary to forecast the company’s performance for the next years. You can also do this by using the DCF financial modeling in excel. The DCF valuations help to predict the future performance of a company. Here’s a simple way to conduct the DCF valuations of Apple company using data from sources.

Calculating WACC

Weighted Average Cost of Capital or WACC is the average cost a company can raise money. The formula for WACC is;
WACC = (We x Ke) + (Wd x Kd)
Here, We mean the weight of equity whereas Wd means the weight of debt. Ke is the cost of equity and Kd means the cost of acquiring debt. We calculated for Apple is 4.43% and Wd is 95.57%. Similarly, by using the formula WACC comes out to be 7.27%.

Calculating Terminal value

Terminal value is also a kind of assumption that a company will grow at a constant rate for years. The formula for calculating Terminal value is;
Terminal value = (5yrs FCFF) x (1+ LTGR)
                                 WACC – LTGR

The Long term growth rate or LTGR for Apple company assumes to be 2%. Likewise, the Sum total of 5yrs FCFF is 161,258.26. And, the WACC comes out to be 7.27%. Thus, the terminal value of Apple company comes out to be 3,059,928.95. If you try to take the present value of the terminal value, it comes out to be 2,154,368.25.

Calculate Enterprise value

Enterprise value is the entire value of the company in the market. This also includes market capitalization, outstanding debt, interest, and cash and cash equivalents. The formula for calculating Enterprise value is;

Enterprise value = Market capitalization + Preferred stock + Outstanding debt + Minority interest – Cash and
                              cash equivalents

The DCF valuations of Apple company end with Enterprise value. As per the data available, the debt is $89779, whereas total equity stands out to be $2,571,236.63. Therefore, total enterprise value is $2,661,015.63. 

Finding Intrinsic value

After calculating the enterprise value of the firm, the last step also includes finding the Intrinsic or fair value. Intrinsic value is basically the calculated value of the company or the stock. The formula for calculating Intrinsic value is;
Intrinsic value = Enterprise value – Long term debt + Cash and cash equivalents
                            Outstanding shares
The Enterprise value is $2,661,015.63, Long term debt is $89,779, Cash is $62,639 and outstanding shares are $16,864.92 as per the figures. The Intrinsic value as per the DCF financial modeling comes out to be $156.17.

Pros And Cons Of Using DCF model

While preparing the DCF model, one question arises: What is the company’s future cash flows value at the current time? This question can be easily answered through the DCF model by using Excel. DCF model valuations can also prove to be very useful for analysts in excel. Here are some advantages and disadvantages of the DCF model.

Advantages of DCF model

The advantages of the DCF model used in Excel are listed below;

1. Detailed process

The process for the calculation of discounted cash flow is extremely detailed. To arrive at a value, it uses particular data. It includes important factors about a firm like cash flow predictions, growth rate, and other factors.

2. Helps to find Intrinsic value

The DCF model gets calculated for this purpose only. The main aim of DCF is to calculate the present value of the cash flows. This method is likely going to give a near-exact value of the investment made. By using the above-mentioned formula, you can calculate the DCF value of cash flow.

3. No comparison needed

Once you have calculated the DCF value using Excel, you don’t need to compare it with others. Every company might have a different cash flow. This is because every company invests different amounts. For example, ABC Ltd might be having cash flows of $5000 whereas when you calculate the DCF of $6000.

4. Important during mergers and acquisitions

DCF model can prove to be an important tool during mergers and acquisitions. During mergers, it is frequently used. It helps to understand the present value of investment made in the future. For example, if ABC Ltd is taking over JACK Ltd, it will definitely calculate the DCF model. it means if they invest in this company, is it worth their investment. You can also do calculations of discounted cash flow in excel. This would basically help the firm to analyze their decisions regarding their investment in another company.

5. Performed in Excel

The DCF valuations can be easily done in Excel. It means you can do calculations of the discounted cash flow (DCF) model in the excel spreadsheet. With the help of specialized software, you can do the DCF modeling analysis in Excel.

6. Calculates IRR

The main advantage of the DCF model is that it calculates the Internal Rate of Return (IRR). It basically allows you to calculate IRR which will help you compare investments. In other words, it helps you to make a comparison between similar investments.

7. Sensitivity Analysis

DCF model allows you to perform Sensitivity analysis. It helps the analysts to find out which assumptions in the investment can affect the future value of the investment. This also includes variables like discounted rate or growth rate.

Disadvantages of DCF model

The Disadvantages of the DCF model used in Excel are as follows;

1. Complex method

The DCF model can be sometimes difficult to perform. The data for DCF analysis is very difficult to obtain. Therefore, this method might be not so easy to calculate.

2. Neglects DCF of competitors

Although it was an advantage, it is also a disadvantage. DCF can sometimes calculate values that are different from its competitors. It means the DCF model is not using the market variables for calculation. This will eventually make wrong decisions.

Conclusion

This article discussed the meaning of the DCF model used in Excel. Also, it talks about the meaning of free cash flow and the time value of money. Besides this, it also states how DCF valuations can be useful for investors. This article also describes the formula of DCF financial modeling of the company. Lastly, the DCF model also has some advantages and disadvantages.