This article provides a detailed response to: How to Calculate Terminal Value in Excel [Formula + Step-by-Step Guide] For a comprehensive understanding of Company Financial Model, we also include relevant case studies for further reading and links to Company Financial Model templates.
TLDR Calculate terminal value in Excel using 2 methods: (1) Gordon Growth Model—Terminal Value = Final Year FCF × (1 + Growth Rate) ÷ (Discount Rate - Growth Rate), or (2) Exit Multiple Method—Terminal Value = Final Year EBITDA × Exit Multiple. Both methods require building DCF models in Excel with proper cell references, sensitivity analysis tables, and circular reference handling for WACC calculations.
Before we begin, let's review some important management concepts, as they relate to this question.
Calculating terminal value in Excel is a fundamental skill for financial analysts conducting discounted cash flow (DCF) valuations and business appraisals. Terminal value represents the present value of all future cash flows beyond the explicit forecast period, typically accounting for 60-80% of total enterprise value in DCF models. Understanding the terminal value formula in Excel and implementing it correctly is critical for investment bankers, corporate development teams, and private equity professionals making acquisition, investment, or strategic decisions. Excel provides the computational power and flexibility needed to build robust terminal value calculations with sensitivity analysis and scenario modeling.
The 2 primary methods for calculating terminal value in Excel are the Perpetuity Growth Model (also called Gordon Growth Model) and the Exit Multiple Method. The terminal value formula Excel implementation for the Perpetuity Growth Model is: Terminal Value = [Final Year Unlevered Free Cash Flow × (1 + Perpetuity Growth Rate)] ÷ [WACC - Perpetuity Growth Rate]. This formula assumes the business will grow at a constant rate in perpetuity—typically 2-3% reflecting long-term GDP growth. The Exit Multiple Method calculates terminal value Excel as: Terminal Value = Final Year EBITDA × Exit EBITDA Multiple (or Revenue Multiple). This method benchmarks the terminal value against market trading multiples or M&A transaction comparables. Best practice in terminal value calculation Excel involves running both methods and comparing results—significant discrepancies indicate assumption problems or valuation risks requiring further investigation.
Implementing the terminal value formula in Excel requires careful attention to cell structure, formula construction, and sensitivity analysis. Start by establishing your DCF model foundation: build historical and projected financial statements, calculate unlevered free cash flow for each forecast year, and determine your weighted average cost of capital (WACC). For the Gordon Growth Model terminal value Excel calculation, create dedicated assumption cells for perpetuity growth rate (typically cell input) and WACC. The terminal value formula should reference your final forecast year FCF and growth assumptions using absolute cell references ($ signs) for assumptions but relative references for FCF. Add the terminal value to your DCF model by discounting it back to present value using: PV of Terminal Value = Terminal Value ÷ (1 + WACC)^Number of Forecast Years. Leading investment banks emphasize creating sensitivity tables showing how terminal value changes with different growth rates and WACC assumptions—Excel data tables automate this analysis. Common Excel errors to avoid include: circular references when WACC depends on enterprise value, using inconsistent time periods, applying growth rates incorrectly, and forgetting to discount terminal value to present value.
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.
Step-by-Step Guide to Calculating Terminal Value in Excel
To calculate terminal value using the Gordon Growth Model in Excel, follow these steps:
- Forecast the free cash flow (FCF) for the final year of your explicit forecast period.
- 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.
- Input the organization's weighted average cost of capital (WACC).
- Use the formula "=FCF * (1 + growth rate) / (WACC - growth rate)" in Excel to calculate the terminal value.
- 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.
Company Financial Model Document Resources
Here are templates, frameworks, and toolkits relevant to Company Financial Model from the Flevy Marketplace. View all our Company Financial Model templates here.
Explore all of our templates 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.
Strategy & Operations, Management Consulting
This Q&A article was reviewed by Mark Bridges. Mark is a Senior Director of Strategy at Flevy. Prior to Flevy, Mark worked as an Associate at McKinsey & Co. and holds an MBA from the Booth School of Business at the University of Chicago.
It is licensed under CC BY 4.0. You're free to share and adapt with attribution. To cite this article, please use:
Source: "How to Calculate Terminal Value in Excel [Formula + Step-by-Step Guide]," Flevy Management Insights, Mark Bridges, 2026
Flevy is the world's largest marketplace of business templates & consulting frameworks.
Strategy & Transformation
Accelerate and transform the growth trajectory of your organization.
Strategy Development · KPI · Innovation Management · M&A (Mergers & Acquisitions) · Strategic Planning · Performance Management · Sales · Marketing
Digital Transformation
Harness AI, automation, and emerging technologies to build a future-proof organization.
Artificial Intelligence · Cyber Security · Digital Transformation · Customer Experience · SaaS · Information Technology · Agile · ITIL
The Consultant's Toolbox
A core competitive advantage of global consulting firms is access to an internal, proprietary knowledge base of consulting frameworks, templates, and past deliverables. FlevyPro provides boutique firms with that same—if not greater—access. Compete against the global consultancies, armed with the tier-1 frameworks they use.
- On-demand access to 1,000+ consulting frameworks
- Covers strategy, OpEx, digital, change, organization, HR, IT, and more
- New frameworks added weekly
