Terminal value is an essential financial tool used in valuations and can be calculated by using one of two methods: the perpetuity growth model and the exit multiple method. Calculating the terminal value with the perpetuity model in Excel can be done by simply inputting the formula into a cell and pressing enter.
What Is Terminal Value?
Video of the Day
The terminal value is a business's estimated present value after the specified forecasted period, the team at the Corporate Finance Institute writes. It is most often used in discounted cash flow analyses; however, it is common to use the terminal value in residual earnings calculations and when using the Gordon Growth Model, which is a method of calculating a stock's intrinsic value regardless of stock market conditions.
Video of the Day
Terminal value is used to predict values that would otherwise be difficult to determine when using the regular financial model forecast period. According to Corporate Finance Institute, terminal value can be calculated in one of two ways: the exit multiple method and the perpetuity growth model. Industry members most often use the exit multiple method. It can be employed to calculate terminal value when a business is sold for a multiple of a metric since the method relies on comparable trading multiples for similar businesses.
On the other hand, the perpetuity growth model is most often used by academics since it relies on mathematical theory. According to Corporate Finance Institute, the method assumes cash flow values will endlessly grow at a constant rate, known as free cash flow. However, it is essential to note that predicting an accurate perpetuity growth rate can be difficult.
Calculating Terminal Value With Perpetuity Growth Method
The perpetuity formula relies on two major assumptions: the discount rate and the perpetuity growth rate. The weighted average cost of capital (WACC) is used as the discount rate when projecting un-levered free cash flows; however, if predicting the levered sum of all future free cash flows, the discount rate should be the cost of equity.
Calculating the terminal value with the perpetuity growth method takes the final year of free cash flows and grows it using the assumed growth rate. Then, calculate the difference between the discount and perpetuity growth rates. Then, divide the former by the difference you've just calculated.
The perpetuity formula is as follows: Terminal value = [Final Year Free Cash Flow x (1 + Perpetuity Growth Rate)] / (Discount Rate - Perpetuity Growth Rate). If you would prefer to use a spreadsheet program, calculating the terminal value with the perpetuity formula in Excel can be done by inputting the values into the formula.
Calculating Terminal Value With Perpetuity Formula in Excel
To calculate the terminal value using the perpetuity model in Excel, create a table by inputting the values necessary for the equation into their own cell, then plug the corresponding cells into the equation.
This can be done by typing the following into a new cell in Excel: =Final Year FCF cell*(1+perpetuity Growth Rate cell)/(Discount Rate cell-perpetuity Growth Rate cell). You can input the cell into the equation by either manually typing the cell name, which is the column letter, followed by row number (e.g., B21) or by simply clicking the cell in your Excel spreadsheet, if you'd prefer.
Once the equation is inputted, press enter and Excel will do the calculation for you and reveal the terminal value. To edit the equation, simply click on the cell, and Excel will expose the equation so you can easily edit it any way you need.
Consider also: How to Create a Check Register in Excel