Terminal Value Formula in Excel: A Free Template - Wisesheets Blog (2024)

At first, it might seem that calculating the terminal value of a business might be cumbersome. But this is not true. You can use Excel to calculate it. In this blog post, we will first take a dive into what is actually the terminal value, then we will provide a step-by-step guide on how to calculate the value in Excel. We will also tell you about a free Excel template you can use for this. Let's get started!

What is Terminal Value and its Importance

The terminal value is the estimated value of a company at the end of its projected financial planning horizon in a Discounted Cash Flow (DCF) model.

In a discounted cash flow (DCF) model, the terminal value often makes up around 75% of the total implied valuation.

The terminal value is important because it represents a significant portion of the total value of a business. In many cases, it can be over 50%! This shows that it is important to accurately estimate the free cash flows (FCFs) beyond the initial forecast period. To ensure that the implied valuation holds credibility, these projected FCFs must be realistic and justifiable.

The Different Methods to Calculate Terminal Value

There are three different methods to calculate the terminal value of a business. They are as follows.

1. Perpetuity Growth Method

This is the most popular terminal value calculation method. It denotes a particular growth rate onto the forecasted cashflows.

The terminal value is calculated by discounting the expected cash flows of a business at an appropriate rate back to the present. The terminal value formula for this method is:

Terminal Value= Terminal Cashflow/(WACC-Growth Rate)

The perpetuity growth model in discounted cash flow (DCF) analysis assumes that a business’s cash flows will grow at a constant rate indefinitely, with the return on capital exceeding the cost of capital. This growth rate typically aligns with the long-term average growth rate of the economy.

The weighted average cost of capital (WACC) is selected as the discount rate, and the terminal cash flow is the expected cash flow at the end of the forecast period. This method is usually used for companies with a history of stable or growing cash flows. Examples of this could be utility companies.

2. Exit Multiple Method

The exit multiple approach estimates a company's terminal value by applying a valuation multiple to a specific financial metric. It predicts what the company will be worth in the future when it's fully mature.

To determine the terminal value, you multiply a chosen financial metric, like EBITDA, in the final year of your forecast by an exit multiple. This exit multiple is based on current market data from similar companies and historical transactions of comparable businesses.

The terminal value formula for this method is:

Terminal Value= Metric multiple * Projected Metric

In this terminal value formula, the projected metric is typically EBITDA, EV, Revenue, or Earnings for the final projected year. These metrics are then multiplied by their respective multiple, like PE ratio, price to sales, EBITDA to enterprise value, etc.

3. No Growth Perpetuity Method

This terminal value calculation method is also known as the Gordon Growth model. The terminal value formula for this method is :

Terminal Value= Terminal Cashflow/WACC

The terminal value formula is similar to the perpetuity growth method but doesn't assume business cash flows will grow consistently. Instead, it assumes the terminal cash flow will match the last year's cash flow.

This method is handy for businesses with steady cash flows and minimal growth, such as an established telecommunications company that doesn't expect much expansion.

Examples of Calculating Terminal Value from Different Methods

1. Perpetuity Growth Method

Let's assume the following:

• The free cash flow (FCF) in the last forecasted year (Year 5) is $10 million.
• The growth rate in perpetuity (g) is 3%.
• The discount rate (r) is 8%.

Using these values, the terminal value (TV) can be calculated as follows:

1. Determine the free cash flow in the last forecasted year:

FCF = $10,000,000

2. Growth rate in perpetuity:

g = 3% = 0.03

3. Discount rate:

r = 8% = 0.08

4. Apply the Perpetuity Growth Method formula:

Terminal Value = 10,000,000 x (1 + 0.03)/(0.08 – 0.03)

5. Calculate the result:

TerminalValue= $206,000,000

So, the terminal value at the end of the forecast period (Year 5) is $206 million. This represents the value of the business's free cash flows growing at a stable rate indefinitely, discounted back to the present value.

2. Exit Multiple Method

