Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 18, Stock valuation page 11
1
2
3
4
5
6
7
8
9
10
11
12
13
AB C
2003 FCF (base year) 2,000,000
Future FCF growth rate 8%
WACC 15%
End-2003 debt 10,000,000
End-2003 cash 1,000,000
Number of shares outstanding 1,000,000
Enterprise value 33,090,599 <-- =B2*(1+B3)/(B4-B3)*(1+B4)^0.5
Add cash 1,000,000 <-- =B6
Subtract debt -10,000,000 <-- =-B5
Value of equity 24,090,599 <-- =SUM(B9:B11)
Share value 24.09 <-- =B12/B7
VALUING ARNOLD CORP
Valuation method 2: Example 2—two FCF growth rates
In the valuation of Arnold Corp. in the previous subsection we assumed a FCF growth
rate which is unchanging over the future. This assumption is often suitable for a mature, stable
company, but it may not be appropriate for a company that is currently experiencing very high
growth rates. In this subsection we show how to perform a FCF valuation of a company for
which we assume two FCF growth rates—a high FCF growth rate for a number of years followed
by a subsequent lower FCF growth rate.
Xanthum Corp. has just finished its 2003 financial year. The company’s 2003 FCF was
$1,000,000. Xanthum has been growing very fast; you anticipate that for the coming 5 years the
FCF growth rate will be 35%. After this time, you anticipate that the FCF growth will slow to
10% per year, because the market for Xanthum’s products will become mature.
Xanthum has 3,000,000 shares outstanding and a WACC of 20%. It currently has
$500,000 of cash on hand which is not needed for operations; Xanthum also has $3,000,000 of
debt. To value the company, we apply the same valuation scheme as before, but this time we use
the two FCF growth rates:
PFE Chapter 18, Stock valuation page 12
()()
()
5
0.5
16
This factor "corrects"
for the fact that FC
The PV of the "high The PV of the "normal
growth" FCFs growth" FCFS
*1
11
tt
tt
tt
FCF FCF
Enterprise value WACC
WACC WACC
∞
==
↑
↑↑
⎡⎤
⎢⎥
⎢⎥
⎢⎥
=+ +
⎢⎥
++
⎢⎥
⎢⎥
⎢⎥
⎣⎦
∑∑
Fs occur
throughout the year.
There’s a valuation formula which can be derived using techniques described in the
appendix to Chapter 1:
()
5
2003 2003
In the spreadsheet this is called
1
"term 1" and is called "term1 factor"
1
1
1
11
1
1
1
1
1
high
high
high h
high
g
WACC
Enterprise value
g
FCFg FCFg
WACC
g
WACC
WACC
↑
+
+
=
⎛⎞
+
⎛⎞
⎜⎟
−
⎜⎟
++
+
⎜⎟
⎝⎠
+
⎜⎟
+
+
⎜⎟
−
⎜+ ⎟
⎝⎠
()
()
()
5
0.5
5
In the spreadsheet this is called
"term 2"
1
*1
1
igh
normal
normal
g
WACC
WACC g
WACC
↑
⎡⎤
⎢⎥
⎢⎥
⎢⎥
⎢⎥
⎢⎥
⎛⎞
+
⎢⎥
+
⎜⎟
⎢⎥
−
+
⎝⎠
⎢⎥
⎢⎥
⎢⎥
⎢⎥
⎢⎥
⎢⎥
⎣⎦
The spreadsheet below shows that Xanthum’s enterprise value is $27,040,649 (cell B15)
and that its per-share value is $8.18 (cell B21):
PFE Chapter 18, Stock valuation page 13
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
AB C
2003 FCF (base year) 1,000,000
High growth rate, g
high
35%
Normal growth rate, g
normal
10%
Number of high growth years 5
Term 1 factor: (1+g
high
)/(1+WACC)
113% <-- =(1+B4)/(1+B9)
WACC 20%
End-2003 debt 3,000,000
End-2003 cash 500,000
Term 1: PV of high-growth cash flows 7,218,292 <-- =B2*B7*(1-B7^B6)/(1-B7)
Term 2: PV of normal-growth cash flows 19,822,357 <-- =B2*(1+B4)^B6*(1+B5)/(B9-B5)/(1+B9)^B6
Enterprise value 27,040,649 <-- =SUM(B13:B14)
Add cash 27,540,649 <-- =B15+B11
Subtract debt -3,000,000 <-- =-B10
Value of equity 24,540,649 <-- =SUM(B16:B17)
Number of shares, end 2003 3,000,000
Share value 8.18 <-- =B18/B20
VALUING XANTHUM CORP
Valuation method 2: Example 3—using the terminal value in a real-estate project
In the previous two examples we discounted an infinitely-lived stream of cash flows.
Sometimes it makes more sense to discount a finite number of cash flows and then attribute a
terminal value to the project.
Here’s an example: Your Aunt Sarah has quite a bit of money. She’s been offered a
share in a partnership which is being set up by a local real estate agent. The partnership will buy
an existing building, called the Station Building, for $20 million. The agent is selling 25 shares,
for $800,000 each (
$20,000,000
$800,000
25
=
). Aunt Sarah has asked you to do some financial
analysis to determine whether this is a fair price for a partnership share in the Station Building.
Here’s what you discover:
PFE Chapter 18, Stock valuation page 14
• All income from the Station Building partnership will flow through to the shareholders,
who will pay taxes on the income at their personal tax rates. Aunt Sarah’s tax rate is
40%.
•
Station Building will be depreciated over 40 years, giving an annual depreciation of
$500,000 per year.
•
The building is fully rented out and brings up annual rents of $7 million. You do not
anticipate that these rents will increase over the next 10 years.
•
Maintenance, property taxes, and other miscellaneous expenses for Station Building cost
about $1 million per year.
•
The agent who is putting together the partnership has proposed selling Station Building
after 10 years. He estimates that the market price of the building will not change much
over this period—meaning that the market price of Station Building in year 10 is
anticipated to be $20 million, like its price today.
In your valuation of the Station Building shares, you see that the annual free cash flow
(FCF) to Aunt Sarah is $152,000 (cell B16 in the spreadsheet below). This FCF will be available
to her in years 1-10, and is based on the building’s profit before taxes of $5,500,000, which will
be spread equally among the partners.
The terminal value of the building is $20,000,000, which on a per-share basis is $800,000
(cell B19). At the time the building is sold in year 10, its accumulated depreciation is
$5,000,000, so that its book value is $15,000,000. To compute Aunt Sarah’s cash flow from this
terminal value, we deduct the per-share book value of the building ($600,000, cell B20) from the
sale price to arrive at taxes of $80,000 on the profit from the sale of the building (cell B22). The
PFE Chapter 18, Stock valuation page 15
cash flow from the sale is the $800,000 sale price minus the taxes--$720,000 as shown in cell
B23.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ABCDEFG
Building cost 20,000,000
Depreciable life (years) 40
Annual rents 7,000,000
Annual expenses 1,000,000
Annual depreciation 500,000 <-- =B2/B3
Profit and loss, Station Building as a whole
Aunt Sarah's tax rate 40% Annual rent 7,000,000
WACC 18% Minus annual expenses -1,000,000
Shares issued 25 Minus annual depreciation -500,000
Share price 800,000 Anticipated annual building profit before taxes 5,500,000 <-- =SUM(F7:F9)
Profit and loss, Aunt Sarah's share
Anticipated annual building profit before taxes 220,000 <-- =F10/B9
Terminal value, year 10, Station Building as a whole
Profit after taxes 132,000 <-- =(1-B7)*B13 Anticipated building market price 20,000,000 <-- =B2
Building depreciation, per share 20,000 <-- =B6/B9 Accumulated depreciation, year 10 5,000,000 <-- =B6*10
Free cash flow 152,000 <-- =B14+B15 Book value of building, year 10 15,000,000 <-- =B2-F15
Terminal value, year 10, Aunt Sarah's share
Anticipated building market price 800,000 <-- =F14/B9
Book value in year 10, per share 600,000 <-- =F16/B9
Profit from sale of building 200,000 <-- =B19-B20
Tax on profit 80,000 <-- =B7*B21
Terminal value: cash flow from sale 720,000 <-- =B19-B22
Year
Aunt Sarah's
anticipated
FCF
1 152,000 <-- =$B$16
2 152,000
3 152,000
4 152,000
5 152,000
6 152,000
7 152,000
8 152,000
9 152,000
10 872,000 <-- =$B$16+B23
Share value: Present value of Aunt Sarah's
free cash flows
$820,667.53 <-- =NPV(B8,B26:B35)
STATION BUILDING PARTNERSHIP--SHARE VALUATION
Cells B26:B35 show Aunt Sarah’s anticipated free cash flows from the building
partnership, including the terminal value. Discounting these cash flows at the WACC of 20%
values a partnership share at $820,667.53. Conclusion: Aunt Sarah should invest in the
building!
Valuation method 2: Example 4—using the terminal value to get around large FCF
growth rates
Our second example of using the terminal value involves the Formanis Corporation.
Formanis is in a growth industry and has had formidable FCF growth rates for the past several
PFE Chapter 18, Stock valuation page 16
years, and you anticipate that these rates will continue for years 1-5. However, after year 5 you
anticipate a big slowdown in Formanis’s FCF growth, as its industry matures.
Here are the relevant facts about Formanis:
•
The company’s FCF for the current year is $1,000,000.
•
You anticipate that the FCF for years 1-5 will grow at a rate of 25% per year.
•
You anticipate a growth rate of FCFs of 6% per year for years 6, 7, … (termed the “long-
term growth rate” in the spreadsheet below).
•
The company has 5 million shares outstanding.
The valuation formula is:
()
()()()()
()
()
()
35
12 4
2345
5
5
This is the terminal value:
an explanation is given in Chapter ??
1
1111
*1 -
1
*
-
1
FCF FCF
FCF FCF FCF
Formanis value
WACC
WACC WACC WACC WACC
FCF long term growth rate
WACC long term growth rate
WACC
↑
=+ + + +
+
++++
+
+
−
+
To value Formanis, we first predict the FCFs for years 1-5 (cells B9:B13 of the
spreadsheet). The present value of these FCFs is $6,465,787 (cell B20). The terminal value
represents the year-5 present value of the Formanis cash flows for years 6, 7, … . To compute
the terminal value, we assume that Formanis’s cash flows for these years grow at the long-term
growth rate:
()
()()
()
()
()
()
()
()
67 7
22
2
55
2
3
5
2
-5 , 6,7,...
...
1
11
*1 - . *1 - .
1
1
*1 - .
...
1
Terminal value year PV of Formanis FCFs years
FCF FCF FCF
WACC
WACC WACC
FCF long term growth rate FCF long term growth rate
WACC
WACC
FCF long term growth rate
WACC
F
=
=+ + +
+
++
++
=+
+
+
+
++
+
=
()
()
5
*1 -
-
CF long term growth rate
WACC long term growth rate
+
−
PFE Chapter 18, Stock valuation page 17
In cell B17 below the terminal value—assuming a long-term FCF growth rate of 6%—is
$17,025,596.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
ABC
Current FCF 1,000,000
Anticipated growth rate, years 1-5 25%
WACC 15%
Long-term growth rate, after year 5 6%
Number of shares outstanding 5,000,000
Year
A
nticipated
FCF
1 1,250,000 <-- =$B$2*(1+$B$3)
2 1,562,500 <-- =B9*(1+$B$3)
3 1,953,125 <-- =B10*(1+$B$3)
4 2,441,406
5 3,051,758
Terminal value calculation
FCF in year 5 3,051,758 <-- =B13
Terminal value 17,025,596 <-- =B16*(1+B5)/(B3-B5)
V
aluing Formanis Corporation
Present value of FCFs, years 1-5 6,465,787 <-- =NPV(B4,B9:B13)
Present value of terminal value 8,464,730 <-- =B17/(1+B4)^5
Value of Formanis 14,930,518 <-- =B21+B20
Per share value $2.99 <-- =B22/B6
FORMANIS CORPORATION
The value of Formanis (cell B22) is $14,930,518. The per-share value of Formanis is
$2.99 (cell B23).
The terminal value method illustrated for Formanis is often used:
•
It allows the stock analyst to distinguish between short-term growth and long-term
growth. Often short-term growth is a function of market performance, whereas long-term
growth is determined by macro-economic factors. For example in a new and rapidly
developing market, we might anticipate high short-term growth rates. But we would also
anticipate that as the market matures and becomes more saturated, the long-term growth
rates would approximate the growth of the economy as a whole.
PFE Chapter 18, Stock valuation page 18
• From an Excel point of view, the terminal value method allows us to do interesting
sensitivity analyses. For example, here is the per-share value of Formanis for a variety of
long-term growth rates and WACCs; we use the
Data Table technique described in
Chapter ???:
26
27
28
29
30
31
32
ABCDEF
Long-term growth rate ↓
$2.99
0% 2% 4% 6%
WACC →
15%
2.51 2.64 2.80 2.99
20% 2.11 2.22 2.35 2.50
25%
1.80 1.89 1.99 2.12
30%
1.55 1.62 1.70 1.81
Sensitivity analysis: Per share value of Formanis
with different WACC and long-term growth. Year 1-5 growth
rate = 25%
=B23
Varying the year 1-5 growth rate gives different values. In the table below, for example,
we’ve assumed that year 1-5 growth is 20%:
26
27
28
29
30
31
32
ABCDEF
Long-term growth rate ↓
$3.01
0% 2% 4% 6%
WACC →
15%
2.38 2.54 2.75 3.01
20% 2.00 2.13 2.30 2.51
25%
1.70 1.81 1.95 2.12
30%
1.46 1.55 1.66 1.81
Sensitivity analysis: Per share value of Formanis
with different WACC and long-term growth. Year 1-5 growth
rate = 20%
=B23
18.3. Valuation method 3: The price of a share is the present value of its
future anticipated equity cash flows discounted at the cost of equity
In the previous section we “backed into” the equity valuation of the firm, by first
calculating the value of the firm’s assets (the enterprise value plus initial cash balances), and then
subtracting from this number the value of the firm’s debts. In this section we present another
PFE Chapter 18, Stock valuation page 19
method for calculating the value of the firm’s equity—we directly discount the value of the
firm’s anticipated payouts to its shareholders.
As an example consider Haul-It Corp., which has a steady record of paying dividends and
repurchasing shares. The company has 10 million shares outstanding. Here’s a spreadsheet with
the valuation model:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ABCDEFG
1998 1999 2000 2001 2002
Repurchases $1,440,000 $2,410,000 $3,500,000 $6,820,000 $4,830,000
Dividends $3,950,000 $3,997,000 $4,238,000 $4,875,000 $5,100,000
Total cash paid to equity holders $5,390,000 $6,407,000 $7,738,000 $11,695,000 $9,930,000
Compound annual
growth, 1998-2002
16.50% <-- =(F5/B5)^(1/4)-1
Haul-It's cost of equity, r
E
25.00%
V
aluation
Current equity payout $9,930,000 <-- =F5
Anticipated future growth 16.50%
Value of total equity 136,164,862 <-- =B12*(1+B13)/(B9-B13)
Number of shares outstanding 10,000,000
Value per share 13.62 <-- =B15/B16
HAUL-IT CORPORATION--EQUITY PAYOUT
HISTORY AND SHARE VALUATION
Haul-It Corporation--Payouts to Equity Holders
$0
$2,000,000
$4,000,000
$6,000,000
$8,000,000
$10,000,000
$12,000,000
$14,000,000
1998 1999 1999 2000 2000 2001 2001 2002 2002
Repurchases
Dividends
Total cash paid to equity holders
PFE Chapter 18, Stock valuation page 20
Between 1998 and 2002, Haul-It’s payouts to its equity holders have increased at an
impressive rate of 16.50% per year (cell B7). The company’s cost of equity r
E
is 25% (cell B9).
1
Assuming that future equity payout growth equals historical growth, Haul-It is valued at $136
million (cell B15), which gives a per-share value of $13.62.
The equity value of the company is the discounted value of the future anticipated equity
payouts:
() ()
() ()
()
()
()
()
2003 2004 2004
23
23
2002 2002 2002
23
2002
....
1
11
11 1
....
1
11
1 9,930,000 1.16
E
EE
E
EE
E
Equity payout Equity payout Equity payout
Equity value
r
rr
Equity payout g Equity payout g Equity payout g
r
rr
Equity payout g
rg
=+++
+
++
++ +
=+ + +
+
++
+
==
−
()
5
136,164,862
25.00% 16.50%
=
−
Dividing the equity value by the number of shares outstanding gives the estimated value
per share:
136,164,862
13.62
10,000,000
Equity value
Value per share
Shares outstanding
===
Why do finance professionals shun direct equity valuation?
Valuation method 3, the direct valuation of equity is so simple that it may surprise you
that it is rarely used. There are several reasons for this, none of which we can fully explain at
this point in the book:
•
The direct equity valuation method depends on projected equity payouts (that is,
dividends plus share repurchases), whereas Method 3 depends on projected free cash
1
At this point we do not discuss how we arrived at this cost of equity. For a recapitulation of cost of capital
techniques, see sections 18.??? – 18.???.