Earnings Power Value Model in Python
Introduced back in the days by investment legends Benjamin Graham and David Dodd, the Earnings Power Value (EPV) is still a widely used valuation method used by value investors.
The goal of an EPV calculation is to estimate the portion of earnings that is ready to be distributed to the shareholders at a sustainable level.
This means, the investor must take into account a couple of factors and principles to arrive at sustainable distributed earnings. I won’t dive into too many details, but here are some steps to arrive at EPV:
- First, a sustainable EBIT margin must be calculated.
- Next, the growth expenses must be identified. We assume that the business continues at its current level of business and thus growth expenses can be added to distributable earnings.
- Similarly, we need to identify maintenance capital expenditures and depreciation.
- Next up, we need the effective tax rate.
- Now, using the found sustainable EBIT and subtracting: maintenance capex and tax expenses and adding back depreciation (since it’s a non-cash expense) and growth expenses, we get to a value for sustainable NOPAT (Net Operating Profit After Taxes).
- At last, we can divide this value for sustainable NOPAT by the required rate of return to end up at EPV.
Now, lets get to python and setup a model to estimate the EPV of any stock available on Yahoo Finance.
The Libraries and Importing Finance Data
First step first, we need to import some libraries.
import datetime as dt
import numpy as np
import matplotlib.pyplot as plt
from yahoofinancials import YahooFinancials
import time
import pandas as pd
from pandas_datareader import data as pdr
import yfinance as yf
yf.pdr_override()Now, we can start importing financial data from Yahoo Finance. For this, we need to connect to Yahoo Finance and submit a stock symbol. For this example I will be analyzing the oil\gas giant Shell.
StockList = ['Shell.AS']
balanceSheet = {}
incomeStatement = {}
cashStatement = {}
for stock in StockList:
print(stock)
start=time.time()
yahoo_financials = YahooFinancials(stock)
balance_sheet_data = yahoo_financials.get_financial_stmts('annual', 'balance')
income_statement_data = yahoo_financials.get_financial_stmts('annual', 'income')
cash_statement_data = yahoo_financials.get_financial_stmts('annual', 'cash')
balanceSheet[stock] = balance_sheet_data['balanceSheetHistory'][stock]
incomeStatement[stock] = income_statement_data['incomeStatementHistory'][stock]
cashStatement = cash_statement_data['cashflowStatementHistory'][stock]
end=time.time()
print('time taken {:.2f} s'.format(end-start))
break
Note that the connection to Yahoo Finance can take about 20s to retrieve the data. Once it is finished, the output should be a combination of the ticker symbol and the time taken to retrieve the data.
Now, the financial statement data can be called using:
balanceSheet
incomeStatement
cashStatementI recommend plotting these separately since you might want to check the structure of these dictionaries. You can, of course, also just check the items on Yahoo Finance itself.
To retrieve particular data from the dictionaries we use the dictionary keys and search the values in the appropriate financial statements. Subsequently we put these values in data frames to use them later on.
for(keyB, valB), (keyI, valI), (keyC, valC) in zip(balanceSheet.items(), incomeStatement.items(),cashStatement.items()):
revenue = [v['totalRevenue'] for year in valI for k, v in year.items()]
operatingIncome = [v['operatingIncome'] for year in valI for k, v in year.items()]
unusualExpenses = [v['totalUnusualItems'] for year in valI for k, v in year.items()]
SGAExpenses = [v['sellingGeneralAndAdministration'] for year in valI for k, v in year.items()]
RDExpenses = [v['researchAndDevelopment'] for year in valI for k, v in year.items()]
netIncome = [v['netIncome'] for year in valI for k, v in year.items()]
taxExpense = [v['taxProvision'] for year in valI for k, v in year.items()]
pretaxIncome = [v['pretaxIncome'] for year in valI for k, v in year.items()]
interestExpense = [v['interestExpense'] for year in valI for k, v in year.items()]
PPE = [v['grossPPE'] for year in valB for k, v in year.items()]
netPPE = [v['netPPE'] for year in valB for k, v in year.items()]
equity = [v['stockholdersEquity'] for year in valB for k, v in year.items()]
totalDebt = [v['totalDebt'] for year in valB for k, v in year.items()]
netDebt = [v['netDebt'] for year in valB for k, v in year.items()]
capEx = [v['capitalExpenditure'] for year in valC for k, v in year.items()]
depreciation = [v['depreciation'] for year in valC for k, v in year.items()]
yearsI = [k for year in valI for k, v in year.items()]
yearsB = [k for year in valB for k, v in year.items()]
df_rev = pd.DataFrame(data=revenue, index=yearsI)
df_rev.columns = ['Revenue']
df_OI = pd.DataFrame(data=operatingIncome, index=yearsI)
df_OI.columns = ['OperatingIncome']
df_unusualExpenses = pd.DataFrame(data=unusualExpenses, index=yearsI)
df_unusualExpenses.columns = ['Unusual Expenses']
df_SGA = pd.DataFrame(data=SGAExpenses, index=yearsI)
df_SGA.columns = ['SG&A Expenses']
df_RD = pd.DataFrame(data=RDExpenses, index=yearsI)
df_RD.columns = ['R&D Expenses']
df_NI = pd.DataFrame(data=netIncome, index=yearsI)
df_NI.columns = ['NetIncome']
df_taxExpense = pd.DataFrame(data=taxExpense, index=yearsI)
df_taxExpense.columns = ['Tax Expense']
df_pretax = pd.DataFrame(data=pretaxIncome, index=yearsI)
df_pretax.columns = ['Pre-Tax Income']
df_interestExpense = pd.DataFrame(data=interestExpense, index=yearsI)
df_interestExpense.columns = ['Interest Expense']
df_PPE = pd.DataFrame(data=PPE, index=yearsB)
df_PPE.columns = ['Gross PPE']
df_netPPE = pd.DataFrame(data=netPPE, index=yearsB)
df_netPPE.columns = ['Net PPE']
df_equity = pd.DataFrame(data=equity, index=yearsB)
df_equity.columns = ['Equity']
df_totalDebt = pd.DataFrame(data=totalDebt, index=yearsB)
df_totalDebt.columns = ['Total Debt']
df_netDebt = pd.DataFrame(data=netDebt, index=yearsB)
df_netDebt.columns = ['Net Debt']
df_capEx = pd.DataFrame(data=capEx, index=yearsI)
df_capEx.columns = ['Capital Expenditure']
df_depreciation = pd.DataFrame(data=depreciation, index=yearsI)
df_depreciation.columns = ['Depreciation']With the data retrieved we can start modelling some data frames that will be helpful in calculation the company’s EPV.
Getting the Required Company Data
As I mentioned, we need an estimation of a sustainable EBIT margin and growth expenses. Lets start here.
df_fin = pd.DataFrame(data=df_rev, index=yearsI)
df_fin = df_fin.drop(index='2023-03-31') #I like to have YoY values so I drop the latest quarter data.
df_fin['Revenue Growth %'] = df_fin.Revenue.pct_change().mul(100)
df_fin = pd.concat([df_fin, df_NI], axis=1, join='inner')
df_fin['Net Income Growth %'] = df_fin.NetIncome.pct_change().mul(100)
df_fin = pd.concat([df_fin, df_OI], axis=1, join='inner')
df_fin['OI Growth'] = df_fin.OperatingIncome.diff()
df_fin['OI Growth %'] = df_fin.OperatingIncome.pct_change().mul(100)
df_fin['Operating Income Margin %'] = (df_fin['OperatingIncome'] / df_fin['Revenue']) * 100
df_fin['Median OIM %'] = ' '
df_fin['Median OIM %']['2022-12-31'] = df_fin['Operating Income Margin %'].median()
df_fin = pd.concat([df_fin, df_unusualExpenses, df_SGA], axis=1, join='inner')
df_fin['SG&A-to-Sales %'] = (df_fin['SG&A Expenses'] / df_fin['Revenue']) * 100
df_fin['Mean SG&A Margin %'] = ' '
df_fin['Mean SG&A Margin %']['2022-12-31'] = df_fin['SG&A-to-Sales %'].mean()
df_fin = pd.concat([df_fin, df_RD], axis=1, join='inner')
df_fin['R&D-to-Sales %'] = (df_fin['R&D Expenses'] / df_fin['Revenue']) * 100
df_fin['Mean R&D Margin %'] = ' '
df_fin['Mean R&D Margin %']['2022-12-31'] = df_fin['R&D-to-Sales %'].mean()
df_fin['Total Growth Items'] = df_fin['SG&A Expenses'] + df_fin['R&D Expenses']
df_fin['Growth Items % of Sales'] = (df_fin['Total Growth Items'] / df_fin['Revenue']) * 100
df_fin['Historic Median Growth Impact on Sales %'] = ' '
df_fin['Historic Median Growth Impact on Sales %']['2022-12-31'] = df_fin['Growth Items % of Sales'].mean()
df_fin['Growth Items % of OI'] = (df_fin['Total Growth Items'] / df_fin['OperatingIncome']) * 100
df_fin['Historic Median Growth impact on OI %'] = ' '
df_fin['Historic Median Growth impact on OI %']['2022-12-31'] = df_fin['Growth Items % of OI'].mean()
df_fin = pd.concat([df_fin, df_pretax], axis=1, join='inner')
df_fin = pd.concat([df_fin, df_taxExpense], axis=1, join='inner')
df_fin['Real Tax Rate %'] = (df_fin['Tax Expense'] / df_fin['Pre-Tax Income']) * 100
df_fin['Effective Tax Rate %'] = ' '
df_fin['Effective Tax Rate %']['2022-12-31'] = df_fin['Real Tax Rate %'].mean()
df_fin.TThis gives me a data frame that looks like this:
The median operating income margin is our first estimation of sustainable EBIT! And the mean growth items-to-sales ratio is our first estimation for growth expenses. We’re getting somewhere!
Note that when you use the Yahoo Finance index years, the row order might not me chronologically. To adjust the row order you can use a code like this:
df_fin = df_fin.iloc[::-1]
target_row_fin = 1
idx_fin = [target_row_fin] + [i for i in range(len(df_fin)) if i != target_row_fin]
df_fin = df_fin.iloc[idx_fin]Here, we first reverse the row order and subsequently target a row which is replaced to our ‘0 row’.
Next up: maintenance capital expenditures!
Finding Maintenance Capital Expenditures
The reason I also plotted the revenue and net income growth is so we may spot low growth years. In years of low growth we can check the capital expenditures and when these values are alike, we can use this value for maintenance capex. Unfortunately it’s not that easy for Shell, we must calculate this value ourselves.
A widely used method for finding the maintenance capex is:
- Find a median value for Net PPE (found on the balance sheet)-to-Sales.
- Next we can calculate the growth capex by multiplying the median Net PPE-to-Sales with the growth in Sales.
- Now, subtract the value for growth capex with total capex to find maintenance capex.
Using Python to find maintenance capex:
df_capex = pd.DataFrame(data=df_rev, index=yearsI)
df_capex = df_capex.drop(index='2023-03-31')
df_capex['Revenue Growth'] = df_capex.Revenue.diff()
df_capex = pd.concat([df_capex, df_capEx], axis=1, join='inner')
df_capex = pd.concat([df_capex, df_depreciation], axis=1, join='inner')
df_capex = pd.concat([df_capex, df_PPE], axis=1, join='inner')
df_capex = pd.concat([df_capex, df_netPPE], axis=1, join='inner')
#Make sure to check the median value well, is it influenced by large fluctuations in data?
df_capex['Gross PPE-to-Sales %'] = (df_capex['Gross PPE'] / df_capex['Revenue']) * 100
df_capex['Median PPE-to-Sales %'] = ' '
df_capex['Median PPE-to-Sales %']['2022-12-31'] = df_capex['Gross PPE-to-Sales %'].median()
#For Growth CapEx make sure to analyze revenue growth between two valid years (big drops and rises)
df_capex['Growth CapEx'] = ' '
df_capex['Growth CapEx']['2022-12-31'] = (((df_capex['Revenue']['2022-12-31'] - df_capex['Revenue']['2019-12-31'])/4) * (df_capex['Median PPE-to-Sales %']['2022-12-31'] / 100))*-1
df_capex['Maintenance CapEx'] = ' '
df_capex['Maintenance CapEx']['2022-12-31'] = df_capex['Capital Expenditure']['2022-12-31'] - df_capex['Growth CapEx']['2022-12-31']
df_capex.TThis gives the following data frame:
I had to adjust the data a bit since the growth data in 2022 is extraordinary high due to COVID-19 pullbacks in revenue. Hence I used the growth value between 2019 and 2022 and divided this by the 4-year term. Also, I filtered out the high COVID-19 year net PPE-to-sales values.
Hence, make sure to analyze the data and adjust it where you feel like it must be adjusted! This maintenance capex value will be used in the final calculation.
Comparing Industry Data
To get a better estimation for the sustainable EBIT and growth expenses we will analyze the market averages. To do this, we will connect to: https://pages.stern.nyu.edu/~adamodar/New_Home_Page/home.htm
from compdata import comp_data
market = comp_data.Market()
df = market.get_macro()
comp_data.industry_name_listThis code gives a list of all sectors. Thus, we have to select the sector in which we are interested. In this case: Oil/Gas (Integrated).
industry = comp_data.Industry('Oil/Gas (Integrated)')
growth = industry.get_historical_growth()
growthRetrieving the historic growth data gives some basic information and is not necessary for this calculation, it is still interesting to see.
We want to check the industry margins, todo this, we should go to: https://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/margin.html
Here, we can download the .xls file and import the data to Python.
margins = pd.read_excel('margin21.xls', 'Industry Averages', header=8, index_col=0)
data_margins = pd.DataFrame(data=margins)
industry_margins = margins.iloc[53]
df_margins = pd.DataFrame(industry_margins)
df_marginsIn the .iloc function you have to select the row value for the industry you want to analyze. You will get a data frame like this:
In this data frame we are interested in: the gross margin, R&D/Sales and SG&A/Sales. These objects can easily be called using the .iloc function.
OI_margin = df_margins.iloc[4][0]
RD_margin = df_margins.iloc[-4][0]
SGA_margin = df_margins.iloc[-3][0]Now, we can construct a new data frame and calculate the sustainable margins by averaging the company margins with the industry margins.
a = { 'Industry OIM' : [0.00],
'Industry SG&A Margin' : [0.00],
'Industry R&D Margin' : [0.00],
'Company Median OIM' : [0.00],
'Company Mean SG&A Margin' : [0.00],
'Company Mean R&D Margin' : [0.00],
'Averaged OIM' : [0.00],
'Averaged SG&A Margin' : [0.00],
'Averaged R&D Margin' : [0.00],
'Sustainable OIM' : [0.00],
'Sustainable Growth Expense Margin' : [0.00],
'Effective Tax Rate' : [0.00]
}
df_Margins = pd.DataFrame(data=a, index=StockList)
df_Margins['Industry OIM'] = OI_margin
df_Margins['Industry SG&A Margin'] = SGA_margin
df_Margins['Industry R&D Margin'] = RD_margin
df_Margins['Company Median OIM'] = (df_fin['Median OIM %']['2022-12-31']) / 100
df_Margins['Company Mean SG&A Margin'] = (df_fin['Mean SG&A Margin %']['2022-12-31']) / 100
df_Margins['Company Mean R&D Margin'] = (df_fin['Mean R&D Margin %']['2022-12-31']) / 100
df_Margins['Averaged OIM'] = np.mean([df_Margins['Industry OIM'] , df_Margins['Company Median OIM']])
df_Margins['Averaged SG&A Margin'] = np.mean([df_Margins['Industry SG&A Margin'] , df_Margins['Company Mean SG&A Margin']])
df_Margins['Averaged R&D Margin'] = np.mean([df_Margins['Industry R&D Margin'] , df_Margins['Company Mean R&D Margin']])
df_Margins['Sustainable OIM'] = df_Margins['Averaged OIM']
df_Margins['Sustainable Growth Expense Margin'] = df_Margins['Averaged SG&A Margin'] + df_Margins['Averaged R&D Margin']
df_Margins['Effective Tax Rate'] = (df_fin['Effective Tax Rate %']['2022-12-31']) / 100
print('Summary of Margins:')
df_Margins.TThis gives a data frame summarizing all the margins, looking like this:
Very nice indeed! We now have averaged our company margins with industry data, getting a better picture of what margins are realistic.
Sustainable NOPAT!
Now that we have the value for: sustainable EBIT, growth expense (using the margin found), tax expenses (using rate) and maintenance capex, we can calculate sustainable NOPAT! Note that the depreciation value can be found on the cashflow statement and is simply added back since it’s a non-cash expense.
b = { 'Sustainable EBIT' : [0.00],
'Growth Adjustment' : [0.00],
'Maintenance CapEx Adjustment' : [0.00],
'Depreciation' : [0.00],
'Effective Tax Rate' : [0.00],
'Sustainable NOPAT' : [0.00]
}
df_SusNOPAT = pd.DataFrame(data=b, index=StockList)
df_SusNOPAT['Sustainable EBIT'] = df_fin['Revenue']['2022-12-31'] * df_Margins['Sustainable OIM']
df_SusNOPAT['Growth Adjustment'] = df_fin['Total Growth Items']['2022-12-31'] - ( df_fin['OI Growth']['2022-12-31'] * (df_fin['Historic Median Growth impact on OI %']['2022-12-31']/100) )
df_SusNOPAT['Maintenance CapEx Adjustment'] = df_capex['Maintenance CapEx']['2022-12-31']
df_SusNOPAT['Depreciation'] = df_capex['Depreciation']['2022-12-31']
df_SusNOPAT['Effective Tax Rate'] = df_Margins['Effective Tax Rate']
df_SusNOPAT['Sustainable NOPAT'] = (df_SusNOPAT['Sustainable EBIT'] + df_SusNOPAT['Growth Adjustment'] + df_SusNOPAT['Depreciation'] + df_SusNOPAT['Maintenance CapEx Adjustment']) * (1 - df_SusNOPAT['Effective Tax Rate'])
df_SusNOPAT.TGiving the following data frame:
To make sure everyone’s on the same page, quickly repeating the calculation of sustainable NOPAT:
Sustainable NOPAT = (Sustainable EBIT + Growth Adjustment - Maintenance Capex + Depreciation) x (1 - Effective Tax Rate)
Perfect, now what remains is determining the cost of capital. For this I will use the Weighted Average Cost of Capital (WACC) model in combination with the Capital Asset Pricing Model (CAPM).
Cost of Capital
The general formula for WACC is:
WACC = (Weight of Equity x Cost of Equity) + (Weight of Debt x Cost of Debt) x (1 - Effective Tax Rate)
The weight of equity and debt are easily retrieved from the balance sheet data. The cost of these financing methods are a bit harder to find. Lets start with the cost of equity. For this I will use the CAPM.
The general formula for CAPM:
CAPM = Risk Free Rate + β x (Expected Market Return - Risk Free Rate)
We assume the following:
- The Risk Free Rate equals the 10-yr U.S. Treasury bond yield.
- Expected Market Return equals the average historic market returns and are estimated at around 12.5%.
We get the following:
Treasury = ['^TNX']
name = ['Risk Free Rate', 'Expected Market Return']
yahoo_data = YahooFinancials(Treasury)
df_ReturnRates = pd.DataFrame(data=yahoo_data.get_current_price(), index=name)
df_ReturnRates['^TNX']['Expected Market Return'] = ' '
expectedMarketReturn = 0.125
df_ReturnRates['^GSPC'] = ' '
df_ReturnRates['^GSPC']['Expected Market Return'] = expectedMarketReturn * 100
print('The current risk free rate is based on the 10-yr US Treasury Bond rate.')
print('The current expected market return is based on the mean return of the S&P500 index.')
df_ReturnRates
For the CAPM, we find:
c = { 'Risk Free Rate' : [0.00],
'Expected Market Return' : [0.00],
'Company Beta Value' : [0.00],
'Cost of Equity' : [0.00]
}
df_CAPM = pd.DataFrame(data=c, index=StockList)
df_CAPM['Risk Free Rate'] = df_ReturnRates['^TNX']['Risk Free Rate']
df_CAPM['Expected Market Return'] = df_ReturnRates['^GSPC']['Expected Market Return']
df_CAPM['Company Beta Value'] = beta
df_CAPM['Cost of Equity'] = df_CAPM['Risk Free Rate'] + ( beta * (df_CAPM['Expected Market Return'] - df_CAPM['Risk Free Rate']) )
print('Capital Asset Pricing Model (CAPM):')
df_CAPM.T
Hence, the cost of equity for Shell is estimated at approximately 9.25%.
Luckily, the cost of debt is easier to find. We assume that the interest payments show the effective cost of debt. Hence we find the WACC:
df_WACC = pd.DataFrame(data=df_equity, index=yearsB)
df_WACC = pd.concat([df_WACC, df_totalDebt], axis=1, join='inner')
df_WACC = pd.concat([df_WACC, df_interestExpense], axis=1, join='inner')
df_WACC['Total Financing'] = df_WACC['Equity'] + df_WACC['Total Debt']
df_WACC['Equity Weight'] = df_WACC['Equity'] / df_WACC['Total Financing']
df_WACC['Debt Weight'] = df_WACC['Total Debt'] / df_WACC['Total Financing']
df_WACC['Cost of Equity'] = (df_CAPM['Cost of Equity']['SHELL.AS'] / 100)
df_WACC['Cost of Debt'] = df_WACC['Interest Expense'] / df_WACC['Total Debt']
df_WACC['WACC'] = (df_WACC['Equity Weight'] * df_WACC['Cost of Equity']) + ((df_WACC['Debt Weight'] * df_WACC['Cost of Debt']) * (1-df_SusNOPAT['Effective Tax Rate']['SHELL.AS']))
print('The Weighted Average Cost of Capital (WACC):')
df_WACC.T
Here we can see how the weighted average cost of capital changed over the years which could give some valuable insights to investors.
Earnings Power Value
Now that we have values for: sustainable NOPAT and Cost of Capital, we can estimate the company’s Earnings Power Value.
Of course, if we want an estimation of a fair share value, we must incorporate debt. Hence to get to a fair value per share, we subtract the net debt, found on the balance sheet, from EPV.
d = { 'Sustainable NOPAT' : [0.00],
'WACC' : [0.00],
'Earnings Power Value' : [0.00],
'Shares Out' : [0.00],
'Earnings Power Value per Share' : [0.00],
'Net Debt' : [0.00],
'Fair Company Value' : [0.00],
'Fair Share Value' : [0.00]
}
df_EPV = pd.DataFrame(data=d, index=StockList)
df_EPV['Sustainable NOPAT'] = df_SusNOPAT['Sustainable NOPAT']
df_EPV['WACC'] = df_WACC['WACC']['2022-12-31']
df_EPV['Earnings Power Value'] = df_EPV['Sustainable NOPAT'] / df_EPV['WACC']
df_EPV['Shares Out'] = yahoo_financials.get_num_shares_outstanding(price_type='current')
df_EPV['Earnings Power Value per Share'] = df_EPV['Earnings Power Value'] / df_EPV['Shares Out']
df_EPV['Net Debt'] = df_netDebt['Net Debt'].iloc[3]
df_EPV['Fair Company Value'] = df_EPV['Earnings Power Value'] - df_EPV['Net Debt']
df_EPV['Fair Share Value'] = df_EPV['Fair Company Value'] / df_EPV['Shares Out']
print('Summary of EPV analysis:')
df_EPV.T.style\
.format(precision=2)This gives us a summary of the EPV calculation:
Here you have it. An estimation of EPV! This can be used by investors to assess the fair value of a company by calculating its distributable earnings and dividing this by the cost of capital.
Since the cost of capital has a big impact on the calculation of EPV, it might be desired to see what happens when the WACC varies up or down by a range of 1%.
We can construct a new data frame for this.
e = { 'WACC' : [0.00, 0.00, 0.00, 0.00, 0.00],
'Sustainable NOPAT' : [0.00, 0.00, 0.00, 0.00, 0.00],
'Earnings Power Value' : [0.00, 0.00, 0.00, 0.00, 0.00],
'Net Debt' : [0.00, 0.00, 0.00, 0.00, 0.00],
'Shares Outstanding' : [0.00, 0.00, 0.00, 0.00, 0.00],
'Fair Share Value' : [0.00, 0.00, 0.00, 0.00, 0.00]
}
index = ['WACC-1%', 'WACC-0.5%', 'WACC', 'WACC+0.5%', 'WACC+1%']
df_sens = pd.DataFrame(data=e, index=index)
df_sens['WACC']['WACC-1%'] = df_EPV['WACC'] - 0.01
df_sens['WACC']['WACC-0.5%'] = df_EPV['WACC'] - 0.005
df_sens['WACC']['WACC'] = df_EPV['WACC']
df_sens['WACC']['WACC+0.5%'] = df_EPV['WACC'] + 0.005
df_sens['WACC']['WACC+1%'] = df_EPV['WACC'] + 0.01
df_sens['Sustainable NOPAT'] = df_EPV['Sustainable NOPAT']['SHELL.AS']
df_sens['Earnings Power Value']['WACC-1%'] = df_sens['Sustainable NOPAT']['WACC-1%'] / df_sens['WACC']['WACC-1%']
df_sens['Earnings Power Value']['WACC-0.5%'] = df_sens['Sustainable NOPAT']['WACC-0.5%'] / df_sens['WACC']['WACC-0.5%']
df_sens['Earnings Power Value']['WACC'] = df_sens['Sustainable NOPAT']['WACC'] / df_sens['WACC']['WACC']
df_sens['Earnings Power Value']['WACC+0.5%'] = df_sens['Sustainable NOPAT']['WACC+0.5%'] / df_sens['WACC']['WACC+0.5%']
df_sens['Earnings Power Value']['WACC+1%'] = df_sens['Sustainable NOPAT']['WACC+1%'] / df_sens['WACC']['WACC+1%']
df_sens['Net Debt'] = df_netDebt['Net Debt'].iloc[3]
df_sens['Shares Outstanding'] = df_EPV['Shares Out']['SHELL.AS']
df_sens['Fair Share Value']['WACC-1%'] = (df_sens['Earnings Power Value']['WACC-1%'] - df_netDebt['Net Debt'].iloc[3]) / df_EPV['Shares Out']['SHELL.AS']
df_sens['Fair Share Value']['WACC-0.5%'] = (df_sens['Earnings Power Value']['WACC-0.5%'] - df_netDebt['Net Debt'].iloc[3]) / df_EPV['Shares Out']['SHELL.AS']
df_sens['Fair Share Value']['WACC'] = (df_sens['Earnings Power Value']['WACC'] - df_netDebt['Net Debt'].iloc[3]) / df_EPV['Shares Out']['SHELL.AS']
df_sens['Fair Share Value']['WACC+0.5%'] = (df_sens['Earnings Power Value']['WACC+0.5%'] - df_netDebt['Net Debt'].iloc[3]) / df_EPV['Shares Out']['SHELL.AS']
df_sens['Fair Share Value']['WACC+1%'] = (df_sens['Earnings Power Value']['WACC+1%'] - df_netDebt['Net Debt'].iloc[3]) / df_EPV['Shares Out']['SHELL.AS']
df_sens.T.style\
.format(precision=2)This gives us a range of WACC+1% and WACC-1% and the corresponding EPV.
Perfect, now we have a simple sensitivity analysis, analyzing the EPV for varying cost of capital. Now, we can see what impact the cost of capital will have on the fair share value. This is useful information in an environment where the cost of debt is rising rapidly with interest rates! But also in the aftermath where interest rates, and subsequently the cost of debt, might drop.
Overall, this article described how to use python to construct the well-known Earnings Power Value model, introduced back in the days by legendary investors Graham & Dodd.
With it, we can estimate a sustainable level for distributable earnings by calculating sustainable NOPAT. Subsequently, we have estimated the required rate of return by utilizing the CAPM and WACC model. Together it gives an estimate of fair share value.
