Calculating the terminal value in Excel can be a daunting task. But with our free template, it's easy! In this blog post, we will provide a step-by-step guide on calculating the terminal value in Excel and a free template you can use to get started. We'll also discuss what the terminal value is and why it's important for businesses. So if you're ready to learn more about this essential business calculation, keep reading!
What is the terminal value, and why is it important to calculate it correctly?
The terminal value is the estimated value of a business at the end of its projected financial planning horizon. This horizon is typically five to ten years into the future.
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%! Therefore, it's essential to correctly calculate the terminal value to get an accurate picture of a business's worth.
Most common terminal value calculation methods
There are 3 most common methods of terminal value calculation: perpetuity growth method, exit method, and no growth perpetuity. All these methods have their own terminal value formula and unique set of assumptions.
Perpetuity growth method
This is the most popular terminal value calculation method. In this method, 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)
This method assumes that the cash flows of a business will grow at a constant rate into perpetuity and the return on capital is higher than the cost of capital. This growth rate is typically the long-term average growth rate of the economy. On the other hand, WACC is the weighted average cost of capital, and the terminal cash flow is the expected cash flow at the end of the projection period.
Typically this method is used for companies with a long history of stable or growing cash flows. For example, a utility company that has been in business for many years and is expected to continue growing at a slow and steady pace.
Exit multiple method
This terminal value calculation method uses an exit multiple to estimate the terminal value of a business. 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.
For example, if a company has a price-to-sales ratio of 3 and the projected revenue for the final year is $100 million, then the terminal value would be:
Terminal Value= Price to Sales Ratio * Projected Revenue
Terminal Value= $100 million * 3 million ($300 million)
The method assumes that the business will be sold at the end of the projection period for a certain multiple of some metric.
Usually, this method is used for young companies that don't have a long history of financials to analyze. For example, a young company that doesn't have much revenue but is growing quickly.
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
This terminal value formula is similar to the perpetuity growth method, but it doesn't assume that the cash flows of a business will grow at a constant rate. Instead, this method assumes that the terminal cash flow will remain the same as the last year's cash flow.
This method is used for businesses with stable cash flows and little to no growth. For example, a telecommunications company that has been in business for many years and is not expected to grow much.
How to use Excel to calculate the terminal value
You can use the terminal value formula in Excel to calculate the terminal value of a business. To do this, you need to input the required information into your spreadsheet depending on the terminal value calculation method you wish to use.
The most important thing to keep in mind is to make sure to use the correct terminal value method based on their assumptions. 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 download for easy calculation!
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 Visa, a stable but growing company 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 Visa 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 visa."
- Growth rate: you can find this by analyzing historical numbers as well as projections.
In this example, the projected terminal cash flow is 38,821,343,550.00 (based on historical growth)
The WACC is 8% (according to Finbox).
The growth rate is 4% (based on research).
After entering these numbers in the free terminal value Excel sheet, the terminal value of Visa is $970,533,588,750.00.
That's it! You have now calculated the terminal value of a company using Excel.
Tips for calculating a company's 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.
In one click, you can get all financial statements, key metrics, and growth metrics historically
Most importantly, you can quickly compare companies across metrics like pe, price to sales, roic, etc., so you can find stock investment opportunities faster.
Conclusion
Calculating the terminal value is an important part of any intrinsic valuation. This is because the terminal value represents the future cash flows of a company and can have a big impact on the final intrinsic value.
When calculating terminal value, it's important to use the method that best reflects the characteristics of the company you are analyzing and to be sure to update the inputs in the terminal value template to match your assumptions.
I hope this guide was helpful in understanding how to calculate the terminal value and gave you some practical tips you can use when conducting your own analysis.
Don't forget to download the free terminal value Excel template!
