# Calculating EVA

## Excel Help for Calculating Eva in Analyzing Corporate Financial Data

### Calculating Eva

Rate this:
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= Cd(1-t)*D/(D+E) + Ce*E/(D+E)
Where:
Cd = Pre-tax Debt Rate
Ce = Cost of Equity
t = Corporate Tax Rate
D = Market Value Debt
E = Market Value of Equity

The Cost of Equity (Ce) calculation is:

Ce = R+M* β +L

Where:
R = Risk-free Rate
β = Equity Risk Beta,

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 FunctionFunction 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 FunctionFunction 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_PremEnd 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.
Posted by on

 Replies - Displaying 1 to 5 of 5 Order Replies By: Most Recent | Chronological | Highest Rated
 Rate this: (3/5 from 1 vote) Very useful stuff.Dale[http://www.sisense.com/] Posted by dale7690 on 27 Feb 2008
 Rate this: (3/5 from 1 vote) I know that this was posted ages ago, but it worth more than one commendation. Excelent stuffPeter Peter Posted by Oldie on 27 Sep 2008
 Rate this: (3/5 from 1 vote) HiThanks 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
 Rate this: (3/5 from 1 vote) we are studying in financial management that wacc can also be calculated as wd = LD/W = weight of Long term Debt within the capital structurewp = PS/W = weight of Preferred Stock within the capital structurewc = CE/W = weight of Common Equity within the capital structure New Skycars[url=http://www.newskycars.com/ ]Newskcyars [/url] [url=http://www.newskycars.com/] newskycars[/url] Posted by newskycars on 18 Jun 2010
Rate this:
(3/5 from 1 vote)
I am a newbie and lack of knowledge about excel. Anyone can help me ? :(

----------------------------------------------

 http://www.softwareoutsourcing.biz/services/open-source-development.html

Posted by on
 Displaying page 1 of 1