Understanding Terminal Value in Stocks
Terminal value is the value of all of a company's cash flows after the explicit forecast period in a discounted cash flow (DCF) model. There are two standard ways to calculate it in Excel:
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
- Perpetuity growth (Gordon) method: Terminal Value = FCF in the final forecast year × (1 + g) / (r − g), where g is the long-term growth rate and r is the discount rate (WACC). In Excel:
=B10*(1+B12)/(B11-B12)with final-year FCF in B10, r in B11, and g in B12. - Exit multiple method: Terminal Value = final-year EBITDA (or another metric) × a valuation multiple such as EV/EBITDA. In Excel:
=B13*B14.
Because the terminal value is received at the end of the forecast, discount it to today before adding it to enterprise value: =TV/(1+B11)^n, where n is the number of forecast years. The long-term growth rate must be below the discount rate, and is usually set at or below long-run economic growth.
Calculating Terminal Value in Excel
- Build your forecast of free cash flow for each year (typically 5 to 10 years).
- Enter your discount rate (WACC) and long-term growth rate in separate cells.
- Calculate terminal value with the perpetuity growth formula, the exit multiple formula, or both as a cross-check.
- Discount the terminal value back n years with
=TV/(1+r)^n. - Add the discounted terminal value to the present value of the forecast cash flows to get enterprise value.
Check how much of enterprise value comes from the terminal value. When it is the large majority, the valuation depends mostly on the growth and discount rate assumptions, so test several values.
How can MarketXLS Help?
MarketXLS is an Excel add-in that pulls the DCF inputs into cells with formulas, such as =FreeCashFlowPerShare("AAPL"), =TotalDebt("AAPL"), =Beta("AAPL"), and =TreasuryRate10y() for the risk-free rate. A two-stage DCF template with terminal value and a reverse DCF is described in discounted cash flow Excel, and more valuation workbooks, including a dividend growth (Gordon) model, are in MarketXLS templates.
Conclusion
Terminal value often makes up most of a DCF valuation, so the growth rate, discount rate, and exit multiple behind it deserve the most scrutiny. This article is educational, not investment advice.
