Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 3: Capital budgeting 25
Is Sally and Dave’s condo investment profitable?—incorporating terminal value
into the calculations
A little thought about the previous spreadsheet reveals that we’ve left out an important
factor: The value of the condo at the end of the 10-year horizon. In finance an asset’s value at
the end of the investment horizon is called the asset’s salvage value or terminal value. In the
above spreadsheet we’ve assumed that the terminal value of the condo is zero, but this
assumption implausible.
To make a better calculation about their investment, Sally and Dave will have to make an
assumption about the condo’s terminal value. Suppose they assume that at the end of the 10
years they’ll be able to sell the condo for $80,000. The taxable gain relating to the sale of the
condo is the difference between the condo’s sale price and its book value at the time of sale—the
initial price minus the sum of all the depreciation since Sally and Dave bought it. Since Sally
and Dave have been depreciating the condo by $10,000 per year over a 10-year period, its book
value at the end of 10 years will be zero.
In cell E10 below you can see that the sale of the condo for $80,000 will generate a cash
flow of $56,000:
PFE, Chapter 3: Capital budgeting 26
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ABCDEF
Cost of condo
100,000
Sally & Dave's tax rate
30%
Annual reportable income calculation Terminal value
Rent 24,000
Estimated resale value,
year 10
80,000
Expenses Book value 0
Property taxes -1,500 Taxable gain 80,000 <-- =E6-E7
Miscellaneous expenses -1,000 Taxes 24,000 <-- =B3*E8
Depreciation -10,000
Net after tax--cashflow
from terminal value
56,000 <-- =E8-E9
Reportable income 11,500 <-- =SUM(B6:B10)
Taxes (rate = 30%) -3,450 <-- =-B3*B11
Net income 8,050 <-- =B11+B12
Cash flow, method 1
Add back depreciation
Net income 8,050 <-- =B13
Add back depreciation 10,000 <-- =-B10
Cash flow 18,050 <-- =B17+B16
SALLY & DAVE'S CONDO: PROFITABILITY
AND TERMINAL VALUE
To compute the rate of return of Sally and Dave’s condo investment, we put all the
numbers together:
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
ABCD
Discount rate 12%
Year Cashflow
0 -100,000
1 18,050 <-- =B18, Annual cashflow from rental
218,050
318,050
418,050
518,050
618,050
718,050
818,050
918,050
10 74,050 <-- =B32+E10
NPV of condo investment 20,017 <-- =B23+NPV(B20,B24:B33)
IRR of investment 15.98% <-- =IRR(B23:B33)
Assuming that the 12% discount rate is the correct rate, the condo investment is
worthwhile: It’s NPV is positive and its IRR exceeds the discount rate.
5
5
When we say that a discount rate is “correct,” we usually mean that it is appropriate to the riskiness of the cash
flows being discounted. In Chapter 5 we have our first discussion in this book on how to determine a correct
PFE, Chapter 3: Capital budgeting 27
Book value versus terminal value
The book value of an asset is its initial purchase price minus the accumulated
depreciation. The terminal value of an asset is its assumed market value at the time you “stop
writing down the asset’s cash flows.” This sounds like a weird definition of terminal value, but
often when we do present value calculations for a long-lived asset (like Sally and Dave’s condo,
or like the company valuations we discuss in Chapters 7-9), we write down only a limited
number of cash flows.
Sally and Dave are reluctant to make predictions about condo rents and expenses beyond
a ten-year horizon. Past this point, they’re worried about the accuracy of their guesses. So they
write down ten years of cash flows; the terminal value is their best guess of the condo’s value at
the end of year 10. Their thinking is “let’s examine the profitability of the condo if we hold on
to it for 10 years and sell it.”
This is what we mean when we say that “the terminal value is what the asset is worth
when we stop writing down the cash flows.”
Taxes: If Sally and Dave are right in their terminal value assumption, they will have to
take account of taxes. The tax rules for selling an asset specify that the tax bill is computed on
the gain over the book value. So, in the example of Sally and Dave:
()
()
*
80,000 30%* 80,000 0 56, 000
Terminal value taxes on gain over book
Terminal value-tax rate Terminal value-book value
−
=
=− −=
discount rate. For the moment let’s assume that the discount rate is appropriate to the riskiness of the condo’s cash
flows.
PFE, Chapter 3: Capital budgeting 28
Doing some sensitivity analysis
If we really want to be fancy, we can do a sensitivity table (using Excel’s Data Table,
see Chapter 30). The table below shows the IRR of the investment as a function of the annual
rent and the terminal value:
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
ABCDEFGH
Data table--Condo IRR as function of annual rent and terminal value
Rent
15.98%
18,000 20,000 22,000 24,000 26,000 28,000
Terminal value --> 50,000
9.72% 11.45% 13.15% 14.82% 16.47% 18.10%
60,000
10.26% 11.93% 13.59% 15.22% 16.84% 18.44%
70,000
10.77% 12.40% 14.01% 15.61% 17.19% 18.76%
80,000
11.25% 12.84% 14.42% 15.98% 17.54% 19.08%
90,000
11.71% 13.27% 14.81% 16.34% 17.87% 19.38%
100,000
12.15% 13.67% 15.19% 16.69% 18.19% 19.68%
110,000
12.58% 14.06% 15.55% 17.02% 18.50% 19.96%
120,000 12.98% 14.44% 15.90% 17.35% 18.80% 20.24%
130,000 13.37% 14.80% 16.23% 17.66% 19.09% 20.51%
140,000 13.75% 15.15% 16.56% 17.96% 19.37% 20.78%
150,000 14.11% 15.49% 16.87% 18.26% 19.65% 21.03%
160,000 14.46% 15.82% 17.18% 18.55% 19.91% 21.28%
Note: The data table above computes the IRR of the condo investment for combinations of rent (from $18,000 to
$26,000 per year) and terminal value (from $50,000 to $160,000).
Data tables are very useful though not trivial to compute. See Chapter 30 for more information.
=B36
The calculations aren’t that surprising: For a given rent, the IRR is higher when the
terminal value is higher, and for a given terminal value, the IRR is higher given a higher rent.
Building the data table
6
Here’s how the data table was set up:
•
We build a table with terminal values in the left-hand column and rent in the top row.
•
In the top left-hand corner of the table (cell B40), we refer to the IRR calculation in the
spreadsheet example (this calculation occurs in cell B36).
At this point the table looks like this:
6
This subsection doesn’t replace Chapter 30, but it may help you recall what we said there.
PFE, Chapter 3: Capital budgeting 29
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
ABCDEFGH
Data table--Condo IRR as function of annual rent and terminal value
Rent
15.98%
18,000 20,000 22,000 24,000 26,000 28,000
Terminal value --> 50,000
60,000
70,000
80,000
90,000
100,000
110,000
120,000
130,000
140,000
150,000
160,000
=B36
Using the mouse we now mark the whole table. We use the
Data|Table command and
fill in the cell references from the original example:
PFE, Chapter 3: Capital budgeting 30
The dialog box tells Excel to repeat the calculation in cell B36, varying the rent number
in cell B6 and varying the terminal value number in cell E6. Pressing
OK does the rest.
Mini case
A mini case for this chapter looks at Sally and Dave’s condo once more—this time under
the assumption that they take out a mortgage to buy the condo. Highly recommended!
3.8. Capital budgeting and salvage values
In the Sally-Dave condo example we’ve focused on the effect of non-cash expenses on
cash flows: Accountants and the tax authorities compute earnings by subtracting certain kinds of
expenses from sales, even though these expenses are non-cash expenses. In order to compute the
cash flow, we add back these non-cash expenses to accounting earnings. We showed that these
non-cash expenses create tax shields—they create cash by saving taxes.
In this section’s example we consider a capital budgeting example in which a firm sells
its asset before it is fully depreciated. We show that the asset’s book value at the date of the
terminal value creates a tax shield and we look at the effect of this tax shield on the capital
budgeting decision.
Here’s the example. Your firm is considering buying a new machine. Here are the facts:
•
The machine costs $800.
•
Over the next 8 years (the life of the machine) the machine will generate annual sales of
$1,000.
PFE, Chapter 3: Capital budgeting 31
• The annual cost of the goods sold (COGS) is $400 per year and other costs; selling,
general, and administrative expenses (GS&A) are $300 per year.
•
Depreciation on the machine is straight-line over 8 years (that is: $100 per year).
•
At the end of 8 years, the machine’s salvage value (or terminal value) zero.
•
The firm’s tax rate is 40%.
•
The firm’s discount rate for projects of this kind is 15%.
Should the firm buy the machine? Here’s the analysis in Excel:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
ABCDEFG
Cost of the machine 800
Annual anticipated sales 1,000
Annual COGS 400
Annual SG&A 300 NPV Analysis
Annual depreciation 100 Year Cash flow
0 -800 <-- =-B2
Tax rate 40% 1 220 <-- =$B$23
Discount rate 15% 2 220
3 220
Annual profit and loss (P&L) 4 220
Sales 1,000 5 220
Minus COGS -400 6 220
Minus SG&A -300 7 220
Minus depreciation -100 8 220
Profit before taxes 200 <-- =SUM(B12:B15)
Subtract taxes -80 <-- =-B8*B16 NPV 187 <-- =F7+NPV(B9,F8:F15)
Profit after taxes 120 <-- =B16+B17
Calculating the annual cash flow
Profit after taxes 120
Add back depreciation 100
Cash flow 220
BUYING A MACHINE--NPV ANALYSIS
Notice that we first calculate the profit and loss (P&L) statement for the machine (cells
B12:B18) and then turn this P&L into a cash flow calculation (cells B21:B23). The annual cash
flow is $220. Cells F7:F15 show the table of cash flows, and cell F17 gives the NPV of the
project. The NPV is positive, and we would therefore buy the machine.
PFE, Chapter 3: Capital budgeting 32
Salvage value—a variation on the theme
Suppose the firm can sell the machine for $300 at the end of year 8. To compute the cash
flow produced by this salvage value, we must make the distinction between book value and
market value:
Book value An accounting concept: The book value of the machine is its initial cost
minus the accumulated depreciation (the sum of the depreciation taken on
the machine since its purchase). In our example, the book value of the
machine in year 0 is $800, in year 1 it is $700, ..., and at the end of year 8 it
is zero.
Market value The market value is the price at which the machine can be sold. In our
example the market value of the machine at the end of year 8 is $300.
Taxable gain The taxable gain on the machine at the time of sale is the difference
between the market value and the book value. In our case the taxable gain
is positive ($300), but it can also be negative (see an example at the end of
this chapter).
Here’s the NPV calculation including the salvage value:
PFE, Chapter 3: Capital budgeting 33
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
ABCDEFG
Cost of the machine 800
Annual anticipated sales 1,000
Annual COGS 400
Annual SG&A 300
NPV Analysis
Annual depreciation 100 Year Cash flow
0 -800 <-- =-B2
Tax rate 40% 1 220 <-- =$B$23
Discount rate 15% 2 220
3 220
Annual profit and loss (P&L)
4 220
Sales 1,000 5 220
Minus COGS -400 6 220
Minus SG&A -300 7 220
Minus depreciation -100 8 400 <-- =$B$23+B30
Profit before taxes 200 <-- =SUM(B12:B15)
Subtract taxes -80 <-- =-B8*B16 NPV 246 <-- =F7+NPV(B9,F8:F15)
Profit after taxes 120 <-- =B16+B17
Calculating the annual cash flow
Profit after taxes 120
Add back depreciation 100
Cash flow 220
Calculating the cash flow from salvage value
Machine market value, year 8 300
Book value, year 8 0
Taxable gain 300 <-- =B26-B27
Taxes paid on gain 120 <-- =B8*B28
Cash flow from salvage value 180 <-- =B26-B29
BUYING A MACHINE--NPV ANALYSIS
with salvage value
Note the calculation of the cash flow from the salvage value (cell B30) and the change in
the year 8 cash flow (cell F15).
One more example
Suppose we change the example slightly:
•
The annual sales, SG&A, COGS, and depreciation are still as specified in the original
example. The machine will still be depreciated on a straight-line basis over 8 years.
•
However, we think we will sell the machine at the end of year 7 at an estimated salvage
value of $400. At the end of year 7 the book value of the machine is $100.
Here’s how our calculations look now:
PFE, Chapter 3: Capital budgeting 34
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
ABCDEFG
Cost of the machine 800
Annual anticipated sales 1,000
Annual COGS 400
Annual SG&A 300
NPV Analysis
Annual depreciation 100 Year Cash flow
0 -800 <-- =-B2
Tax rate 40% 1 220 <-- =$B$23
Discount rate 15% 2 220
3 220
Annual profit and loss (P&L)
4 220
Sales 1,000 5 220
Minus COGS -400 6 220
Minus SG&A -300 7 500 <-- =$B$23+B30
Minus depreciation -100
Profit before taxes 200 <-- =SUM(B12:B15) NPV 221 <-- =F7+NPV(B9,F8:F15)
Subtract taxes -80 <-- =-B8*B16
Profit after taxes 120 <-- =B16+B17
Calculating the annual cash flow
Profit after taxes 120
Add back depreciation 100
Cash flow 220
Calculating the cash flow from salvage value
Machine market value, year 7 400
Book value, year 7 100
Taxable gain 300 <-- =B26-B27
Taxes paid on gain 120 <-- =B8*B28
Cash flow from salvage value 280 <-- =B26-B29
BUYING A MACHINE--NPV ANALYSIS
with salvage value. Machine sold in year 7
Note the subtle changes from the previous example:
•
The cash flow from salvage value is
(
)
Taxable gain at time
of machine sale
*Salvage value tax Salvage value Book value
↑
−−
In our example this is $280 (cell B30).
•
Another way to write the cash flow from the salvage value is:
(
)
Tax shield on book
After-tax proceeds from machine
value at time of machine
sale the whole salvage value is
sale
taxed
*1 *
if
Salvage value tax tax book value
↑
↑
−+