Flevy Management Insights Q&A
How to calculate terminal value using Excel?


This article provides a detailed response to: How to calculate terminal value using Excel? For a comprehensive understanding of Company Financial Model, we also include relevant case studies for further reading and links to Company Financial Model best practice resources.

TLDR Calculate terminal value in Excel using the Gordon Growth Model or Exit Multiple Method by inputting final year's FCF, growth rate, and WACC.

Reading time: 5 minutes

Before we begin, let's review some important management concepts, as they related to this question.

What does Financial Modeling mean?
What does Terminal Value Calculation mean?
What does Weighted Average Cost of Capital (WACC) mean?
What does Sensitivity Analysis mean?


Calculating terminal value in Excel is a critical component of financial modeling, especially for C-level executives engaged in strategic planning, mergers and acquisitions, and long-term financial planning. The terminal value represents the future value of an organization's cash flows beyond a forecasted period, typically extending into perpetuity. This calculation is pivotal for understanding the total value of an organization in today's dollars, making it an indispensable tool in the arsenal of strategic decision-making.

The most common methods for calculating terminal value are the Gordon Growth Model (GGM) and the Exit Multiple Method. The GGM assumes that cash flows will grow at a constant rate forever, while the Exit Multiple Method calculates terminal value based on a multiple of some financial metric, such as EBITDA, at the end of the forecast period. Each method has its place, depending on the organization's growth outlook and the availability of industry benchmarks.

To calculate terminal value in Excel using the Gordon Growth Model, you'll need to determine the final year's free cash flow (FCF), the long-term growth rate of these cash flows, and the organization's weighted average cost of capital (WACC). The formula in Excel would be "=FCF * (1 + growth rate) / (WACC - growth rate)". This straightforward approach provides a present value of the expected cash flows beyond the forecasted period, assuming a perpetuity growth model.

Framework for Terminal Value Calculation

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.

Are you familiar with Flevy? We are you shortcut to immediate value.
Flevy provides business best practices—the same as those produced by top-tier consulting firms and used by Fortune 100 companies. Our best practice business frameworks, financial models, and templates are of the same caliber as those produced by top-tier management consulting firms, like McKinsey, BCG, Bain, Deloitte, and Accenture. Most were developed by seasoned executives and consultants with 20+ years of experience.

Trusted by over 10,000+ Client Organizations
Since 2012, we have provided best practices to over 10,000 businesses and organizations of all sizes, from startups and small businesses to the Fortune 100, in over 130 countries.
AT&T GE Cisco Intel IBM Coke Dell Toyota HP Nike Samsung Microsoft Astrazeneca JP Morgan KPMG Walgreens Walmart 3M Kaiser Oracle SAP Google E&Y Volvo Bosch Merck Fedex Shell Amgen Eli Lilly Roche AIG Abbott Amazon PwC T-Mobile Broadcom Bayer Pearson Titleist ConEd Pfizer NTT Data Schwab

Step-by-Step Guide to Calculating Terminal Value in Excel

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.

Conclusion

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.

Best Practices in Company Financial Model

Here are best practices relevant to Company Financial Model from the Flevy Marketplace. View all our Company Financial Model materials here.

Did you know?
The average daily rate of a McKinsey consultant is $6,625 (not including expenses). The average price of a Flevy document is $65.

Explore all of our best practices in: Company Financial Model

Company Financial Model Case Studies

For a practical understanding of Company Financial Model, take a look at these case studies.

No case studies related to Company Financial Model found.

Explore all Flevy Management Case Studies

Related Questions

Here are our additional questions you may be interested in.

How can companies ensure the accuracy and reliability of their financial models in rapidly changing markets?
To ensure financial model accuracy in volatile markets, companies should adopt a Flexible Modeling Framework, strengthen Data Integrity and Governance, and engage in Continuous Learning and Improvement. [Read full explanation]
How can companies leverage advanced analytics and machine learning to enhance the predictive accuracy of their financial models?
Companies can significantly enhance the predictive accuracy of their financial models by integrating advanced analytics and machine learning, leveraging big data and sophisticated algorithms to uncover insights, forecast trends, and optimize strategies for improved decision-making and profitability. [Read full explanation]
What strategies can companies employ to ensure their financial models remain relevant amidst rapid technological advancements?
To ensure financial models remain relevant amidst technological advancements, companies should embrace Digital Transformation, focus on Scenario Planning and Stress Testing, and invest in Continuous Learning and Skills Development. [Read full explanation]
In what ways can real-time data analytics enhance the predictive accuracy of company financial models?
Real-time data analytics enhances predictive accuracy of financial models by incorporating current market conditions, improving granularity, and leveraging machine learning for better forecasting, operational efficiency, and cost management. [Read full explanation]
What role does scenario planning and stress testing play in preparing companies for unforeseen business disruptions?
Scenario Planning and Stress Testing are essential for Strategic Planning and Risk Management, enabling organizations to anticipate disruptions, minimize risks, and seize opportunities for resilience and long-term success. [Read full explanation]
What strategies can businesses employ to effectively integrate non-financial data, such as customer satisfaction metrics, into their financial models?
Discover how businesses can enhance Strategic Planning and Operational Excellence by integrating non-financial data, like customer satisfaction, into financial models through Unified Data Frameworks, Advanced Analytics, and Performance Management Systems. [Read full explanation]

Source: Executive Q&A: Company Financial Model Questions, Flevy Management Insights, 2024


Flevy is the world's largest knowledge base of best practices.


Leverage the Experience of Experts.

Find documents of the same caliber as those used by top-tier consulting firms, like McKinsey, BCG, Bain, Deloitte, Accenture.

Download Immediately and Use.

Our PowerPoint presentations, Excel workbooks, and Word documents are completely customizable, including rebrandable.

Save Time, Effort, and Money.

Save yourself and your employees countless hours. Use that time to work on more value-added and fulfilling activities.




Read Customer Testimonials



Download our FREE Strategy & Transformation Framework Templates

Download our free compilation of 50+ Strategy & Transformation slides and templates. Frameworks include McKinsey 7-S Strategy Model, Balanced Scorecard, Disruptive Innovation, BCG Experience Curve, and many more.