Let's assume the following:

• The EBITDA in the last forecasted year (Year 5) is $15 million.
• The appropriate exit multiple for the industry is 8x.

Using these values, the terminal value (TV) can be calculated as follows:

1. Determine the EBITDA in the last forecasted year:

EBITDA = $15,000,000

2. Identify the exit multiple:

ExitMultiple=8

3. Apply the Exit Multiple Method formula:

TerminalValue=EBITDA x Exit Multiple

4. Calculate the result:

TerminalValue=15,000,000×8

TerminalValue=120,000,000

So, the terminal value at the end of the forecast period (Year 5) is $120 million. This represents the value of the business based on its EBITDA and the industry-specific exit multiple.

3. No Growth Perpetuity Method

Let's assume the following:

• The free cash flow (FCF) in the last forecasted year (Year 5) is $12 million.
• The discount rate (r) is 7%.

Using these values, the terminal value (TV) can be calculated as follows:

1. Determine the free cash flow in the last forecasted year:

FCF = $ 12,000,000

2. Discount rate:

r = 7% = 0.07

3. Apply the No Growth Perpetuity Method formula:

TerminalValue = 12,000,000/0.07

4. Calculate the result:

TerminalValue=171,428,571

So, the terminal value at the end of the forecast period (Year 5) is approximately $171.43 million. This represents the value of the business's free cash flows remaining constant indefinitely, discounted back to the present value.

How to Use Excel to Calculate Terminal Value

You can use the terminal value formula in Excel to calculate the terminal value of a business. Just input the required information into your spreadsheet. This depends on the terminal value calculation method you wish to use.

Please keep in mind that you use the correct terminal value method based on their assumptions when using the terminal value formula in Excel. In addition, make sure the model inputs, like WACC, revenue, etc., are reasonable based on your research.

The good news is that you no longer have to worry about the formulas for these methods. Instead, you can simply use the Excel template below, choose your preferred terminal value method, change the inputs, and you will get the terminal value automatically.

Free Template to Calculate Terminal Value – Wisesheets

The first step in using the template is to download it here. Once you have access to the template, choose the preferred terminal value formula you would like to use based on the company you are analyzing.

After this change, the model inputs your assumptions, and the terminal value will be automatically calculated for you.

You can then copy-paste this value or model into your own DCF or analysis model to get the intrinsic value of the company you are analyzing. If you want to learn more about valuation and practical tips to make this process faster on your spreadsheet, click here.

Example calculation with explanation

To better understand the terminal value, let's provide a practical example. Let's say you are analyzing Netflix according to its past financial performance and analyst reports.

As you can imagine, the method that makes the most sense for creating the terminal value of Netflix is the growth perpetuity terminal value formula.

The inputs you need for this method are:

• Terminal cash flow: the final projected cash flow you expect.
• WACC: you can find this by googling "wacc netflix."
• Growth rate: you can find this by analyzing historical numbers as well as projections.

In this example, the projected terminal cash flow is $16 billion(based on historical growth)

The WACC is 8.2% (according to ValueInvesting)

The growth rate is 16% (based on research).

After entering these numbers in the free terminal value Excel sheet, the terminal value of $205,128,205,128.21.

Tips for Calculating The Terminal Value

Use the terminal value calculation method that best reflects the company you are analyzing.

• When a company is growing steadily, use the growth perpetuity terminal value formula.
• If the business is stable, use the no-growth perpetuity method
• If the company has a short track record and is not stable, use the exit multiple method

Be sure to change the inputs in the terminal value template to match your assumptions and the company you are analyzing.

Lastly, use Wisesheets to analyze important terminal value information such as historical financials and key metrics in Excel faster.

Conclusion

Figuring out the terminal value of a company is very important. The future cash flows captured in this way heavily impact the final intrinsic value. In calculating the terminal value of a company, it's important to choose a method that suits the company's unique situation. This should be done to ensure that the inputs in the terminal value template align with your assumptions for best results.