Excel Help for Calculating Eva

Calculating EVA or "Economic Value Added" is a worthwhile exercise when analyzing corporate financial data as it measures performance while taking into account capital investment required for the economic return.

Calculating EVA in Excel can be facilitated by defining some functions which can then be referenced when making the analysis in the spreadsheet itself.

The EVA calculation in its simplest form is:

EVA = Economic Profit - (WACC * Invested Capital)

Of course, the calculation of the Economic Profit and Invested Capital can be complicated in itself. It is not the purpose of this post to detail this but essentially Net Operating Profit After Tax (NOPAT) and Capital can be adjusted such as capitalizing expenses that are investments in nature and amortizing them over the life for which the benefits are expected to realized. Such expenses may include restructuring costs. (Sophisticated Excel templates exist to calculate these adjustments automatically, such as the Investment and Business Valuation template.)

Nevertheless, there is no predefined Excel function to make the basic calculation. Furthermore calculations are needed for the Weighted Average Cost of Capital (WACC) and also for the Cost of Equity required by the WACC equation.

The WACC calculation is:

WACC= C_d(1-t)*D/(D+E) + C_e*E/(D+E)
Where:
C_d = Pre-tax Debt Rate
C_e = Cost of Equity
t = Corporate Tax Rate
D = Market Value Debt
E = Market Value of Equity

The Cost of Equity (C_e)calculation is:

C_e = R+M* β +L

Where:
R = Risk-free Rate
β = Equity Risk Beta,
M = Market Risk Premium
L = Liquidity Premium

To facilitate the calculation of these inputs and calculating EVA the following user defined functions can be copied into a module in the VBA editor for Excel.

VBA Code:

Function EVA(Econ_Profit As Single, WACC As Single, Invest_Capital As Single)
    EVA = Econ_Profit - (WACC * Invest_Capital)
End Function

Function WACC(EQUITY_COST As Single, Equity_Capital As Single, Debt_Cost As Single, Debt_Capital As Single, Tax_Rate As Single)
    WACC = ((Debt_Capital / (Debt_Capital + Equity_Capital)) * Debt_Cost * (1 - Tax_Rate)) + ((Equity_Capital / (Debt_Capital + Equity_Capital)) * EQUITY_COST)
End Function

Function EQUITY_COST(Riskfree_Rate As Single, Mkt_Risk_Prem As Single, Equity_Beta As Single, Liquidity_Prem As Single)
    EQUITY_COST = Riskfree_Rate + (Mkt_Risk_Prem * Equity_Beta) + Liquidity_Prem
End Function

These functions can then be accessed from the menu by choosing Insert >> Function and choosing them from User Defined category. Cell links with the required values can be used in these functions to help in calculating EVA when analyzing corporate finance data.

Calculating EVA

Excel Help for Calculating Eva in Analyzing Corporate Financial Data

Calculating Eva

Excel templates and solutions matched for Calculating EVA:

	Thanks Rate this: (3/5 from 1 vote) Very useful stuff. Dale [http://www.sisense.com/]
	Posted by dale7690 on 27 Feb 2008

	Calculating EVA Rate this: (3/5 from 1 vote) I know that this was posted ages ago, but it worth more than one commendation. Excelent stuff Peter
	Peter
	Posted by Oldie on 27 Sep 2008

	THANKS Rate this: (3/5 from 1 vote) Hi Thanks for ur information and u gave very clear stuff with formulas. I think its very useful for all people.
	[url=http://www.e-tradecounter.co.uk/p-157-monterey-electric-panel-heater.aspx]electric panel heaters[/url]
	Posted by billysiddle on 14 Nov 2009

	Wacc calculation from Newskycars Rate this: (3/5 from 1 vote) we are studying in financial management that wacc can also be calculated as w_d = LD/W = weight of Long term Debt within the capital structure w_p = PS/W = weight of Preferred Stock within the capital structure w_c = CE/W = weight of Common Equity within the capital structure <a href="http://www.newskycars.com/ "> New Skycars</a> [url=http://www.newskycars.com/ ]Newskcyars [/url]
	[url=http://www.newskycars.com/] newskycars[/url]
	Posted by newskycars on 18 Jun 2010