Description
A spreadsheet describes the workings of Free Cash Flow to Firm (FCFF) Valuation Model for organizations with stable growth rates.
FCFF Stable Model
FCFF STABLE GROWTH MODEL
This model is designed to value a stable firm on the basis of free cashflows to firm.
Assumptions in the model: 1. The firm is in steady state and will grow at a stable rate forever. 2. The firm's leverage is known and constant. User defined inputs The user has to define the following inputs to the model: 1. Current EBIT and tax rate 2. Capital Spending and Depreciation 3. Change in working capital 4. Debt ratio 5. Cost of Equity or Inputs to the CAPM (Beta, Riskfree rate, Risk Premium) and Cost of Debt 6. Expected Growth Rate in free cashflows to firm forever.
Page 1
FCFF Stable Model
Please enter inputs to the model: Current EBIT Current tax rate = $1,535.00 36% (in currency)
Capital Expenditures Depreciation = Change in Working Capital =
$550.00 $400.00 $160.00
(in currency) (in currency) (in currency) If negative, enter zero. Yes 125% ( in percent) (Yes or No)
Do you want to change the capital expenditure/depreciation ratio? If so, enter capital expenditures as a percent of depreciation Debt ratio = 29.97%
Are you directly entering the cost of equity? (Yes or No) If yes, enter cost of equity = If no, enter the inputs for the CAPM Beta of the stock = Riskfree rate = Risk Premium= 1.1 7% 5.50% (in percent) (in percent) (in percent)
No
Enter the cost of debt =
8.50%
This is a pre-tax cost of borrowing.
Expected Growth Rate =
5%
(in percent)
The expected growth rate for a stable firm cannot be significantly higher than the nominal growth rate in the economy in which the firm operates. It can be lower.
Page 2
FCFF Stable Model
Warnings:
Page 3
FCFF Stable Model
This is the output from the Model Firm Details: from inputs on prior page EBIT (1- tax rate) = - (Capital Spending - Depreciation) - Change in Working Capital Free Cashflow to Firm = $982.40 $100.00 $160.00 $722.40
Cost of Equity = Cost of Debt = Cost of Capital = Expected Growth rate =
13.05% 5.44% 10.77% 5.00%
Value of Firm
$13,147.56
Value of Stock
Growth rate 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00%
Value $20,507.03 $16,055.75 $13,147.56 $11,098.61 $9,577.10 $8,402.60 $7,468.55
Value vs. Expected Growth $25,000.00 $20,000.00
$15,000.00
$10,000.00 $5,000.00 $0.00 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00%
Page 4
FCFF Stable Model $0.00 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00%
Expected Growth Rate
Page 5
doc_422983764.xls
A spreadsheet describes the workings of Free Cash Flow to Firm (FCFF) Valuation Model for organizations with stable growth rates.
FCFF Stable Model
FCFF STABLE GROWTH MODEL
This model is designed to value a stable firm on the basis of free cashflows to firm.
Assumptions in the model: 1. The firm is in steady state and will grow at a stable rate forever. 2. The firm's leverage is known and constant. User defined inputs The user has to define the following inputs to the model: 1. Current EBIT and tax rate 2. Capital Spending and Depreciation 3. Change in working capital 4. Debt ratio 5. Cost of Equity or Inputs to the CAPM (Beta, Riskfree rate, Risk Premium) and Cost of Debt 6. Expected Growth Rate in free cashflows to firm forever.
Page 1
FCFF Stable Model
Please enter inputs to the model: Current EBIT Current tax rate = $1,535.00 36% (in currency)
Capital Expenditures Depreciation = Change in Working Capital =
$550.00 $400.00 $160.00
(in currency) (in currency) (in currency) If negative, enter zero. Yes 125% ( in percent) (Yes or No)
Do you want to change the capital expenditure/depreciation ratio? If so, enter capital expenditures as a percent of depreciation Debt ratio = 29.97%
Are you directly entering the cost of equity? (Yes or No) If yes, enter cost of equity = If no, enter the inputs for the CAPM Beta of the stock = Riskfree rate = Risk Premium= 1.1 7% 5.50% (in percent) (in percent) (in percent)
No
Enter the cost of debt =
8.50%
This is a pre-tax cost of borrowing.
Expected Growth Rate =
5%
(in percent)
The expected growth rate for a stable firm cannot be significantly higher than the nominal growth rate in the economy in which the firm operates. It can be lower.
Page 2
FCFF Stable Model
Warnings:
Page 3
FCFF Stable Model
This is the output from the Model Firm Details: from inputs on prior page EBIT (1- tax rate) = - (Capital Spending - Depreciation) - Change in Working Capital Free Cashflow to Firm = $982.40 $100.00 $160.00 $722.40
Cost of Equity = Cost of Debt = Cost of Capital = Expected Growth rate =
13.05% 5.44% 10.77% 5.00%
Value of Firm
$13,147.56
Value of Stock
Growth rate 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00%
Value $20,507.03 $16,055.75 $13,147.56 $11,098.61 $9,577.10 $8,402.60 $7,468.55
Value vs. Expected Growth $25,000.00 $20,000.00
$15,000.00
$10,000.00 $5,000.00 $0.00 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00%
Page 4
FCFF Stable Model $0.00 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00%
Expected Growth Rate
Page 5
doc_422983764.xls