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:

1. 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.
2. 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.
3. 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
4. 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.
5. 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.

Related posts:

How to Calculate Savings in Excel Kg to lbs and lbs to kg converter How to calculate ROCE?