How to Calculate Economic Value of Equity in Excel
In this Excel tutorial, you learn how to calculate economic value of equity in Excel.
To calculate the economic value of equity (EVE) in Excel, you can follow these steps:
- Determine the discount rate: The discount rate is the rate of return that investors require for holding the company’s equity. It reflects the opportunity cost of investing in the company instead of investing in other opportunities. You can use the CAPM (Capital Asset Pricing Model) to determine the discount rate. The formula for the discount rate is:Discount rate = Risk-free rate + Beta * (Market return – Risk-free rate)where:
- Risk-free rate: the risk-free rate of return, such as the yield on a government bond.
- Beta: the company’s beta, which measures the volatility of the company’s stock returns relative to the market.
- Market return: the expected return of the market, such as the return of the S&P 500.
- Forecast the company’s cash flows: Estimate the cash flows that the company will generate in the future. This can be done by projecting the company’s financial statements (income statement, balance sheet, and cash flow statement) for a certain period, such as five years.
- Calculate the present value of the cash flows: Calculate the present value of the cash flows by discounting them back to the present using the discount rate. You can use Excel’s PV (present value) function for this. For example, if the discount rate is 10%, the cash flow in year 1 is $100, and the cash flow in year 2 is $200, the present value of the cash flows in year 1 and year 2 would be:PV of year 1 cash flow = $100 / (1 + 10%)^1 = $90.91 PV of year 2 cash flow = $200 / (1 + 10%)^2 = $165.29
- Sum the present value of the cash flows: Add up the present value of the cash flows to get the net present value (NPV) of the company. For example, if the present value of the cash flows in year 1 and year 2 are $90.91 and $165.29, respectively, the NPV would be $90.91 + $165.29 = $256.20.
- Subtract the market value of debt: Subtract the market value of the company’s debt from the NPV to get the economic value of equity. The market value of debt can be obtained by multiplying the outstanding debt by its market price. For example, if the market value of the company’s debt is $100, the economic value of equity would be $256.20 – $100 = $156.20.
Here’s an example of a formula to calculate the present value of a cash flow in Excel:
=PV(rate, nper, pmt, [fv], [type])
where:
- rate: the discount rate per period.
- nper: the number of periods.
- pmt: the cash flow per period.
- fv: (optional) the future value of the cash flow, usually 0.
- type: (optional) the timing of the cash flow, 0 for end of period, 1 for beginning of period.
You can use this formula to calculate the present value of each cash flow and then sum them up to get the NPV.
Table of Contents
Practical Example: Building an EVE Model in Excel
To better understand how to calculate economic value of equity in practice, let’s walk through a complete example using Excel. This section will demonstrate how to set up a workbook to calculate EVE for a hypothetical company.
Assumptions for the Example:
Suppose you are analyzing XYZ Corporation with the following assumptions:
Risk-free rate: 3% (yield on 10-year government bond)
Company beta: 1.2 (higher volatility than the market)
Market return: 10% (expected return of S&P 500)
Discount rate (cost of equity): 3% + 1.2 × (10% – 3%) = 11.4%
Projected cash flows for 5 years: Year 1: $50,000; Year 2: $60,000; Year 3: $70,000; Year 4: $75,000; Year 5: $80,000
Terminal value (Year 5 cash flow × 2): $160,000
Current market value of debt: $200,000
Step-by-Step Excel Calculation:
Create a data table with columns for Year, Cash Flow, and Present Value
Use the PV function to calculate the present value of each year’s cash flow: =PV(11.4%, year, -cash_flow)
Sum all present values of projected cash flows
Add the present value of the terminal value
Subtract the market value of debt to get the economic value of equity
The result would be: Total NPV – Market Value of Debt = Economic Value of Equity
This practical example demonstrates that with proper assumptions and Excel formulas, you can accurately calculate the economic value of a company’s equity, which is essential for valuation, investment decisions, and financial analysis.
Leave a Reply