Get documents about "capital budgeting model
Document Sample
A B C D E F G H I J K
1 5/11/2004
2
3 Chapter 10. Tool Kit for The Basics of Capital Budgeting: Evaluating Cash Flows
4
5 In this file we use Excel to do most of the calculations explained in Chapter 10. First, we analyze Projects S and L,
6 whose cash flows are shown immediately below in both tabular and a time line formats. Spreadsheet analyses can be
7 set up vertically, in a table with columns, or horizontally, using time lines. For short problems, with just a few years,
8 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.
9
10
11 Expected after-tax Project S
12 net cash flows (CFt)
13 Year (t) Project S Project L 0 1 2 3 4
14 0 ($1,000) ($1,000) (1,000) 500 400 300 100
15 1 500 100
16 2 400 300 Project L
17 3 300 400
18 4 100 600 0 1 2 3 4
19 (1,000) 100 300 400 600
20
21
22 Capital Budgeting Decision Criteria
23
24 Here are the five key methods used to evaluate projects: (1) payback period, (2) discounted payback period, (3) net
25 present value, (4) internal rate of return, and (5) modified internal rate of return. Using these criteria, financial
26 'analysts seek to identify those projects that will lead to the maximization of the firm's stock price.
27
28 Payback Period
29 The payback period is defined as the expected number of years required to recover the investment, and it was the
30 first formal method used to evaluate capital budgeting projects. First, we identify the year in which the cumulative
31 cash inflows exceed the initial cash outflows. That is the payback year. Then we take the previous year and add to it
32 unrecovered balance at the end of that year divided by the following year's cash flow. Generally speaking, the
33 shorter the payback period, the better the investment.
34
35 Project S
36 Time period: 0 1 2 3 4
37 Cash flow: (1,000) 500 400 300 100
38 Cumulative cash flow: (1,000) (500) (100) 200 300 Click fx > Logical > AND > OK to ge
39 FALSE FALSE FALSE TRUE FALSE Use Logical "AND" to determine Then specify you want TRUE if cumu
40 0.00 0.00 0.00 2.33 0.00 the first positive cumulative CF. There will be one TRUE.
41 Payback: 2.33 Use Logical IF to find the Payback.Click fx > Logical > IF > OK. Specif
42 Use Statistical Max function to Click fx > Statistical > MAX > OK >
43 Alternative calculation: 2.33 display payback.
44
45 Project L
46 Time period: 0 1 2 3 4
47 Cash flow: (1,000) 100 300 400 600
48 Cumulative cash flow: (1,000) (900) (600) (200) 400
49
50 Payback: 3.33 Uses IF statement.
51
A B C D E F G H I J K
52 Discounted Payback Period
53 Discounted payback period uses the project's cost of capital to discount the expected cash flows. The calculation of
54 discounted payback period is identical to the calculation of regular payback period, except you must base the
55 calculation on a new row of discounted cash flows. Note that both projects have a cost of capital of 10%.
56
57 WACC = 10%
58
59 Project S
60 Time period: 0 1 2 3 4
61 Cash flow: (1,000) 500 400 300 100
62 Disc. cash flow: (1,000) 455 331 225 68 Cash Flows Discounted back at 10%.
63 Disc. cum. cash flow: (1,000) (545) (215) 11 79
64
65 Discounted Payback: 2.95 Uses IF statement.
66
67 Project L
68 Time period: 0 1 2 3 4
69 Cash flow: (1,000) 100 300 400 600
70 Disc. cash flow: (1,000) 91 248 301 410
71 Disc. cum. cash flow: (1,000) (909) (661) (361) 49
72
73 Discounted Payback: 3.88 Uses IF statement.
74
75 The inherent problem with both paybacks is that they ignore cash flows that occur after the payback period mark.
76 While the discounted method accounts for timing issues (to some extent), it still falls short of fully analyzing projects.
77 However, all else equal, these two methods do provide some information about projects' liquidity and risk.
78
79 Net Present Value (NPV)
80 To calculate the NPV, we find the present value of the individual cash flows and find the sum of those discounted
81 cash flows. This value represents the value the project add to shareholder wealth.
82
83 WACC = 10%
84
85 Project S
86 Time period: 0 1 2 3 4
87 Cash flow: (1,000) 500 400 300 100
88 Disc. cash flow: (1,000) 455 331 225 68
89
90 NPV(S) = $78.82 = Sum disc. CF's. or $78.82 = Uses NPV function.
91
92 Project L
93 Time period: 0 1 2 3 4
94 Cash flow: (1,000) 100 300 400 600
95 Disc. cash flow: (1,000) 91 248 301 410
96
97 NPV(L) = $49.18 $ 49.18 = Uses NPV function.
98
A B C D E F G H I J K
99
The NPV method of capital budgeting dictates that all independent projects that have positive NPV should accepted.
100
The rationale behind that assertion arises from the idea that all such projects add wealth, and that should be the
101
overall goal of the manager in all respects. If strictly using the NPV method to evaluate two mutually exclusive
102
projects, you would want to accept the project that adds the most value (i.e. the project with the higher NPV).
103
Hence, if considering the above two projects, you would accept both projects if they are independent, and you would
104
only accept Project S if they are mutually exclusive.
105
106
107 Internal Rate of Return (IRR)
108
The internal rate of return is defined as the discount rate that equates the present value of a project's cash inflows to
109
its outflows. In other words, the internal rate of return is the interest rate that forces NPV to zero. The calculation
110
for IRR can be tedious, but Excel provides an IRR function that merely requires you to access the function and enter
111
the array of cash flows. The IRR's for Project S and L are shown below, along with the data entry for Project S.
112
113
114 Expected after-tax
115 net cash flows (CFt)
116 Year (t) Project S Project L
117 0 ($1,000) ($1,000) The IRR function assumes
118 1 500 100 IRR S = 14.49% payments occur at end of
119 2 400 300 IRR L = 11.79% periods, so that function does
120 3 300 400 not have to be adjusted.
121 4 100 600
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137 The IRR method of capital budgeting maintains that projects should be accepted if their IRR is greater than the cost
138 of capital. Strict adherence to the IRR method would further dictate that mutually exclusive projects should be
139 chosen on the basis of the greatest IRR. In this scenario, both projects have IRR's that exceed the cost of capital
140 (10%) and both should be accepted, if they are independent. If, however, the projects are mutually exclusive, we
141 would chose Project S. Recall, that this was our determination using the NPV method as well. The question that
142 naturally arises is whether or not the NPV and IRR methods will always agree.
143
144 When dealing with independent projects, the NPV and IRR methods will always yield the same accept/reject result.
145 'However, in the case of mutually exclusive projects, NPV and IRR can give conflicting results. One shortcoming of
146 the internal rate of return is that it assumes that cash flows received are reinvested at the project's internal rate of
147 return, which is not usually true. The nature of the congruence of the NPV and IRR methods is further detailed in a
148 latter section of this model.
149
A B C D E F G H I J K
150 Multiple IRR's
151 Because of the mathematics involved, it is possible for some (but not all) projects that have more than one change of signs in the
152 set of cash flows to have more than one IRR. If you attempted to find the IRR with such a project using a financial calculator,
153 you would get an error message. The HP-10B says "Error - Soln", the HP-17B says '"Many/No Solutions, and the HP12C says
154 Error 3; Key in Guess" when such a project is evaluated. The procedure for correcting the problem isto store in a guess for the
155 IRR, and then the calculator will report the IRR that is closest to your guess. You can then use a different "guess" value, and
156 you should be able to find the other IRR. However, the nature of the mathematics creates a scenario in which one IRR is quite
157 extraordinary (often, a few hundred percent).
158 Consider the case of Project M.
159
160 Project M: 0 1 2
161 (1.6) 10 (10)
162
163 We will solve this IRR twice, the first time using the default guess of 10%, and the second time we will enter a guess
164 of 300%. Notice, that the first IRR calculation is exactly as it was above.
165
1
166 IRR M = 25.0%
167
168
169
170
171 IRR M 2 = 400%
172
173
174
175
176
177
178
179
180
181 The two solutions to this problem tell us that this project will have a positive NPV for all costs of capital between
182 '25% and 400%. We illustrate this point by creating a data table and a graph of the project NPVs.
183
184 Project M: 0 1 2
185 (1.6) 10 (10)
186 r = 25.0%
187 NPV = 0.00
188
A B C D E F G H I J K
189 NPV
190 r $0.0
191 0% (1.60)
192 25% 0.00
193 50% 0.62
194 75% 0.85
195 100% 0.90 Max.
196 125% 0.87
197 150% 0.80
198 175% 0.71
199 200% 0.62
200 225% 0.53
201 250% 0.44
202 275% 0.36
203 300% 0.28
204 325% 0.20
205 350% 0.13
206 375% 0.06
207 400% 0.00
208 425% (0.06)
209 450% (0.11)
210 475% (0.16)
211 500% (0.21)
212 525% (0.26)
213 550% (0.30)
214
215 NPV Profiles
216 NPV profiles graph the relationship between projects' NPVs and the cost of capital. To create NPV profiles for
217 Projects S and L, we create data tables of NPV at different costs of capital.
218
219 Net Cash Flows
220 Year Project S Project L WACC = 10.0%
221 0 -$1,000 -$1,000 Project S Project L
222 1 $500 $100 NPV = $78.82 $49.18
223 2 $400 $300 IRR = 14.49% 11.79%
224 3 $300 $400 Crossover = 7.17%
225 4 $100 $600
226 Data Table used to make graph:
227
228
229 WACC
230 0%
231 5%
232 7.17%
233 10%
234 11.79%
235 14.49%
236 15.0%
237 20%
238 25%
239
240
241
242
243
244
245 Points about the graphs:
246 1. In Panel a, we see that if WACC < IRR, then NPV > 0, and vice versa.
247 2. Thus, for "normal and independent" projects, there can be no conflict between NPV and IRR rankings.
248 3. However, if we have mutually exclusive projects, conflicts can occur. In Panel b, we see that IRRS is
249 always greater than IRRL, but if WACC < 11.56%, then IRRL > IRRS, in which case a conflict occurs.
250 4. Summary: a. For normal, independent projects, conflicts can never occur, so either method can be used.
A B C D E F G H I J K
251 b. For mutually exclusive projects, if WACC > Crossover, no conflict, but if WACC < Crossover,
252 then there will be a conflict between NPV and IRR.
253
A B C D E F G H I J K
254
255 Previously, we had discussed that in some instances the NPV and IRR methods can give conflicting results. First, we
256 should attempt to define what we see in this graph. Notice, that the two project profiles (S and L) intersect the x-axis
257 at costs of capital of 14% and 12%, respectively. Not coincidently, those are the IRR's of the projects. If we think
258 about the definition of IRR, we remember that the internal rate of return is the cost of capital at which a project will
259 have an NPV of zero. Looking at our graph, it is a logical conclusion that the IRR of a project is defined as the point
260 at which its profile intersects the x-axis.
261
262 Looking further at the NPV profiles, we see that the two project profiles intersect at a point we shall call the
263 crossover point. We observe that at costs of capital greater than the crossover point, the project with the greater IRR
264 (Project S, in this case) also has the greater NPV. But at costs of capital less than the crossover point, the project
265 with the lesser IRR has the greater NPV. This relationship is the source of discrepancy between the NPV and IRR
266 methods. By looking at the graph, we see that the crossover appears to occur at approximately 7%. Luckily, there is
267 a more precise way of determining crossover. To find crossover, we will find the difference between the two projects
268 cash flows in each year, and then find the IRR of this series of differential cash flows.
269
270 Expected after-tax
271 net cash flows (CFt) Cash flow Alternative: Use Tools > Goal Seek to find WACC when NPV(S) =
272 Year (t) Project S Project L differential NPV(L). Set up a table to show the difference in NPV's, which we
273 0 ($1,000) ($1,000) 0 want to be zero. The following will do it, getting WACC = 7.17%.
274 1 500 100 400 Look at B57 for the answer, then restore B57 to 10%.
275 2 400 300 100 NPV S = $ 78.82
276 3 300 400 (100) NPV L = $ 49.18
277 4 100 600 (500) S - L = $ 29.64
278
279
280
281 IRR = Crossover rate = 7.17%
282
283
284
285
286
287 The intuition behind the relationship between the NPV profile and the crossover rate is as follows: (1) Distant cash
288 flows are heavily penalized by high discount rates--the denominator is (1+r)t, and it increases geometrically, hence
289 gets very large at high values of t. (2) Long-term projects like L have most of their cash flows coming in the later
290 years, when the discount penalty is largest, hence they are most severely impacted by high capital costs. (3)
291 'Therefore, Project L's NPV profile is steeper than that of S. (4) Since the two profiles have different slopes, they
292 cross one another.
293
294 Modified Internal Rate of Return (MIRR)
295 The modified internal rate of return is the discount rate that causes a project's cost (or cash outflows) to equal the
296 'present value of the project's terminal value. The terminal value is defined as the sum of the future values of the
297 'project's cash inflows, compounded at the project's cost of capital. To find MIRR, calculate the PV of the outflows
298 'and the FV of the inflows, and then find the rate that equates the two. Or, you can solve using the MIRR function.
299
A B C D E F G H I J K
300 WACC = 10% MIRRS = 12.11%
301 Project S MIRRL = 11.33%
302 10%
303 0 1 2 3 4
304 (1,000) 500 400 300 100
305
306 Project L
307
308 0 1 2 3 4
309 (1,000) 100 300 400 600
310 440.0
311 363.0
312 133.1
313 PV: (1,000) Terminal Value: 1,536.1
314
315 The advantage of using the MIRR, relative to the IRR, is that the MIRR assumes that cash flows received are
316 reinvested at the cost of capital, not the IRR. Since reinvestment at the cost of capital is more likely, the MIRR is a
317 'better indicator of a project's profitability. Moreover, it solves the multiple IRR problem, as a set of cash flows can
318 have but one MIRR .
319
320 Note that if negative cash flows occur in years beyond Year 1, those cash flows would be discounted at the cost of
321 capital and added to the Year 0 cost to find the total PV of costs. If both positive and negative flows occurred in
322 some year, the negative flow should be discounted, and the positive one compounded, rather than just dealing with
323 the net cash flow. This makes a difference.
324
325 Also note that Excel's MIRR function allows for discounting and reinvestment to occur at different rates. Generally,
326 MIRR is defined as reinvestment at the WACC, though Excel allows the calculation of a special MIRR where
327 reinvestment occurs at a different rate than WACC.
328
329
Finally, it is stated in the text, when the IRR versus the NPV is discussed, that the NPV is superior because (1) the
330
NPV assumes that cash flows are reinvested at the cost of capital whereas the IRR assumes reinvestment at the IRR,
331
and (2) it is more likely, in a competitive world, that the actual reinvestment rate is more likely to be the cost of
332
capital than the IRR, especially if the IRR is quite high. The MIRR setup can be used to prove that NPV indeed does
333
assume reinvestment at the WACC, and IRR at the IRR.
334
A B C D E F G H I J K
335
336 Project S
337 WACC = 10%
338 0 1 2 3 4
339 (1,000) 500 400 300 100
340 330.0
341 484.0 Reinvestment at WACC = 10%
342 665.5
343 PV outflows -$1,000.00 Terminal Value: 1,579.5
344 PV of TV $1,078.82
345 NPV $ 78.82 Thus, we see that the NPV is consistent with reinvestment at WACC.
346
347
348 Now repeat the process using the IRR, which is G118 as the discount rate.
349
350 Project S
351 IRR = 14.49%
352 0 1 2 3 4
353 (1,000) 500 400 300 100
354 343.5
355 524.3 Reinvestment at IRR = 14.49%
356 750.3
357 PV outflows -$1,000.00 Terminal Value: 1,718.1
358 PV of TV $1,000.00
359 NPV $0.00 Thus, if compounding is at the IRR, NPV is zero. Since the
360 definition of IRR is the rate at which NPV = 0, this demonstrates
361 that the IRR assumes reinvestment at the IRR.
362
363 Profitability Index (PI)
364 The profitability index is the present value of all future cash flows divided by the intial cost. It measures
365 the PV per dollar of investment.
366
367 For project S:
368 PI(S) = PV of future cash flows ÷ Initial cost
369 PI(S) = $ 1,078.82 ÷ $ 1,000.00
370 PI(S) = 1.079
371
372 For project L:
373 PI(L) = PV of future cash flows ÷ Initial cost
374 PI(L) = $ 1,049.18 ÷ $ 1,000.00
375 PI(L) = 1.049
376
377
378 PROJECTS WITH UNEQUAL LIVES
379
380 If two mutually exclusive projects have different lives, and if the projects can be repeated, then it is necessary to deal explicitly
381 with those unequal lives. We use the replacement chain (or common life) approach. This procedure compares projects of
382 unequal lives by equalizing their lives by assuming that each project can be repeated as many times as necessary to reach a
383 common life span. The NPVs over this life span are then compared, and the project with the higher common life NPV is chosen.
384 To illustrate, suppose a firm is considering two mutually exclusive projects, either a conveyor system (Project C) or a fleet of
385 forklift trucks (Project F) for moving materials. The firm's cost of capital is 12%. The cash flow timelines are shown below,
386 'along with the NPV and IRR for each project.
387
388 Project C WACC: 11.5%
389 End of Period:
390
391 0 1 2 3 4 5 6
A B C D E F G H I J K
392 ($40,000) $8,000 $14,000 $13,000 $12,000 $11,000 $10,000
393
394 NPV $7,165
395 IRR 17.5%
396
397 Project F
398 End of Period:
399
400 0 1 2 3
401 ($20,000) $7,000 $13,000 $12,000
402
403 NPV $5,391
404 IRR 25.2%
405
406 Initially, it would appear that Project C is the better investment, based upon its higher NPV. However, if the firm chooses
407 Project F, it would have the opportunity to make the same investment three years from now. Therefore, we must reevaluate
408 Project F 'using extended common life of 6 years. The time lines are shown below. Note that only F's is changed.
409
410 Common Life Approach
411
412 Project C
413 End of Period:
414
415 0 1 2 3 4 5 6
416 ($40,000) $8,000 $14,000 $13,000 $12,000 $11,000 $10,000
417
418 NPV $7,165
419 IRR 17.5%
420
421 Project F
422
423 0 1 2 3 4 5 6
424 ($20,000) $7,000 $13,000 $12,000
425 ($20,000) $7,000 $13,000 $12,000
426 ($20,000) $7,000 $13,000 ($8,000) $7,000 $13,000 $12,000
427
428 NPV $9,281
429 IRR 25.2%
430
431 On the basis of this extended analysis, it is clear that Project F is the better of the two investments (with both the
432 NPV and IRR methods).
433
434 Equivalent Annual Annuity (EAA) Approach (See the Chapter 10 Web Extension for details.)
435
436 Here are the steps in the EAA approach.
437 1. Find the NPV of each project over its initial life (we already did this in our previous analysis).
438 NPVC= 7,165
439 NPVF= 5,391
440
441 2. Convert the NPV into an annuity payment with a life equal to the life of the project.
442 EEAC= 1,718 Note: we used the Function Wizard for the PMT function.
443 EEAF= 2,225
444
445 Project F has a higher EEA, so it is a better project.
446
447 ECONOMIC LIFE VS. PHYSICAL LIFE
448
449 Sometimes an asset has a physical life that is greater than its economic life. Consider the following asset
450 which has a physical life of three years. During its life, the asset will generate operating cash flows.
451 However, the project could be terminated and the asset sold at the end of any year. The following table
452 shows the operating cash flows and the salvage value for each year-- all values are shown on an after-tax
453 basis.
A B C D E F G H I J K
454
Operating Salvage
455 Year Cash Flow Value
456 0 ($4,800) $4,800
457 1 $2,000 $3,000
458 2 $2,000 $1,650
459 3 $1,750 $0
460
461 The cost of capital is 10%. If the asset is operated for the entire three years of its life, its NPV is:
462
PV of PV of
3-Year NPV = Intial Cost + Operating + Salvage
463 Cash Flow Value
464 = ($4,800.00) + $4,785.88 + $0.00
465 3-Year NPV = ($14.12)
466
467 The asset has a negative NPV if it is kept for three years. But even though the asset will last three years,
468 it might be better to operate the asset for either one or two years, and then salvage it.
469
PV of PV of
2-Year NPV = Intial Cost + Operating + Salvage
470 Cash Flow Value
471 = ($4,800.00) + $3,471.07 + $1,363.64
472 2-Year NPV = $34.71
473
PV of PV of
1-Year NPV = Intial Cost + Operating + Salvage
474 Cash Flow Value
475 = ($4,800.00) + $1,818.18 + $2,727.27
476 1-Year NPV = ($254.55)
L M N O P Q R S T
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
Click fx38Logical > AND > OK to get dialog box.
>
39
Then specify you want TRUE if cumulative CF > 0 but the previous CF < 0.
40
There will be one TRUE.
41
Click fx > Logical > IF > OK. Specify that if true, the payback is the previous year plus a fraction, if false, then 0.
Click fx42Statistical > MAX > OK > and specify range to find Payback.
>
43
44
45
46
47
48
49
50
51
L M N O P Q R S T
52
53
54
55
56
57
58
59
60
61
iscounted back at62
10%.
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86 Notice that the NPV function isn't really a Net present value.
87 Instead, it is the present value of future cash flows. Thus, you specify
88 only the future cash flows in the NPV function. To find the true
89 NPV, you must add the time zero cash flow to the result of the NPV
90 function.
91
92
93
94
95
96
97
98
L M N O P Q R S T
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
L M N O P Q R S T
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
L M N O P Q R S T
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
Data Table used to make graph:
227 Project NPVs
228 S L
229 $78.82 $49.18
230 $300.00 $400.00
231 $180.42 $206.50
232 $134.40 $134.40
233 $78.82 $49.18
234 $46.10 $0.00
235 $0.00 -$68.02
236 -$8.33 -$80.14
237 -$83.72 -$187.50
238 -$149.44 -$277.44
239
240
241
242
243
244
245
246
247
248
249
250
Related docs
