Discounted Cash Flow Valuation Excel

• Version
• 9'364 Downloads
• 300KB File Size
• January 3, 2022 Updated
• 0 Number of comments
• ★★★★★

  ★★★★★
  Rating

As an investor, it is important to be able to evaluate the worth of current investments and future investment opportunities.

The profit that can be materialized as an investor/business owner is not restricted to dividends/profits. The final sale value of the asset is a major part of your overall return.

There are numerous methods of valuation currently being used in the market:

• relative valuation,
• net assets value,
• discounted cash flows, and
• market comps.

Discounted cash flow is a widely used method of valuation, often used for evaluating companies with strong projected future cash flow.

This is the only method which assigns more importance to the future cash generation capacity of the company – not the current cash flow.

Our Discounted Cash Flow Valuation Template is designed to assist you through the journey of valuation. The template comes with various scenarios along with sensitivity analysis.

Read this Excel tutorial to find out how you can easily estimate the net present value of your holdings with our free template, step by step.

What is Discounted Cash Flow Valuation?

Discounted cash flows allows you to value your holdings today based on cash flows to be generated over the future period. These cash flows are then discounted using a discount rate, termed ‘cost of capital,’ to arrive at the present value of investment.

The reason behind discounting the cash flows is that the value of $1 to be earned in the future may not be the same as the value today.

The value calculated through this method is then compared to the cost of investment today to evaluate whether the same is profitable or not. Higher value in comparison to its cost of acquisition denotes that a profitable opportunity can be materialized by the investor.

The elements that are part of the net present value (NPV formula) are:

• Cash flows over the future period: This represents the income earned in cash on any given security or investment in the form of interest, dividend, or profit. To learn more about the types of cash flows, click here.
• Discount rate or cost of capital: This is the required rate of return that an average investor expects to receive from the investment. The same is also referred to as the weighted average cost of capital. To learn more about the discount rate, click here.
• Number of years under projection: Cash flows earned are discounted at the cost of capital for the period to which it relates. Usually the period may be monthly, quarterly or yearly depending upon the frequency of cash flows.
• Terminal Value: Cash flows from an investment may run for an infinite period (theoretically). An investor can not always correctly determine the period for which he will keep on receiving the cash flows. The concept of terminal value is used to solve this.

A single value is estimated at the end of 5 or 10 years which is the representative of the total value over the future period. The same is then discounted at the cost of capital to arrive at the net present value (NPV).

There are multiple formulas to calculate the terminal value. The top methods are:

• Exit multiple (EBITDA or sales or PE multiple),
• Perpetual growth rate, and
• No growth formula.

To learn about the above formulas in detail, click here.

How to Use The Template

You can execute your own DCF valuation model by inserting some basic data into the template. In this article, we break down the entire procedure into simple steps.

To use the template, you will need to replace data that is in blue with your own information.

Contents of the Template

The main elements of this template are:

1. Date of Valuation: Enter the date of the valuation. The valuation date determines the period for which the cash flows will be discounted.

In our template, the estimates have been prepared for a period of 10 years and the cash flows are discounted on a yearly basis. The input affects the field highlighted in the screenshot below:

2. Revenue: Revenue for the previous year acts as a base for projecting the future revenue. You can check the previous year’s financial statements to ascertain the total revenue earned.

After you enter the revenue, future revenue will automatically be calculated based on short-term and long-term revenue growth rate.

3. EBITDA: You have to fill this section with the EBITDA earned in the previous year. This should be extracted from previous year’s financial statements. After having filled EBITDA, the template will automatically convert the amount of EBITDA to a percentage of revenue based rate.

The rate calculated above is applied to the projected revenue (calculated in the previous section) to arrive at the EBITDA figures in the future years. This will further be adjusted to arrive at the net cash flows.

4. Depreciation and Amortization: In order to calculate the tax on projected profits of the company, you need to first deduct the depreciation (and/or amortization) figure from the EBITDA calculated above.

After you have entered the depreciation (and/or amortization) figure, the template will automatically convert it as a percentage of revenue and will apply to the projected revenue figures to arrive at future estimates.

After having calculated the tax, figure of depreciation and amortization will again be added back to the cash flow, as this is a non-cash item.

5. Capital Expenditures: It denotes the amount spent by the company on acquisition of fixed assets.

You can view the capital expenditure figure from the cash flow statement of the previous year and that is taken as the basis for future projections. The amount will then be converted as a percentage of sales and applied to the future revenue figures to arrive at the projected capital expenditure figures.

