# 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
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

DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 1368 posted: 3/26/2008 language: English pages: 16
How are you planning on using Docstoc?