How to Calculate Terminal Value in Excel [Formula + Step-by-Step Guide]

Before diving into Excel, it's important to have a clear framework for your calculation. This starts with a robust financial model that forecasts cash flows for a discrete period, typically five to ten years. The terminal value calculation then extends this model into the future, beyond this discrete period. C-level executives must ensure the assumptions used, such as growth rates and WACC, are realistic and reflective of the organization's strategic planning and market conditions.

Using a consulting firm's template or strategy can help standardize the process, ensuring consistency and accuracy in your calculations. Many leading consulting firms, including McKinsey and Bain, offer insights and tools that can be adapted to your organization's needs. These resources often include best practices for selecting appropriate growth rates and WACC, critical inputs in the terminal value calculation.

When setting up your Excel model, it's beneficial to structure your workbook with clear, separate sections for assumptions, calculations, and outputs. This not only aids in transparency and ease of understanding but also facilitates sensitivity analysis, allowing executives to see how changes in key assumptions impact the terminal value.

To calculate terminal value using the Gordon Growth Model in Excel, follow these steps:

1. Forecast the free cash flow (FCF) for the final year of your explicit forecast period.
2. Determine the long-term growth rate. This rate should be conservative and sustainable, often set to the long-term inflation rate or GDP growth rate.
3. Input the organization's weighted average cost of capital (WACC).
4. Use the formula "=FCF * (1 + growth rate) / (WACC - growth rate)" in Excel to calculate the terminal value.
5. Discount this terminal value back to present value using the formula "=Terminal Value / (1 + WACC)^n", where n is the number of years from the present to the end of the forecast period.

For the Exit Multiple Method, the process involves selecting an appropriate multiple (e.g., EV/EBITDA) and applying it to the financial metric forecasted for the last year of your period. This method is particularly useful when comparable industry data is available and provides a realistic basis for the multiple.

In practice, calculating terminal value is both an art and a science, requiring judgment and experience. It's crucial to review and adjust assumptions regularly, especially in rapidly changing markets. Real-world examples, such as valuations in merger and acquisition scenarios, often reveal the nuances of applying these methods and underscore the importance of a meticulous approach.

Calculating terminal value in Excel is a fundamental skill for C-level executives involved in financial planning and valuation. Whether using the Gordon Growth Model or the Exit Multiple Method, the key is to base your calculations on realistic, well-justified assumptions. Leveraging frameworks and templates from reputable consulting firms can enhance the accuracy and reliability of your models. Remember, the terminal value calculation is a powerful tool in strategic decision-making, providing insights into the long-term value of an organization's cash flows.