Valuation of Discounted Cash Flows: Excel and Calculation Algorithm

 **Excel Template attached below**

“Price is what you pay. Value is what you get”. This is how one of the world’s largest investors, Warren Buffet defines it.

Long before that, Quevedo and the poet Antonio Machado, neither of whom were exactly the Oracle of Omaha, had already said: “It is but a fool who confuses value and price.”

The decision to buy or sell a company is crucial in the business sector. A good purchase (paying a reasonable price at the right time) will have satisfactory repercussions on the future of a firm. However, an acquisition made at the wrong time, or at an excessive price, can lead to corporate bankruptcy.

From the economic point of view, valuing a business is not an easy task and valuing companies is not an exact science. After all, the value of a company, which is understood to be an exact and concise figure, does not exist. Rather, it is conceived as a range of values of subjective appreciation, that is subject to future suppositions.

There are different methods used to establish the value of a company. One method that stands out is the valuation method of discounted cash flows (DCF). It tends to be the most commonly used for evaluating companies when there are expectations of continuity and operation.

Therefore, we intend to elaborate on DCF evaluation by means of this cooperation:

Valuation of Discounted Cash Flows: Excel and Calculation Algorithm Template attached below

Concept

DCF is a valuation method based on a company’s ability to generate future wealth. In other words, a company’s capacity to produce free cash flows, with the understanding that these cash flows are the final outcome of the expected flows of income and expenditures in the course of corporate life. This corporate life is seen as unlimited.

In this sense, the valuation of a company is based upon the formula indicated hereafter, which we will proceed to explain:

Valuation = (CF1 /(1+k)) + (CF2 /(1+k) 2) + … + ((CFn+RVn) /(1+k) n)

Where CF1 is free cash flow for period 1; k is the discount rate; RV is the residual value; and n represents unlimited corporate life.

Thus, by applying a DCF analysis the value of a company is calculated as the sum of projected cash flows and the residual value, whereby cash flows will need to be capitalized in present terms. We can summarize this calculation method in three variables that allow us to obtain a proper valuation:

Cash Flow (CF)

Time Horizon (Residual Value)

Discount Rate (k, WACC)

Next, we will see how these variables behave and how they are calculated:

Calculating free cash flows (FCF)

Cash flow is the amount of money a company will obtain once its needs have been met, both on an operational and investment level, in order to pursue the development of its activity. In other words, it is the amount of money that will remain within a company for the repayment of its external debt and/or remuneration of its shareholders.

Ultimately, it is the profit that a company will obtain regardless of how it is financed.

To calculate it, we take the income statement, more specifically, from the so-called operating income section.

To obtain the cash flow it is necessary to add the tax liability (tax rate) to the operating income. Also, the depreciations need to be included (to the extent they do not produce an outgoing cash flow). In addition, another variable to be added is the investment’s need for assets (purchases of machinery, etc.). And finally, it is necessary to introduce the financial needs for operations (purchase of stocks, etc.) that a company expects it will have.

Projecting cash flows

Cash flows are projected using estimates. Suppositions on growth, investment spending and operational needs will be established.

It is important to keep in mind that the valuation will depend greatly on the value accorded to these suppositions. To do this, it is necessary to know the evolution a company has made in recent years in order for these suppositions to be as real as possible.

Extent of the time horizon to project cash flows

The time horizon sets the value of a company to a specific period of time. Let us use an example. If we project cash flows for five years, we will get the value of a company as if it only had a defined life of 5 years.

Since we assume that a company will not simply last for just five years, but that it will generate future cash flows for an indefinite period of time, we need to calculate the residual value of a company based upon the last projected cash flow.

Therefore, the residual value will be the value of a company in perpetuity. The calculation of cash flows for the period n, taking into account two variables: the discount rate and the growth rate of a company in perpetuity.

The residual value is the result of the following formula:

RV= CFn x ( 1+ g ) / (k – g)

Where CFn is cash flow for the period n; k is the discount rate; g is the growth rate in perpetuity.

What is the discount rate? Why does it need to be applied?

In Spanish, the discount rate is also known as the update rate. This rate enables projected future cash flows to be updated and translated to the present time. After all, the amount of money one has in 2017 is not the same as the amount in 2027. In order to compare both we need to update or “discount” the amount of money to be obtained in 2027 to the present time, in this case 2017.

That is why all calculated and projected cash flows are updated or discounted at the time of valuation. This discount, or update, is done by applying the so-called discount rate.

Determining the discount rate

One of the most widespread methods to calculate the discount rate is the so-called WACC method.

WACC stands for Weighted Average Cost of Capital. WACC is calculated by weighting the costs of debt and equity, always taking into account the financial structure of a company. The following formula shows the procedure for its calculation:

WACC = Ke E/(E+D) + Kd (1-T) D/(E+D)

Where Ke is the cost of equity; Kd is the cost of financial debt;

E represents equity; D financial debt; and

T the tax rate

All of the variables applicable to WACC are easily ascertainable, except for the estimate of the cost of equity (ke).

To estimate the cost of equity, another ratio, called CAPM, is normally used. This abbreviation stands for Capital Asset Pricing Model. The CAPM model is used to calculate the price of an asset taking into account three variables: (I) risk-free rate (Rf), (ii) expected profitability (Rm) and (iii) sector risk (b = Beta).

The risk-free rate determines the expected return on the investment in bonds issued by a Central Bank. Which is used essentially as a place holder because one would not take on an investment with a lower or equal expected return when they could simply purchase a bond with zero risk.

The expected profitability is identified by the return rate the investor expects to obtain, taking into account the market in which the investment is made.

The sector risk is the market risk inherent to a company and its operational and financial activity. Below we enclose a link from which the amounts of this sector risk (Beta) can be extracted.

Ke = Rf + (E (Rm-Rf)) * Beta

Key aspects to be taken into account in the implementation of the DCF model

Under the indicated premises, the valuation method by DCF is based upon the following ideas:

1. Calculation of future cash flows under certain growth and investment suppositions.
2. Calculation of a company’s residual value on the basis of the last projected cash flow by applying a growth rate in perpetuity.
3. Calculation of the discount rate to update cash flows and the residual value, based on the cost of equity and external resources of a company.

The sum of discounted cash flows and the residual value will result in the valuation of a company.

Conclusion

The valuation method by DCF corresponds to the result of the calculation of cash flows a company is able to generate, taking into account certain growth and investment suppositions, and finally, discounting these cash flows back to the present time.

The valuation method by DCF is presently one of the most widespread methods. It is also one of the most accepted models by experts to determine the value of a company. Its calculation is based on future estimates of the ability of a company to generate wealth.

The following link leads to an Excel template that allows you to perform a valuation by calculating DCF.

DFC

If you still have any questions, please feel free to call us. It is not as hard as it looks, believe us.