Enterprise Value (EV) is a measure of a company’s total value, including all debt and equity capital. It is often used in valuation and merger and acquisition (M&A) transactions. EV can be calculated using a variety of methods, but the most common is the discounted cash flow (DCF) method.
Key Facts
- Gather the necessary financial data: You will need the projected free cash flows for each year and the terminal value of the company.
- Set up your Excel spreadsheet: Create a column for the years and another column for the corresponding free cash flows.
- Calculate the NPV of the free cash flows: Use the NPV function in Excel to calculate the present value of the projected free cash flows. The formula would look like this: NPV(r, range of FCFs). ‘r’ represents the discount rate, and the range of FCFs should include the projected cash flows for each year.
- Calculate the terminal value: The terminal value represents the value of the company beyond the projection period. You can calculate it using the formula: TV = FCFn+1 / (r – g), where FCFn+1 is the projected free cash flow for the next year, ‘r’ is the discount rate, and ‘g’ is the perpetual growth rate.
- Calculate the enterprise value: Add the NPV of the free cash flows to the terminal value. The formula would look like this: EV = NPV + TV.
By following these steps, you can calculate the enterprise value of a company in Excel.
Steps to Calculate Enterprise Value in Excel
To calculate EV using the DCF method in Excel, follow these steps:
- Gather the necessary financial data.You will need the projected free cash flows for each year and the terminal value of the company.
- Set up your Excel spreadsheet.Create a column for the years and another column for the corresponding free cash flows.
- Calculate the NPV of the free cash flows.Use the NPV function in Excel to calculate the present value of the projected free cash flows. The formula would look like this: NPV(r, range of FCFs). ‘r’ represents the discount rate, and the range of FCFs should include the projected cash flows for each year.
- Calculate the terminal value.The terminal value represents the value of the company beyond the projection period. You can calculate it using the formula: TV = FCFn+1 / (r – g), where FCFn+1 is the projected free cash flow for the next year, ‘r’ is the discount rate, and ‘g’ is the perpetual growth rate.
- Calculate the enterprise value.Add the NPV of the free cash flows to the terminal value. The formula would look like this: EV = NPV + TV.
Example
To illustrate the steps involved in calculating EV in Excel, consider the following example:
- Company XYZ has projected free cash flows of $10 million, $12 million, $14 million, and $16 million for the next four years.
- The terminal value of the company is estimated to be $20 million.
- The discount rate is 10%.
Using the steps outlined above, we can calculate the EV of Company XYZ as follows:
- NPV of the free cash flows
NPV = NPV(10%, $10 million, $12 million, $14 million, $16 million)
= $40.8 million
- Terminal value
TV = $16 million / (10% – 2%)
= $200 million
- Enterprise value
EV = $40.8 million + $200 million
= $240.8 million
Therefore, the EV of Company XYZ is $240.8 million.
Conclusion
EV is a valuable metric for assessing the overall value of a company. It can be used for a variety of purposes, including valuation, M&A transactions, and capital budgeting. By following the steps outlined in this article, you can calculate EV in Excel using the DCF method.
Sources
- https://macabacus.com/valuation/dcf-enterprise-equity-values
- https://breakingintowallstreet.com/kb/equity-value-enterprise-value/how-to-calculate-enterprise-value/
- https://www.wallstreetprep.com/knowledge/enterprise-value/
FAQs
What is enterprise value (EV)?
Enterprise value (EV) is a measure of a company’s total value, including all debt and equity capital. It is often used in valuation and merger and acquisition (M&A) transactions.
What is the most common method for calculating EV?
The most common method for calculating EV is the discounted cash flow (DCF) method.
What are the steps involved in calculating EV using the DCF method in Excel?
The steps involved in calculating EV using the DCF method in Excel are:
* Gather the necessary financial data.
* Set up your Excel spreadsheet.
* Calculate the NPV of the free cash flows.
* Calculate the terminal value.
* Calculate the enterprise value.
What is the formula for calculating the NPV of the free cash flows?
The formula for calculating the NPV of the free cash flows is:
NPV = NPV(r, range of FCFs),
where ‘r’ represents the discount rate and ‘range of FCFs’ includes the projected cash flows for each year.
What is the formula for calculating the terminal value?
The formula for calculating the terminal value is:
TV = FCFn+1 / (r – g),
where FCFn+1 is the projected free cash flow for the next year, ‘r’ is the discount rate, and ‘g’ is the perpetual growth rate.
What is the formula for calculating the enterprise value?
The formula for calculating the enterprise value is:
EV = NPV + TV,
where NPV is the net present value of the free cash flows and TV is the terminal value.
What are some of the uses of EV?
EV is used for a variety of purposes, including valuation, M&A transactions, and capital budgeting.
Are there any limitations to using the DCF method to calculate EV?
Yes, there are some limitations to using the DCF method to calculate EV. These limitations include the need for accurate financial data, the difficulty in estimating the discount rate and terminal value, and the sensitivity of EV to changes in these inputs.