10model
10/6/2009 8:15
12/2/2002
Chapter 10.
Model for Capital Budgeting Tools
In this file we use Excel to do most of the calculations explained in Chapter 10.
First, we analyze Projects S and L,
whose cash flows are shown immediately below in both tabular and time line formats. Spreadsheet analyses can be
set up vertically, in a table with columns, or horizontally, using time lines.
For short problems, with just a few years,
we generally use the time line format because rows can be added and we can set the problem up as a series of income
statements. For long problems, it is often more convenient to use a tabular layout.
Expected aftertax
Project S
Year (t)
Project S
Project L
0
1
2
3
4
0
($1,000)
($1,000)
(1,000)
500
400
300
100
1
500
100
2
400
300
Project L
3
300
400
4
100
600
0
1
2
3
4
(1,000)
100
300
400
600
Capital Budgeting Decision Criteria
Here are the five key methods used to evaluate projects:
(1) payback period, (2) discounted payback period, (3) net
present value, (4) internal rate of return, and (5) modified internal rate of return.
Using these criteria, financial
analysts seek to identify those projects that will lead to the maximization of the firm's stock price.
PAYBACK PERIOD
The payback period is defined as the expected number of years required to recover the investment, and it was the
first formal method used to evaluate capital budgeting projects.
First, we identify the year in which the cumulative
cash inflows exceed the initial cash outflows.
That is the payback year.
Then we take the previous year and add to it
the unrecovered balance at the end of that year divided by the following year's cash flow.
Generally speaking, the
shorter the payback period, the better the investment.
Project S
Time period:
0
1
2
3
4
Cash flow:
(1,000)
500
400
300
100
Cumulative cash flow:
(1,000)
(500)
(100)
200
300
0.00
0.00
0.00
1.00
0.00 Use Logical "AND" to determine
0.00
0.00
0.00
2.33
0.00
the first positive cumulative CF.
Payback:
2.33
Use Logical IF to find the Payback.
Use Statistical Max function to
Alternative calculation:
2.33
Alternative: Use nested IF statements to
display payback.
find payback. Fx > Logical > IF > OK, statements.
Project L
Time period:
0
1
2
3
4
Cash flow:
(1,000)
100
300
400
600
Cumulative cash flow:
(1,000)
(900)
(600)
(200)
400
Payback:
3.33
Uses IF statement.
DISCOUNTED PAYBACK PERIOD
Discounted payback period uses the project's cost of capital to discount the expected cash flows. The calculation of
discounted payback period is identical to the calculation of the regular payback period, except you must base the
calculation on a new row of discounted cash flows.
Note that both projects have a cost of capital of 10%.
WACC
=
10%
Project S
Time period:
0
1
2
3
4
Cash flow:
(1,000)
500
400
300
100
Disc. cash flow:
(1,000)
455
331
225
68
Disc. cum. cash flow:
(1,000)
(545)
(215)
11
79
Discounted Payback:
2.95
Uses IF statement.
net cash flows (CF
t
)
A
B
C
D
E
F
G
H
I
J
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Project L
Time period:
0
1
2
3
4
Cash flow:
(1,000)
100
300
400
600
Disc. cash flow:
(1,000)
91
248
301
410
Disc. cum. cash flow:
(1,000)
(909)
(661)
(361)
49
Discounted Payback:
3.88
Uses IF statement.
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '09
 EyupCetin
 Management, Net Present Value

Click to edit the document details