Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
Fundamental analysis

Earnings Power Value Spreadsheet Using Marketxls

Written by admin
Thu Jun 01 2017
Earnings Power Value
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Earnings Power Value
 

While there are many methods to arrive at the intrinsic value of the stock for value investing, one approach is the Earnings Power Value, or EPV. A Columbia University Professor Bruce Greenwald developed this approach. In this article we will learn about the Earnings power Value method to value investing and we will also build an Earnings Power Value Spreadsheet using MarketXLS that you can use to check intrinsic value of any stock.

When it comes to stock markets, there are many approaches to investing and you should choose one that suits your beliefs and style. One such style of investing commonly referred to as value investing is a long-term investing approach where an investor aims to buy stocks that have a low market price compared to the fundamental value that the investor thinks the stock is worth. Since the investor thinks that the market price is lower than its fundamental value, te expects the price of the stock to rise and therefore buys the stock. This perceived difference between market price and intrinsic value of the stock is referred to as margin of safety. Generally, the discount should to be atleast 30-50% for investors interest in the stock.

Earnings Power Value

Earnings Power Value Formula

The formula for Earnings Power Value is based on the assumption that the current earnings of the firm are sustainable under a no growth scenario. EPV estimates the enterprise value by dividing an earnings measure with the weighted average cost of capital.

EPV = Adjusted Earnings / r

r is the cost of capital. We arrive at the adjusted earnings by adjusting the earnings for one time charges, depreciation, capital expenditures, taxation adjustments, and more adjustments based on the business cycle and other details. Finding the adjusted earnings can be difficult.

Let’s look at it in a bit more detail:

1. Adjusted Operating Earnings

We start with the adjusted operating earnings, i.e., EBIT. We take an average of adjusted operating earnings over the past 5-7 years. This is done to normalize the operating earnings in order to remove the effects of business cycles.

2. Deduct for Non-recurring Charges

We then deduct the long-term average of non-recurring charges from the normalized operating earnings.

3. Taxation Adjustment

We can either use an average tax rate of the company over the 5-7 year period or use the general corporate tax rate and apply it to the figure we get in step 2.

4. Depreciation Adjustment

We now add back the after-tax depreciation of the most recent year as it may not reflect the true economic cost of depreciation. Some analysts may deduct the entire depreciation while other may deduct only a percentage (such as 20%) of the depreciation value.

5. Subtract Maintenance Capital Expenditure

The next step is to deduct the maintenance capex which reflects the economic depreciation. We can calculate this by deducting growth capex from the capex in the cash flow statement. We calculate growth capex by taking an average of Gross Property Plant and Equipment (PPE)/ sales ratio over a long-term period and multiplying this by the current year’s increase in sales.

5. Adjusted After-tax EBIT

We now have the adjusted after-tax EBIT of the firm. We divide this by cost of capital to arrive at the earnings power value of the firm.

After deriving the EPV, we can compare it with the reproduction value. If the EPV is higher than the reproduction value, then the management is adding value and the firm is earning more that it’s cost of capital.

We can also compare EPV per share with the market price. If market price is below EPV per share, then the stock may be undervalued. For a fair comparison, we must first subtract any corporate debt from the EPV, and add back any cash in excess of operating requirements. This number is then divided by the number of shares to arrive at EPV per share.

Note that there are no hard rules for arriving at the EPV or reproduction value. What Greenwald provides is a method along with his recommendations of how to evaluate each item. Each analyst will come up with a different valuation based on their understanding of the financial statements and the business.

Earnings Power Value Spreadsheet (Example)

To demonstrate how we can calculate the Earnings Power Value, we have created a spreadsheet. This spreadsheet showcases the entire calculation using the Microsoft (MSFT) stock. It starts from the operating earnings and then make all the adjustments to calculate the EPV per share of the company. Users need to input some data in cells marked in Yellow. Furthermore, we can fetch some important data using the MarketXLS ‘hf’ functions. We have marked these cells in Green. As you can see in spreadsheet, the EPV per share of Microsoft stock is much lower than its market value. This indicates that the stock is overvalued. Note that EPV is an estimate and can differ based on an analyst’s interpretation of the adjusted earnings and cost of capital.

Buy the EPV Template

Earnings Power Value

These template is open and have no password. Please feel free to modify as per your needs and extend it for your needs

  • MarketXLS EPV Template Template

Instant Delivery

Secure Processing

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

116.74
 
USD
 
6.17
 
(5.58%)
83
Rank
Optionable: Yes
Market Cap: 8,757 M
Industry: Specialty Retail
52 week range    
63.45   
   142.78
Academy Sports and Outdoors Inc logo

Academy Sports and Outdoors Inc

47.63
 
USD
 
2.31
 
(5.10%)
81
Rank
Optionable: Yes
Market Cap: 3,614 M
Industry: Beer, Wine, and Liquor Stores
52 week range    
25.10   
   51.51
Texas Roadhouse Inc. logo

Texas Roadhouse Inc.

89.63
 
USD
 
3.72
 
(4.33%)
79
Rank
Optionable: Yes
Market Cap: 5,743 M
Industry: Restaurants
52 week range    
68.58   
   96.41
Destination XL Group Inc. logo

Destination XL Group Inc.

5.60
 
USD
 
0.10
 
(1.82%)
79
Rank
Optionable: No
Market Cap: 340 M
Industry: Apparel Stores
52 week range    
3.27   
   9.00
Annaly Capital Management Inc logo

Annaly Capital Management Inc

19.42
 
USD
 
0.02
 
(0.10%)
77
Rank
Optionable: Yes
Market Cap: 8,363 M
Industry: REIT - Diversified
52 week range    
5.45   
   22.78
BJ's Wholesale Club Holdings Inc. logo

BJ's Wholesale Club Holdings Inc.

76.06
 
USD
 
0.04
 
(0.05%)
77
Rank
Optionable: Yes
Market Cap: 10,268 M
Industry: Specialty Retail
52 week range    
51.45   
   79.69
Waste Connections Inc. logo

Waste Connections Inc.

139.06
 
USD
 
1.38
 
(1.00%)
76
Rank
Optionable: Yes
Market Cap: 35,409 M
Industry: Waste Management
52 week range    
113.50   
   148.20
STORE Capital Corporation logo

STORE Capital Corporation

31.90
 
USD
 
0.27
 
(0.85%)
75
Rank
Optionable: Yes
Market Cap: 8,941 M
Industry: REIT - Diversified
52 week range    
24.48   
   36.13
Sprouts Farmers Market Inc. logo

Sprouts Farmers Market Inc.

28.58
 
USD
 
1.51
 
(5.58%)
75
Rank
Optionable: Yes
Market Cap: 2,909 M
Industry: Grocery Stores
52 week range    
21.18   
   35.34
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

107.01
 
USD
 
6.17
 
(6.12%)
75
Rank
Optionable: Yes
Market Cap: 7,483 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   126.93

More Features