Home > Forum Home > Analyzing Corporate Financial Data > Calculating EVA Share

Calculating EVA

Excel Help for Calculating Eva in Analyzing Corporate Financial Data


Forum TopicPost Reply Login

Calculating Eva

Rate this:
(4/5 from 2 votes)
ConfusedCalculating 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,
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.
 Excel Business Forums Administrator
 Posted by on
 
Replies - Displaying 1 to 5 of 5Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
Very useful stuff.

Dale
[http://www.sisense.com/]
 Posted by on
Confused
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 on
Confused
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 on
Confused
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 structure
wp = PS/W = weight of Preferred Stock within the capital structure
wc = 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 on
Confused
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 

Excel templates and solutions matched for Calculating EVA:

Solutions: Investment and Business Valuation
Categories: Business Finance