6. Working Capital: Working capital is the difference between current assets and current liabilities. Net changes in working capital are adjusted in the free cash flow projections to arrive at the free cash flow figure.

You can ascertain the figure from the previous year’s balance sheet.

Similarly, the change in operating assets and liabilities represents non-cash current assets and liabilities like accounts receivables and inventory and are treated in the same way as changes in working capital.

7. Discount Rate: The Discount rate is the required rate of return below which the investment opportunity is considered as a non-optimal use of cash. Sources of capital (including equity, preference shares, debentures, debt, etc.) are considered while calculating the discount rate or weighted average cost of capital. The discount rate is a weighted average of cost of equity and cost of debt. It basically is the return sought by the investor in lieu of the financial risk taken for investing in the project. You can also consider IRR (internal rate of return) as discount rate for valuing an investment.

8. Discount Rate Sensitivity: The future is uncertain. In order to determine the valuation of investment in the case of adverse or favorable changes in the economy, you can manipulate the figures using the sensitivity analysis option in the template.

The sensitivity factor will then be applied to the discount rate showcasing the three different scenarios.

This can be illustrated using an example. If the discount rate estimates for your business is 12% and the discount rate sensitivity is 1%, the discount rate in different scenarios will be:

• Case 1 = 12%
• Case 2= (12%+1%) = 13%
• Case 3 = (12%-1%) = 11%

The resultant present value multiplier will be depicted in the template as follows:

To learn more, click here.

9. Tax Rate: You have to input the corporate tax rate in case of company valuation or individual tax rate in case of natural persons.

10. Short-term and Long-term Revenue Growth Rate: Used in calculating growth rate of revenue for the projected period. Short-term growth rate represents the increase in sales for the projected period.

Long-term growth rate represents the assumption that the company will grow perpetually at this particular rate and is used for calculating terminal value.

Advantages of Discounted Cash Flow Valuation

• Use of the core aspects of business operations including growth rate, discount rate, free cash flows from operations etc. will allow you to calculate the intrinsic value of an investment.
• You can use the method to value the whole company or just some components of it.
• It correctly assigns weight to the cash flows based on the period under projections and takes into account the time value of money.
• It allows you to compare cost of capital to the projected rate of return, which is not possible in other valuation methods.
• The method makes the use of pure cash flows rather than earnings that can be manipulated by accounting policies and amortization rules.
• It is the only method which calculates the value of business based on future outcomes rather than applying multiples on historical results.
• The method can also take into account levered and unlevered valuations. Levered DCF measures the value of the equity alone whereas the unlevered DCF values the company as a whole including debt. You can just deduct net debt from unlevered DCF value to come up with the equity value.

Limitations of the Method

• One of the main elements of discounted cash flow valuation, i.e. discount rate, is an assumption and can be different for different sets of investors, and can change over the course of time. The cost of equity and cost of debt are never static as the financial risk keeps on evolving for a company.
• The method might not be applicable to cases where projects are heavily complicated and cash flows are highly erratic.
• DCF methodology can’t be used for many technology startups whose focus is growth in user base as compared to growing free cash flows.
• Under the method, capital expenditure is projected considering a constant rate, while that may not be the case in real life. For instance, if you are thinking of expanding your business in year 5, the capital expenditure may be higher in year 5 as compared to other years.
• Share of terminal value in overall valuation figure can be much higher than that of the operating cash flows. Thus valuation is largely dependent on the terminal value.

These factors do not disqualify the discounted cash flow model; just that you need to carefully analyze the final values and assumptions taken.

Comparison with Other Valuation Methods

There is no single valuation model that you can apply to all situations. The choice of appropriate method will depend upon the availability and accuracy of basic inputs, nature of the industry, characteristics of the investment, and the development stage of the company.

Discounted cash flows model is also suitable for companies not paying dividends or making irregular dividend payments, or companies having multiple revenue streams. It is best suited for companies having a detailed operational history.

For the detailed analysis of different methods, click here.

Conclusion

Although the method is widely used by investors, sometimes it may produce misleading figures. It may happen in the case where the company sells its assets to inflate the cash flows, while the actual earnings of the company may be zero or negligible.

On the other hand, if you use it wisely, especially by considering each minor detail that has been used in the calculation of cash flows, the method is considered to be the most reliable valuation model.

When you use DCF in combination with other valuation methods, most of the individual shortfalls of different methods are eliminated and you get a fair valuation.