My documentation and code snippets for the Valuation Course by Prof. Aswath Damodaran.
These functions and formulas were taken directly from this Excel spreadsheet: Valuation of GameStop (January 28, 2021)
Each component in this cheatsheet is nested at increasing levels. I have also indented with the dash (-) character to make the nesting a bit easier to visually distinguish.
You may click on the heading of each component to toggle the detailed view of that component and its child components.
Reading linearly is not advisable, you will quickly get lost once the levels reaches 10+ deep. Make use of the Used In, Components, Details, and Related links to navigate through the formulas.
-(lv1) Value of Equity in Common Stock
This is the final value that we are looking for. Divided by the number of shares, this becomes the Value/Share, that can be directly compared with the current price of the stock to determine whether it is under/overvalued.
Value of Equity in Common Stock = Value of Equity - Value of Options
--(lv2) Value of Equity
Value of Equity = Value of operating assets - Debt - Minority Interests + Cash + Cash from new issue + Cross holdings and other Non-operating Assets
- Value of operating assets
- Debt
- Minority Interests
- Cash
- Cash from new issue
- Cross holdings and other Non-operating Assets
---(lv3) Value of operating assets
Value of operating assets = Sum of PV(Cash Flow) * (1 - Probability of failure) + Proceeds if Firm fails * Probability of failure
Probability of failure is determined qualitatively.
----(lv4) Sum of PV(Cash Flow)
Sum of PV(Cash Flow) = PV(Terminal Value) + PV(Non-terminal Cash Flow)
-----(lv5) PV(Terminal Value)
PV(Terminal Value) = Terminal Value * Cumulated Discount Factor
------(lv6) Terminal Value
Terminal Value = Terminal Cash Flow / (Terminal Cost of Capital - Revenue Growth Rate)
Terminal Cash Flow is also known as FCFF (Free Cash Flow to Firm) of the terminal year.
-------(lv7) FCFF
For each year, including the terminal year, the formula of FCFF is:
FCFF = After-tax EBIT - Reinvestment
--------(lv8) After-tax EBIT (Earnings before Interest and Taxes)
There are three flavors of After-tax EBIT:
After-tax EBIT for the first year This component is calculated with the following function:
IF EBIT > 0:
EBIT * (1 - Tax Rate for EBIT calculation)
ELSE:
0
After-tax EBIT for the last year We assume the terminal year would definitely have a positive EBIT, so the After-Tax EBIT is calculated with this function:
After-tax EBIT = EBIT * (1 - Tax Rate for EBIT calculation)
After-tax EBIT for the next year until the terminal year This component is calculated with the following function:
IF EBIT > 0:
IF EBIT < NOL in the previous year:
EBIT
ELSE:
EBIT - (EBIT - NOL in the previous year) * Tax Rate for EBIT calculation
ELSE:
EBIT
Basically, this function reduces the amount used for tax calculation with the Net Operating Losses from the previous year.
The latest regulation seems to limit this amount to 80% of the EBIT, so the alternative formula would be something like:
IF EBIT > 0:
IF EBIT < NOL in the previous year * 80%:
EBIT - (EBIT * 80% * Tax Rate for EBIT calculation)
ELSE:
EBIT - (EBIT - NOL in the previous year * 80%) * Tax Rate for EBIT calculation
ELSE:
EBIT
---------(lv9) EBIT (or Operating Income - depending on the valuation style)
Depending on the company style, you may use either EBIT or Operating Income. I can't tell for sure which one was used in the GameStop valuation since the numbers are different from those in Yahoo Finance, but it was likely to be Operating Income since the "Stories to Numbers" sheet shows "Operating margin" instead of "EBIT margin".
EBIT for the first year is calculated with this function:
IF Have lease commitments:
IF Have R&D to capitalize:
Operating Income or EBIT
+ Adjustment to Operating Income from leases
+ Adjustment to Operating Income from R&D
ELSE:
Operating Income or EBIT
+ Adjustment to Operating Income from leases
ELSE:
IF Have R&D to capitalize:
Operating Income or EBIT
+ Adjustment to Operating Income from R&D
ELSE:
Operating Income or EBIT
For subsequent years until the terminal year, use the following formula:
EBIT = EBIT margin * Revenues
- Operating Income or EBIT
- EBIT margin
- Revenues
- Adjustment to Operating Income from leases
- Adjustment to Operating Income from R&D
----------(lv10) EBIT margin
EBIT margin is decided qualitatively. It is unique to each company.Here are the factors Used In the GameStop spreadsheet:
- Revenue growth rate for next year. This one is purely qualitative.
- Target Pre-tax EBIT margin (also known as target pre-tax operating margin) This is the EBIT as % of sales in year 10. To get this number, look at the company's current pre-tax EBIT margin and the average for the industry.
- Year of convergence The year in which the current margin will converge on target.
For each "year" in the valuation (the year next to the base year is year 1) starting from year 2, set the EBIT Margin with the result of this function:
IF year > Year of convergence:
use Target Pre-tax EBIT margin
ELSE:
Target Pre-tax operating margin - ((Target Pre-tax EBIT margin - previous year's EBIT margin) / (Year of convergence - year))
In other words, simply grow linearly between year 2 and the terminal year, capped at Target pre-tax EBIT margin.
- Target pre-tax EBIT margin
- Previous year's EBIT margin
- Year of convergence
----------(lv10) Revenues
this year's Revenue = previous year's Revenue * (1 + Revenue growth rate)
- Previous year's Revenue
- Revenue growth rate
-----------(lv11) Revenue growth rate
Just like EBIT margin growth, the Revenue growth rates are decided qualitatively throughout the years.
In the GameStop's case, it was set at a constant 2% from year 2 all the way through the terminal year. In the case Amazon in the 2000's, it started at 150% and grow half each year down to a stable growth value of 6% in the terminal year.
----------(lv10) Adjustment to Operating Income from leases
Adjustment to Operating Income from leases = Operating lease expense in the current year - Depreciation on Operating Lease Asset
- Operating lease expense in the current year
- Depreciation on Operating Lease Asset
-----------(lv11) Depreciation on Operating Lease Asset
Depreciation on Operating Lease Asset = Debt Value of Leases / (Number of years the lease is known + Number of years embedded in lease estimation)
- Debt Value of Leases / Adjustment to Total Debt outstanding from leases
- Number of years the lease is known
- Number of years embedded in lease estimation
----------(lv10) Adjustment to Operating Income from R&D
Adjustment to Operating Income from R&D = Current year's R&D expense - Amortization of R&D Asset for the current year
- Current year's R&D expense
- Amortization of R&D Asset for the current year
-----------(lv11) Amortization of R&D Asset for the current year
This value is calculated for the assumed length of R&D lifetime with the formula of
Amortization of R&D Asset for the current year = sum of Amortized portion of R&D Expenses from previous years
To calculate the Amortized portion of R&D Expenses from previous years, we multiply each year's expense from the previous assumed R&D lifetime by the assumed amortization rate 1 / R&D lifetime.
For example, if the length of R&D lifetime is assumed to be five years, then each amortized portion of the previous five years is R&D Expense * (1 / 5).
Here is the table display for this calculation:
---------(lv9) NOL (Net Operating Losses)
The company may have Net Operating Losses from prior years that may be used to reduce tax calculations for the subsequent years.---------(lv9) Tax Rate for EBIT calculation
In the spreadsheet, the tax rates for the first five years are constant, set to the Effective Tax Rate, and then increase/decrease linearly until the terminal year. To determine the tax rate for the terminal year, use the following conditional operator:
The first assumption is the effective tax rate will adjust to the result of this function:
IF assume the effective tax rate will adjust to the marginal tax rate in the terminal year?
use Marginal Tax Rate
ELSE:
use Effective Tax Rate
--------(lv8) Reinvestment
Reinvestments are calculated differently for the terminal year and pre-terminal years. For our purposes, Reinvestment in the terminal year is called Terminal Reinvestment and before it Pre-terminal Reinvestment.
---------(lv9) Pre-terminal Reinvestment
Reinvestment for subsequent years after the base year (but before the terminal year) is the result of the following function:
IF this year's Revenue > previous year's Revenue:
(this year's Revenue - previous year's Revenue) / Sales to Capital Ratio
ELSE:
0
----------(lv10) Sales to Capital Ratio
A ratio that tells us how efficient the company converts their capital to sales. Look at the industry averages in the worksheet or consult this page: Capital Expenditures by Sector (US)
---------(lv9) Terminal Reinvestment
Reinvestment in the terminal year is determined with the following function:
IF Revenue growth rate in the terminal year > 0:
(Revenue growth rate in the terminal year / ROIC in the terminal year) * After-tax EBIT
ELSE:
0
- Revenue growth rate in the terminal year
- Terminal ROIC
----------(lv10) ROIC (Return on Invested Capital)
The ROICs are calculated and treated differently before and in the terminal year.
Terminal ROIC is determined first, and then calculate other parts of the DCF valuation. Earlier years' ROICs are then calculated after the Invested Capital and After-tax EBIT are calculated.
-----------(lv11) Terminal ROIC
Start by determining the ROIC for the terminal year with the following function:
The default assumption is the firm will earn a "Return on Capital" equal to its "Cost of Capital" at maturity. This assumes that whatever competitive advantages the company has today will fade over time.
IF override fading competitive advantages assumption:
use Expected ROIC at Maturity
ELSE:
use Cost of Capital at the year before the terminal year
------------(lv12) Cost of Capital (COC)
Control the year 1 and terminal year Cost of Capital. For the years between year 1 and the terminal year, qualitatively determine the growth function.
- Year 1's Cost of Capital is called Initial Cost of Capital.
- Cost of Capital in the terminal year is called Terminal Cost of Capital
-------------(lv13) Initial Cost of Capital
Either enter as a setting or use the "Cost of capital worksheet" to determine its value.
In the "Cost of capital worksheet", the formula for the Cost of Capital is as follows:
Cost of Capital (COC) = Weight of Equity in COC * Cost of Equity + Weight of Debt in COC * Cost of Debt + Weight of Preferred Stock in COC * Cost of Preferred Stock
- Weight of Equity in COC
- Cost of Equity
- Weight of Debt in COC
- Cost of Debt
- Weight of Preferred Stock in COC
- Cost of Preferred Stock
--------------(lv14) Weight of Equity in COC
Weight of Equity in COC = Market Value of Equity / Market Value of Capital Structure
---------------(lv15) Market Value of Equity
Market Value of Equity is just the Market Cap, that is,Market Value of Equity = Number of Shares Outstanding * Current Market Price per Share
---------------(lv15) Market Value of Capital Structure
This component is composed of the total market cap and liabilities of a company.
Market Value of Capital Structure = Market Value of Equity + Market Value of Debt + Market Value of Preferred Stock
--------------(lv14) Cost of Equity (COE)
Cost of Equity = Riskfree Rate + Levered Beta for Equity * ERP for COC
---------------(lv15) Riskfree Rate
This should be today's long term riskfree rate. If you are working with a currency where the government has default risk, clean up the government bond rate to make it riskfree (by subtracting the default spread for the government).---------------(lv15) Levered Beta for Equity
Levered beta for equity is the beta Used In the Cost of Equity calculation.
If not direct input, use the unlevered beta calculated above for the levered beta calculation.
IF "Direct Input":
Use Levered Beta
ELSE:
Unlevered Beta * (1+(1-Marginal Tax Rate)*(Market Value of Debt / Market Value of Equity)))
----------------(lv16) Unlevered Beta
IF "Single Business(US)":
Lookup "Industry Name" in the "Industry Average Beta (US)" to find "Unlevered Beta"
ELSE IF "Multibusiness(US)":
Get the weighted average of Unlevered Beta in the "Multi Business (US Industry Averages)" table
ELSE IF "Single Business(Global)":
Lookup "Industry Name" in the "Industry Average Beta (Global)" to find "Unlevered Beta"
ELSE:
Get the weighted average of Unlevered Beta in the "Multi Business (Global Industry Averages)" table
To get the weighted average, first calculate the Estimated Value for each business by its Revenues:
Estimated Value for a business = Revenues for that business * EV/Sales for that Business
And then,
Weighted Average of Unlevered Beta = Sum(Unlevered Beta for a business * Estimated Values for that business / sum of Estimated Values for all businesses)
The Unlevered Beta and EV/Sales tables can be read/downloaded from the following links in prof. Damodaran's website (the Excel Spreadheet versions are said to be more complete):
- For Betas:
- Betas by Sector (US) or Excel spreadsheet.
- Betas by Sector (Global) - Excel Spreadsheet (no webpage for the global data, it seems)
- For EV/Sales:
----------------(lv16) Market Value of Debt
This is not a commonly available information. Here is how to estimate Market Value of Debt in the above formula:
Est. Market Value of Debt = Est. Market Value of Straight Debt + Est. Market Value of Straight Debt in Convertible + Value of Debt in Operating Leases
- Est. Market Value of Straight Debt
- Est. Market Value of Straight Debt in Convertible
- Value of Debt in Operating Leases
-----------------(lv17) Est. Market Value of Straight Debt
Est. Market Value of Straight Debt =
Interest Expense * (1 - (1 + Pre-tax Cost of Debt) ** (-Average Debt Maturity)) / Interest Expense
+ Book Value of Straight Debt / (1 + Pre-Tax Cost of Debt) ** Average Debt Maturity
- Average Debt Maturity is generally found in the footnotes of financial statements.
- Book Value of Straight Debt is also commonly known as "Total Debt" in the Balance Sheet (at least in Yahoo Finance).
------------------(lv18) Pre-tax Cost of Debt
IF Approach for estimating Pre-tax COD = "Direct Input":
Direct input of Pre-tax Cost of Debt
ELSE IF Approach for estimating Pre-tax COD = "Synthetic Rating":
Estimated COD in Synthetic Rating
ELSE (IF Approach for estimating Pre-tax COD = "Actual Rating"):
Rating Spread = Lookup "actual rating" in the rating spread table in the "Synthetic Rating" to get "rating spread"
Pre-tax Cost of Debt = Risk-free Rate + Rating Spread
- Est. Market Value of Straight Debt
- Est. Market Value of Straight Debt in Convertible
- Cost of Debt
- PV of Expected lease commitments
- Interest Expense plus lease adjustment
-------------------(lv19) Estimated COD in Synthetic Rating
Estimated Cost of Debt = Risk-free Rate + Estimated Company Default Spread + Estimated Country Default Spread (if any)
The calculation of this component will lead to a circular reference problem for the case where the firm has a lease commitment, since the lease adjustment requires Cost Of Debt to calculate.
The circular dependency issue is as illustrated below:
To deal with this circular dependency issue, we iterate the calculation of Estimated COD in Synthetic Rating until the Pre-tax COD converges. The initial value of the Pre-tax COD is set to the Riskfree Rate + Estimated Country Default Spread.
--------------------(lv20) Estimated Company Default Spread
The formula depends on whether the company has a large market cap (> \$5billion) or small market cap (< \$5billion or volatile earnings or is in risky business)
IF Large Market Cap:
Lookup "Interest Coverage Ratio" in the large manufacturing firms table to get "spread"
ELSE IF Small Market Cap:
Lookup "Interest Coverage Ratio" in the smaller and riskier firms table to get "spread"
ELSE:
(there is a missing reference in the spreadsheet)
The spread can be looked up from this web page: Ratings, Interest Coverage Ratios and Default Spread
---------------------(lv21) Interest Coverage Ratio
This is the ratio that tells us how capable the earnings of the company is to pay the current interest expense. This is used in the default spread estimation (i.e. the likelihood of the company to default on their debts).
IF stable firm:
IF Interest Expense plus lease adjustment = 0:
100000
ELSE:
IF current EBIT plus lease adjustment < 0:
-100000
ELSE:
current EBIT plus lease adjustment / current Interest Expense plus lease adjustment
ELSE:
IF Interest Expense in terminal year plus lease adjustment = 0:
100000
ELSE:
IF EBIT in terminal year plus lease adjustment < 0:
-100000
ELSE:
EBIT in terminal year plus lease adjustment / Interest Expense in terminal year plus lease adjustment
This function will cause a circular dependency when Approach for estimating Pre-tax COD = "Synthetic Rating" because EBIT plus lease adjustment and Adjustment to Total Debt Outstanding from leases also need Pre-tax COD.
To deal with this circular dependency issue, we iterate the calculation of Estimated COD in Synthetic Rating until the Pre-tax COD converges. Learn more about this from the documentation of Estimated COD in Synthetic Rating.
- Interest Expense plus lease adjustment
- EBIT plus lease adjustment
- Operating Income or EBIT
- Interest Expense
----------------------(lv22) Interest Expense plus lease adjustment (Current Interest Expense in the spreadsheet)
IF Have lease commitments:
Interest Expense + Adjustment to Total Debt Outstanding from leases * Pre-tax COD
ELSE:
Interest Expense
- Interest Expense
- Adjustment to Total Debt Outstanding from leases
- Pre-Tax COD
----------------------(lv22) EBIT plus lease adjustment (Current EBIT in the spreadsheet)
This is similar to the adjusted EBIT used in the current year, but without the R&D adjustment. Why is it like that? I don't know, but probably because the default spread table was calculated without including the R&D adjustments for the companies involved (this is purely my speculation).
The function used to determine this value is as follows:
IF Have lease commitments:
Operating Income or EBIT
+ Adjustment to Operating Income from leases
ELSE:
Operating Income or EBIT
- Operating Income or EBIT
- Adjustment to Operating Income from leases
--------------------(lv20) Estimated Country Default Spread (if any)
Lookup the country the company is incorporated in in the Country equity risk premiums sheet to get Adjusted Default Spread. The latest Country Equity Risk Premiums sheet can be downloaded from prof. Damodaran's website here (sheet name is "Regional Weighted Averages").
-----------------(lv17) Est. Market Value of Straight Debt in Convertible
Est. Market Value of Straight Debt in Convertible =
Interest Expense on Convertible * (1 - (1 + Pre-tax Cost of Debt) ** (-Maturity of Convertible Bond)) / Pre-tax Cost of Debt
+ Book Value of Convertible Debt / (1 + Pre-tax Cost Of Debt) ** Maturity of Convertible Bond
- Interest Expense on Convertible
- Pre-tax Cost of Debt
- Maturity of Convertible Bond
- Book Value of Convertible Debt
- Market Value of Convertible
------------------(lv18) Book Value of Convertible Debt
Todo: Clarify
From AVC.com:
Convertible debt is when a company borrows money from an investor or a group of investors and the intention of both the investors and the company is to convert the debt to equity at some later date. Typically the way the debt will be converted into equity is specified at the time the loan is made. Sometimes there is compensation in the form of a discount or a warrant. Other times there is not. Sometimes there is a cap on the valuation at which the debt will convert. Other times there is not.
The typical forms of compensation for making a convertible loan are warrants or a discount.
------------------(lv18) Interest Expense on Convertible
Todo: Clarify
Interest expense on convertible debts
------------------(lv18) Maturity of Convertible Bond
Todo: Clarify
From Investopedia:
A convertible bond is a fixed-income corporate debt security that yields interest payments, but can be converted into a predetermined number of common stock or equity shares. The conversion from the bond to stock can be done at certain times during the bond's life and is usually at the discretion of the bondholder.
------------------(lv18) Market Value of Convertible
Todo: Clarify
-----------------(lv17) Value of Debt in Operating Leases
IF Have operating lease commitments:
"Adjustment to Total Debt Outstanding" in the "Operating Lease Converter" sheet
ELSE:
0
------------------(lv18) Adjustment to Total Debt Outstanding from leases
To get this value, compute the Sum of PV(Cash Flow)(expected lease commitments)
Expected lease commitments are the expected lease expenses in the future, both for the foreseeable future and beyond that.
Here is the calculation table for this component:
Lease commitments for the foreseeable years are gathered from the financial statements, and then beyond that determined qualitatively.
- Value of Debt in Operating Leases
- Invested Capital
- Depreciation on Operating Lease Asset
- Interest Expense plus lease adjustment
- PV of lease commitments
- PV of Expected lease commitments
-------------------(lv19) PV of Expected lease commitments
PV of Expected lease commitments in the foreseeable years is calculated by the following formula:
PV of Expected lease commitment in a foreseeable year = Expected lease commitment for that year / (1 + Pre-tax Cost of Debt) ** Number of year in the future
PV of Expected lease commitments beyond the foreseeable years is determined by the following function:
IF Number of years embedded in the estimate > 0:
(Expected lease commitments * (1 - (1 + Pre-tax Cost of Debt)**(-Number of years embedded in the estimate)) / Pre-tax Cost of Debt)
/ (1 + Pre-tax Cost of Debt)**(Number of years the lease is known)
ELSE:
Expected lease commitments / (1 + Pre-tax Cost of Debt)**(Number of years the lease is known + 1)
- Expected lease commitments
- Pre-tax Cost of Debt
- Number of years the lease is known
--------------------(lv20) Expected lease commitments
Expected lease commitments are divided into two categories:
- Leases for the foreseeable years, and
- leases beyond the foreseeable years.
For the latter, the estimation is done qualitatively, usually through some sort of average over the known number of years.
When calculating PV of Expected lease commitments, the lease commitments for the foreseeable years can be used directly, but the commitments beyond that must be converted into an annuity for ten years with the following function:
IF Expected lease commitments beyond the foreseeable years > 0:
IF Number of years embedded in lease estimation > 0:
Expected lease commitments beyond the foreseeable years / Number of years embedded in lease estimation
ELSE:
Expected lease commitments beyond the foreseeable years
ELSE:
0
- Expected lease commitments beyond the foreseeable years
- Number of years embedded in lease estimation
---------------------(lv21) Number of years embedded in lease estimation
This is a number that tells us how many years does the total Expected lease commitments beyond the foreseeable years covers, assuming that the annual lease commitments are going to be the same with the expected lease commitments in the foreseeable years.
IF Expected lease commitments beyond the foreseeable years > 0:
ROUND(Expected lease commitments beyond the foreseeable years / AVERAGE(Expected lease commitments for the foreseeable years))
- Expected lease commitments beyond the foreseeable years
- Expected lease commitments for the foreseeable years
---------------(lv15) ERP for COC (Equity Risk Premium for Cost of Capital)
IF approach is "Will Input":
Manual input
ELSE IF approach is "Country of Incorporation":
Lookup "Country of Incorporation" in the "Country equity risk premiums" sheet to get "Equity Risk Premium"
ELSE IF approach is "Operating regions":
Get the "Total Weighted ERP" from the "Operating Regions ERP calculator" table
ELSE:
Get the "Total Weighted ERP" from the "Operating Countries ERP calculator" table
To get the Total Weighted ERP, first calculate the weight of revenues from each country/region:
Weight = Revenues / Total Revenues
and then calculate the Weighted ERP for each country/region with the following formula:
Country/Region Weighted ERP = ERP for the country/region * Weight
The sum(Country/Region Weighted ERP) is the Total Weighted ERP.
The ERP can be looked up from the "Country equity risk premiums" sheet or Country Default Spreads and Risk Premiums page, column "Equity Risk Premium". The Excel sheet - which is more complete - can be downloaded here, from the sheet name "Regional Weighted Averages".
--------------(lv14) Weight of Debt in COC
Weight of Debt in COC = Market Value of Debt / Market Value of Capital Structure
--------------(lv14) Cost of Debt
Cost of Debt = Pre-tax Cost of Debt * (1 - Marginal Tax Rate)
- Pre-tax Cost of Debt
- Marginal Tax Rate
--------------(lv14) Weight of Preferred Stock in COC
Weight of Preferred Stock in COC = Market Value of Preferred stock / Market Value of Capital Structure
---------------(lv15) Market Value of Preferred Stock
Market Value of Preferred Stock = Number of Preferred Shares * Current Market Price per Share
Find the Number of Preferred Shares and the Current Market Price per Share in the financial statements.
--------------(lv14) Cost of Preferred Stock
Cost of Preferred Stock = Annual Dividend per Preferred Share / Current Market Price per Preferred Share
Find the Annual Dividend per Preferred Share and Current Market Price per Preferred Share in the financial statements.
-------------(lv13) Terminal Cost of Capital
This is the Cost of Capital in the terminal year.
The default assumption is the firm will eventually have a "Cost of Capital" similar to that of typical mature companies (Riskfree Rate + Mature Market ERP).
Set the Terminal Cost of Capital to the result of the following function:
IF override fading risk levels:
Manual input
ELSE:
IF override assumption that today's Riskfree Rate will prevail in perpetuity:
Manual input of Terminal Riskfree Rate + Mature Market ERP
ELSE:
Riskfree Rate + Mature Market ERP
To get the Mature Market ERP, find the "Total Equity Risk Premium" of United States from this page: Country Default Spreads and Risk Premiums.
- Riskfree Rate
- Terminal Riskfree Rate
- Mature Market Equity Risk Premium
-----------(lv11) Pre-terminal ROIC
ROIC for each year before the terminal year is calculated with the following formula:
this year's ROIC = this year's After-tax EBIT / Invested Capital
------------(lv12) Invested Capital
IF Have lease commitments:
IF Have R&D to capitalize:
Book Value of Equity
+ Book Value of Debt
- Cash and Marketable Securities
+ Adjustment to Total Debt Outstanding from leases
+ Value of Research Assets
ELSE:
Book Value of Equity
+ Book Value of Debt
- Cash and Marketable Securities
+ Adjustment to Total Debt Outstanding from leases
ELSE:
IF Have R&D to capitalize:
Book Value of Equity
+ Book Value of Debt
- Cash and Marketable Securities
+ Value of Research Assets
ELSE:
Book Value of Equity
+ Book Value of Debt
- Cash and Marketable Securities
- Book Value of Equity
- Book Value of Debt
- Cash and Marketable Securities
- Adjustment to Total Debt Outstanding from leases
- Value of Research Assets
-------------(lv13) Book Value of Equity
Enter the book value of equity (total) from the end of the most recent time period (i.e. the most recent balance sheet). This book equity will include everything - paid in capital, retained earnings etc. and may even be negative for companies that have been losing money for a while.
-------------(lv13) Book Value of Debt
Book Value of Debt is also called "Total Debt" in the balance sheet.
-------------(lv13) Cash and Marketable Securities
Enter the cash balance from the most recent balance sheet. This should include marketable securities.
Cash and Marketable Securities is also called "Cash, Cash Equivalents & Short Term Investments" in the balance sheet.
-------------(lv13) Value of Research Assets
This is the sum of all Unamortized portion of R&D Expenses as shown here:
--------------(lv14) Unamortized portion of R&D Expenses
This value is calculated for the assumed length of R&D lifetime. For example, if the length of R&D lifetime is assumed to be five years, then get the R&D Expenses for the previous five years, reduce each year's expense with the amortized portion.
If we are calculating the value for year 2020, for instance, then the R&D Expense of year 2015 would worth 0 since 100% of its portion is amortized, followed by 20% of year 2016's R&D Expense, then 40%, all the way to 100% in the year 2020's R&D Expense.
Unamortized portion of R&D Expenses = R&D Expense * unamortized portion
------(lv6) Cumulated Discount Factor
For each year in the DCF calculation, calculate the Cumulated Discount Factor as follows:
IF the year after the current year:
1 / (1 + this year's COC)
ELSE:
previous year's Cumulated Discount Factor / (1 + this year's COC)
- previous year's Cumulated Discount Factor
- Cost of Capital
-----(lv5) PV(Non-terminal Cash Flow)
PV(this year's FCFF) = this year's FCFF * this year's Cumulated Discount Factor
---(lv3) Debt
IF Have operating lease commitments:
Book Value of Debt + "Adjustment to Total Debt Outstanding" in the "Operating Lease Converter" sheet
ELSE:
Book Value of Debt
---(lv3) Minority Interests
The "market" value of minority interests. This is a uniquely accounting item and will be on the liability side of your company's balance sheet.
It reflects the requirement that if you own more than 50% of another company or have effective control of it, you have to consolidate that company's statements with yours. Thus, you count 100% of that subsidiaries assets, revenues and operating income with your company, even if you own only 60%. The minority interest reflects the book value of the 40% of the equity in the subsidiary that does not belong to you.
It is best if you can convert the book value to a market value by applying the price to book ratio for the sector in which the subsidiary operates.
---(lv3) Cash
IF override the assumption that none of the cash is trapped in foreign currencies and there is no additional tax liability coming due and that cash is a neutral asset:
Cash and Marketable Securities - Enter trapped cash (if taxes) or entire balance (if mistrust) * (Marginal Tax Rate - Average Tax Rate of the foreign markets where the cash is trapped)
ELSE:
Cash and Marketable Securities
Enter trapped cash (if taxes) or entire balance (if mistrust) If your concern is that a portion of the cash is trapped in foreign markets and will be subject to tax, when returned, enter the trapped cash balance. If you feel that the entire cash balance is being discounted because markets don't trust managers, enter the entire cash balance.
Average Tax Rate of the foreign markets where the cash is trapped This is the additional tax due, if the cash is trapped cash. If your concern is that all cash is being discounted by the market because of management mistrust, enter the percentage discount to apply to cash.
- Cash and Marketable Securities
- Trapped cash (if taxes) or entire balance (if mistrust)
- Marginal Tax rate
- Average Tax Rate of the foreign markets where the cash is trapped
---(lv3) Cash from new issue
Cash from new issue = New shares issued * Expected issuance price
- New shares issued
- Expected issuance price
---(lv3) Cross holdings and other Non-operating Assets
Enter the market value of those non-cash assets whose earnings are (and will never) show up as part of operating income.
The most common non-operating assets are minority holdings in other companies (which are not consolidated). You can find the book value of these holdings on the balance sheet, but see if you can convert to market value. (I apply a price to book ratio, based on the sector that the company is in to the book value).
--(lv2) Value of Options
IF Have employee options outstanding:
"Value of all options outstanding" in the "Option value" sheet
ELSE:
0
---(lv3) Value of all options outstanding
Value of all options outstanding = Value per option * Number of warrants (options) outstanding
----(lv4) Value per Option
Use the Black-Scholes model to value the options.
Value per Option = (e**((0 - Annualized dividend yield on stock) * Option expiration (in years)))
* Adjusted S * NORMSDIST(d1)
- Option Strike Price * (e**((0 - T. Bond Rate) * Option expiration (in years))) * NORMSDIST(d2)
- Annualized dividend yield on stock
- Option expiration (in years)
- Black-Scholes - Adjusted S
- NORMSDIST
- Black-Scholes - d1
- Option Strike Price - This is also known as Adjusted K
- T. Bond Rate (use Riskfree rate for this)
- Black-Scholes - d2
-----(lv5) Black-Scholes - Adjusted S
Adjusted S = (Stock Price * Number of Shares outstanding + Value per Option * Number of Warrants) / (Number of Shares outstanding + Number of Warrants)
This formula would cause a circular dependency error. Solve it with iterative calculation.
- Value per Option
- Stock Price
- Number of Warrants
- Number of Shares outstanding
-----(lv5) NORMSDIST
NORMSDIST is a function to get the probabilty that a value is between 0 and a given input in a standard normal distribution.
Here is an example:
In the above image, α is 1.53. What is the probability that x is less than or equal 1.53? By entering NORMSDIST(1.53) we get a value of 93.70%.
-----(lv5) Black-Scholes - d1
d1 = (Log_e(Adjusted S / Option Strike Price) + (Div. Adj. interest rate + (Variance/2)) * Option expiration (in years))
/ (((Variance/2)**0.5) * (Option expiration (in years) ** 0.5))
Log_e is Log function with the base e (Euler's number)
- Black-Scholes - Adjusted S
- Option Strike Price - This is also known as Adjusted K
- Black-Scholes - Div. Adj. interest rate
- Black-Scholes - Variance
- Option expiration (in years)
------(lv6) Black-Scholes - Div. Adj. interest rate
Div. Adj. interest rate = T. Bond Rate - Annualized dividend yield on stock
- T. Bond Rate (use Riskfree rate for this)
- Annualized dividend yield on stock
------(lv6) Black-Scholes - Variance
Variance = Standard deviation on stock price ** 2
-------(lv7) Standard deviation on stock price
If you have a standard deviation for your stock, enter that number. If not, use the industry average standard deviation from the worksheet.
-----(lv5) Black-Scholes - d2
d2 = d1 - ((Variance**0.5) * (Option expiration (in years) ** 0.5))
- Black Scholes - d1
- Black Scholes - Variance
- Option expiration (in years)
----(lv4) Number of options outstanding
Check your company's annual report or 10K. If it does have options outstanding, enter the total number here (vested and non vested, in the money and out…).