Calculation of Npv Irr Pi Projects - Excel by fsm71579

VIEWS: 480 PAGES: 32

More Info
									           A            B          C           D           E           F           G          H           I            J        K
1                                                                                                                  12/24/2007
2
3                  Chapter 10. Tool Kit for Basics of Capital Budgeting: Evaluating Cash Flows
4
5    In this file we use Excel to do most of the calculations explained in the textbook. 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 set
7    up vertically, in a table with columns, or horizontally, using time lines. For short problems, with just a few years, we
8    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
12                               net cash flows (CFt)
13                   Year (t)   Project S Project L
14                     0        ($1,000)     ($1,000)
15                     1           500         100
16                     2           400         300
17                     3           300         400
18                     4           100         600
19
20 Figure 10-1: Net Cash Flows and Selected Evaluation Criteria for
21 Projects S and L (CFt)
22
23   Panel A: Project Cash Flows and Cost of Capital
24
25    Project S:       0           1           2           3           4
26                      |           |           |           |           |
27                  -$1,000      $500        $400        $300        $100
28
29    Project L:       0           1           2           3           4
30                      |           |           |           |           |
31                  -$1,000      $100        $300        $400        $600
32
33         Project cost of capital = r =     10%
34
35   Panel B: Summary of Selected Evaluation Criteria
36
37                                    Project
38                                 S          L
39                  NPV:        $78.82     $49.18
40                  IRR:        14.5%      11.8%
41                  MIRR:       12.1%      11.3%
42                  PI:          1.08       1.05
43
44
45
46
47
48   NET PRESENT VALUE (NPV) (Section 10.2)
49
50   To calculate the NPV, we find the present value of the individual cash flows and find the sum of those discounted cash
51   flows. This value represents the value the project add to shareholder wealth.
52
53   r =              10%
54
55   Project S
56               Time period:       0          1           2           3          4
57                 Cash flow:    (1,000)      500         400         300        100
         A             B           C        D        E         F         G         H          I   J   K
58           Disc. cash flow:   (1,000)    455      331       225       68
59
60 NPV(S) =           $78.82    = Sum disc. CF's.   or    $78.82       = Uses NPV function.
61
62 Project L
63             Time period:        0        1        2         3         4
64                Cash flow:    (1,000)    100      300       400       600
65           Disc. cash flow:   (1,000)     91      248       301       410
66
67 NPV(L) =           $49.18                              $    49.18   = Uses NPV function.
68
            A            B           C            D           E            F           G           H           I             J          K
 69
      The NPV method of capital budgeting dictates that all independent projects that have positive NPV should accepted.
 70
      The rationale behind that assertion arises from the idea that all such projects add wealth, and that should be the overall
 71
      goal of the manager in all respects. If strictly using the NPV method to evaluate two mutually exclusive projects, you
 72
      would want to accept the project that adds the most value (i.e. the project with the higher NPV). Hence, if considering
 73
      the above two projects, you would accept both projects if they are independent, and you would only accept Project S if
 74
      they are mutually exclusive.
 75
 76
 77
 78   INTERNAL RATE OF RETURN (IRR) (Section 10.2)
 79
 80
      The internal rate of return is defined as the discount rate that equates the present value of a project's cash inflows to its
 81
      outflows. In other words, the internal rate of return is the interest rate that forces NPV to zero. The calculation for
 82
      IRR can be tedious, but Excel provides an IRR function that merely requires you to access the function and enter the
 83
      array of cash flows. The IRR's for Project S and L are shown below, along with the data entry for Project S.
 84
 85
 86                                Expected after-tax
 87                               net cash flows (CFt)
 88                   Year (t)   Project S Project L
 89                     0        ($1,000)     ($1,000)                                         The IRR function assumes
 90                     1           500         100                   IRR S =        14.49%    payments occur at end of
 91                     2           400         300                   IRR L =        11.79%    periods, so that function does
 92                     3           300         400                                            not have to be adjusted.
 93                     4           100         600
 94
 95
 96
                                                                                                           Notice that for IRR you must
 97                                                                                                        specify all cash flows, including
 98                                                                                                        the time zero cash flow. This
 99                                                                                                        is in contrast to the NPV
100                                                                                                        function, in which you specify
101                                                                                                        only the future cash flows.
102
103
104
105
106
107
108
109   The IRR method of capital budgeting maintains that projects should be accepted if their IRR is greater than the cost of
110   capital. Strict adherence to the IRR method would further dictate that mutually exclusive projects should be chosen on
111   the basis of the greatest IRR. In this scenario, both projects have IRR's that exceed the cost of capital (10%) and both
112   should be accepted, if they are independent. If, however, the projects are mutually exclusive, we would chose Project S.
113   Recall, that this was our determination using the NPV method as well. The question that naturally arises is whether or
114   not the NPV and IRR methods will always agree.
115
116
117   COMPARISON OF THE NPV AND IRR METHODS (Section 10.4)
118
119   NPV Profiles
120
121   NPV profiles graph the relationship between projects' NPVs and the cost of capital. To create NPV profiles for Projects
122   S and L, we create data tables of NPV at different costs of capital.
123
124                     Net Cash Flows
125       Year        Project S Project L                WACC =       10.0%
126        0           -$1,000    -$1,000                            Project S Project L
127        1              $500       $100                NPV =       $78.82     $49.18
128        2              $400       $300                IRR =       14.49%     11.79%
129        3              $300       $400                Crossover = 7.17%
130        4              $100       $600
               A              B            C          D              E                 F            G              H          I     J       K
131                                                                                                                                     Data Table used to make graph:
132
133                               Project S's                                               Both Projects' Profiles
                                  NPV Profile
134                                                                                                                                      WACC
135          $400                                                              $600                                                         0%
                                                                                       Conflict
136                   NPVs                                                                                No conflict                       5%
             $300                                                              $400        NPVL
137                                                                                                                                       7.17%
                                   Accept             Reject
138          $200                                                                                                                          10%
139                                                                            $200                                                      11.79%




                                                                         NPV
       NPV




140          $100                                                                                                                        14.49%
                                                                                 $0
141                                                                                                                                       15.0%
               $0
142                                                                            -$200
                                                                                                                                           20%
                                                                                                        Crossover
143          -$100        IRRS = 14.49%                                                                  = 7.17%
                                                                                                                                           25%
144                                                                            -$400
145          -$200
                                                                                       0%      5%       10%   15%       20%   25%
146                  0%      5%      10%        15%   20%      25%
                                          WACC                                                            WACC
147
148
149
150   Points about the graphs:
151    1. In Panel a, we see that if WACC < IRR, then NPV > 0, and vice versa.
152    2. Thus, for "normal and independent" projects, there can be no conflict between NPV and IRR rankings.
153    3. However, if we have mutually exclusive projects, conflicts can occur. In Panel b, we see that IRRS is
154       always greater than IRRL, but if WACC < 11.56%, then IRRL > IRRS, in which case a conflict occurs.
155    4. Summary: a. For normal, independent projects, conflicts can never occur, so either method can be used.
156                   b. For mutually exclusive projects, if WACC > Crossover, no conflict, but if WACC < Crossover,
157                      then there will be a conflict between NPV and IRR.
158
            A            B           C           D            E           F           G           H           I            J         K
159
160   Previously, we had discussed that in some instances the NPV and IRR methods can give conflicting results. First, we
161   should attempt to define what we see in this graph. Notice, that the two project profiles (S and L) intersect the x-axis at
162   costs of capital of 14% and 12%, respectively. Not coincidently, those are the IRR's of the projects. If we think about
163   the definition of IRR, we remember that the internal rate of return is the cost of capital at which a project will have an
164   NPV of zero. Looking at our graph, it is a logical conclusion that the IRR of a project is defined as the point at which its
165   profile intersects the x-axis.
166
167   Looking further at the NPV profiles, we see that the two project profiles intersect at a point we shall call the crossover
168   point. We observe that at costs of capital greater than the crossover point, the project with the greater IRR (Project S,
169   in this case) also has the greater NPV. But at costs of capital less than the crossover point, the project with the lesser
170   IRR has the greater NPV. This relationship is the source of discrepancy between the NPV and IRR methods. By
171   looking at the graph, we see that the crossover appears to occur at approximately 7%. Luckily, there is a more precise
172   way of determining crossover. To find crossover, we will find the difference between the two projects cash flows in each
173   year, and then find the IRR of this series of differential cash flows.
174
175              Expected after-tax                      Alternative: Use Tools > Goal Seek to find WACC when NPV(S) =
176             net cash flows (CFt)      Cash flow      NPV(L). Set up a table to show the difference in NPV's, which we
177      Year (t)    Project S Project L differential    want to be zero. The following will do it, getting WACC = 7.17%.
178        0          ($1,000) ($1,000)        0
179        1            500        100       400         Trial project cost of capital, r =   7.17%
180        2            400        300       100                 NPV S (based on trial r)=     $ 134.40
181        3            300        400       (100)               NPV L (based on trial r) =    $ 134.40
182        4            100        600       (500)                                   S-L=      $   0.00
183
184
185
186    IRR =         Crossover rate =            7.17%
187
188
189
190
191
192
193
194   The intuition behind the relationship between the NPV profile and the crossover rate is as follows: (1)
195   Distant cash flows are heavily penalized by high discount rates--the denominator is (1+r)t, and it
196   increases geometrically, hence gets very large at high values of t. (2) Long-term projects like L have most
197   of their cash flows coming in the later years, when the discount penalty is largest, hence they are most
198   severely impacted by high capital costs. (3) Therefore, Project L's NPV profile is steeper than that of S.
199   (4) Since the two profiles have different slopes, they cross one another.
200
201   When dealing with independent projects, the NPV and IRR methods will always yield the same
202   accept/reject result. However, in the case of mutually exclusive projects, NPV and IRR can give
203   conflicting results. One shortcoming of the internal rate of return is that it assumes that cash flows
204   received are reinvested at the project's internal rate of return, which is not usually true. We explain how
205   to modify IRR to accommodate this shortcoming in a later section.
206
207
208   MULTIPLE IRRS (Section 10.5)
209
            A             B          C            D            E           F           G           H            I     J   K
210
211
      Because of the mathematics involved, it is possible for some (but not all) projects that have more than one
212
      change of signs in the set of cash flows to have more than one IRR. If you attempted to find the IRR with
213
      such a project using a financial calculator, you would get an error message. The HP-10B says "Error -
214
      Soln", the HP-17B says '"Many/No Solutions, and the HP12C says Error 3; Key in Guess" when such a
215
      project is evaluated. The procedure for correcting the problem isto store in a guess for the IRR, and then
216
      the calculator will report the IRR that is closest to your guess. You can then use a different "guess"
217
      value, and you should be able to find the other IRR. However, the nature of the mathematics creates a
218
      scenario in which one IRR is quite extraordinary (often, a few hundred percent).
219
220
221
222   Consider the case of Project M.
223
224   Project M:          Year:      0           1            2
225                        CF:      (1.6)        10          (10)
226
227   We will solve this IRR twice, the first time using the default guess of 10%, and the second time we will
228   enter a guess of 300%. Notice, that the first IRR calculation is exactly as it was above.
229
230
231
            1
232 IRR M =              25.0%
233
234
235
236
237
238
239
240
241
            2
242   IRR M =            400%
243
244
245
246
247
248
249
250
251
252
253
254
255
256   The two solutions to this problem tell us that this project will have a positive NPV for all costs of capital
257   between 25% and 400%. We illustrate this point by creating a data table and a graph of the project
258   NPVs.
259
260   Project M:          Year:      0           1            2
261                        CF:      (1.6)        10          (10)
262
263
264
265
266   r =                25.0%
267   NPV =               0.00
268
            A             B          C           D              E          F          G           H            I     J   K
269                     NPV
270          r          $0.0                                          Multiple Rates of Return
271         0%         (1.60)
272        25%          0.00                         $1.50
273        50%          0.62
274        75%          0.85                         $1.00
275       100%          0.90    Max.
276       125%          0.87                         $0.50
277       150%          0.80
278       175%          0.71                         $0.00
279       200%          0.62                   -100%       0%       100%       200%    300%      400%      500%
280       225%          0.53                        -$0.50
281       250%          0.44
282       275%          0.36                         -$1.00
283       300%          0.28
284       325%          0.20                         -$1.50
285       350%          0.13
286       375%          0.06                         -$2.00
287       400%          0.00
288       425%         (0.06)
289       450%         (0.11)
290       475%         (0.16)
291       500%         (0.21)
292       525%         (0.26)
293       550%         (0.30)
294
295
296   MODIFIED INTERNAL RATE OF RETURN (MIRR) (Section 10.6)
297
298   The modified internal rate of return is the discount rate that causes a project's cost (or cash outflows) to
299   equal the present value of the project's terminal value. The terminal value is defined as the sum of the
300   future values of the 'project's cash inflows, compounded at the project's cost of capital. To find MIRR,
301   calculate the PV of the outflows and the FV of the inflows, and then find the rate that equates the two.
302   Or, you can solve using the MIRR function.
303
304
305
306
         A               B           C           D            E           F           G           H           I       J   K
307   WACC =            10%
308                                         Project S                            MIRRS =        12.11%
309                       10%                                                    MIRRL =        11.33%
310                       0          1           2            3           4
311                    (1,000)      500         400          300         100
312
313                                         Project L
314
315                       0          1           2            3           4
316                    (1,000)      100         300          400         600
317                                                                     440.0
318                                                                     363.0
319                                                                     133.1
320            PV:      (1,000)                      Terminal Value:   1,536.1
321
322   The advantage of using the MIRR, relative to the IRR, is that the MIRR assumes that cash flows received
323   are reinvested at the cost of capital, not the IRR. Since reinvestment at the cost of capital is more likely,
324   the MIRR is a better indicator of a project's profitability. Moreover, it solves the multiple IRR problem,
325   as a set of cash flows can have but one MIRR .
326
327   Note that if negative cash flows occur in years beyond Year 1, those cash flows would be discounted at the
328   cost of capital and added to the Year 0 cost to find the total PV of costs. If both positive and negative
329   flows occurred in some year, the negative flow should be discounted, and the positive one compounded,
330   rather than just dealing with the net cash flow. This makes a difference.
331
332   Also note that Excel's MIRR function allows for discounting and reinvestment to occur at different rates.
333   Generally, MIRR is defined as reinvestment at the WACC, though Excel allows the calculation of a
334   special MIRR where reinvestment occurs at a different rate than WACC.
335
336
      Finally, it is stated in the text, when the IRR versus the NPV is discussed, that the NPV is superior
337
      because (1) the NPV assumes that cash flows are reinvested at the cost of capital whereas the IRR assumes
338
      reinvestment at the IRR, and (2) it is more likely, in a competitive world, that the actual reinvestment rate
339
      is more likely to be the cost of capital than the IRR, especially if the IRR is quite high. The MIRR setup
340
      can be used to prove that NPV indeed does assume reinvestment at the WACC, and IRR at the IRR.
341
            A            B              C             D            E             F         G         H          I         J    K
342
343                                               Project S
344   WACC =               10%
345                       0             1             2            3            4
346                    (1,000)         500           400          300          100
347                                                                           330.0
348                                                                           484.0
349                                                                           665.5        Reinvestment at WACC = 10%
350   PV outflows                  -$1,000.00             Terminal Value:    1,579.5
351   PV of TV                        $1,078.82
352   NPV                         $     78.82      Thus, we see that the NPV is consistent with reinvestment at WACC.
353
354
355   Now repeat the process using the IRR as the discount rate.
356
357                                               Project S
358   IRR =             14.49%
359                       0             1             2            3            4
360                    (1,000)         500           400          300          100
361                                                                           343.5
362                                                                           524.3
363                                                                           750.3        Reinvestment at IRR = 14.49%
364   PV outflows                  -$1,000.00             Terminal Value:    1,718.1
365   PV of TV                        $1,000.00
366   NPV                                $0.00 Thus, if compounding is at the IRR, NPV is zero. Since the
367                                            definition of IRR is the rate at which NPV = 0, this demonstrates
368                                            that the IRR assumes reinvestment at the IRR.
369
370   PROFITABILITY INDEX (PI) (Section 10.7)
371
372   The profitability index is the present value of all future cash flows divided by the intial cost. It measures
373   the PV per dollar of investment.
374
375   For project S:
376                     PI(S) = PV of future cash flows            ÷        Initial cost
377                     PI(S) = $ 1,078.82                         ÷        $ 1,000.00
378                     PI(S) =     1.079
379
380 For project L:
381                     PI(L) = PV of future cash flows            ÷        Initial cost
382                     PI(L) = $ 1,049.18                         ÷        $ 1,000.00
383                     PI(L) =     1.049
384
385
386
387   PAYBACK METHODS (Section 10.8)
388
389   Payback Period
390   The payback period is defined as the expected number of years required to recover the investment, and it was the first
391   formal method used to evaluate capital budgeting projects. First, we identify the year in which the cumulative cash
392   inflows exceed the initial cash outflows. That is the payback year. Then we take the previous year and add to it
393   unrecovered balance at the end of that year divided by the following year's cash flow. Generally speaking, the shorter
394   the payback period, the better the investment.
395
396 Figure 10-4.       Payback Periods for Projects S and L
397
398 Project S                    Year:                0            1             2          3        4
399                                                    |            |             |          |        |
400                              Cash flow:         -1,000        500           400        300      100
         A           B         C          D       E      F      G      H     I   J   K
401            Cumulative cash flow:    -1,000   -500   -100   200    300
             Percent of year required
402                      for payback:            1.00   1.00   0.33   0.00
403                        Payback =    2.33
404
405 Project L            Year:            0       1      2       3     4
406                                        |       |      |       |     |
407                     Cash flow:      -1,000   100    300     400   600
408           Cumulative cash flow:     -1,000   -900   -600   -200   400
             Percent of year required
409                      for payback:            1.00   1.00   1.00   0.33
410                        Payback =    3.33
411
412
413
            A             B         C           D            E            F           G         H            I        J   K
414   Discounted Payback Period
415   Discounted payback period uses the project's cost of capital to discount the expected cash flows. The
416   calculation of discounted payback period is identical to the calculation of regular payback period, except
417   you must base the calculation on a new row of discounted cash flows. Note that both projects have a cost
418   of capital of 10%.
419
420   WACC =                 10%
421
422 Figure 10-5. Projects S and L: Discounted Payback Period (r = 10%)
423
424 Project S             Year:        0        1        2         3                            4
425                                     |        |        |         |                            |
426                     Cash flow: (1,000)     500      400       300                          100
427           Discounted cash flow: (1,000)   454.5    330.6     225.4                         68.3
428      Cumulative discounted CF: (1,000)    (545.5) (214.9)     10.5                         78.8
                 Percent of year required
429                    for payback:                        1.00         1.00       0.95        0.00
430           Discounted Payback:              2.95
431
432 Project L             Year:                  0          1            2           3          4
433                                               |          |            |           |          |
434                      Cash flow:           (1,000)      100          300         400        600
435           Discounted cash flow:           (1,000)      90.9        247.9       300.5      409.8
436      Cumulative discounted CF:            (1,000)     (909.1)      (661.2)    (360.6)      49.2
                 Percent of year required
437                          for payback:                  1.00         1.00       1.00        0.88
438                 Discounted Payback:        3.88
439
440
441   The inherent problem with both paybacks is that they ignore cash flows that occur after the payback
442   period mark. While the discounted method accounts for timing issues (to some extent), it still falls short of
443   fully analyzing projects. However, all else equal, these two methods do provide some information about
444   projects' liquidity and risk.
445
446
447   SPECIAL APPLICATIONS OF CASH FLOW EVALUATION (Section 10.11)
448
449   PROJECTS WITH UNEQUAL LIVES
450
451
      If two mutually exclusive projects have different lives, and if the projects can be repeated, then it is
452
      necessary to deal explicitly with those unequal lives. We use the replacement chain (or common life)
453
      approach. This procedure compares projects of unequal lives by equalizing their lives by assuming that
454
      each project can be repeated as many times as necessary to reach a common life span. The NPVs over this
455
      life span are then compared, and the project with the higher common life NPV is chosen. To illustrate,
456
      suppose a firm is considering two mutually exclusive projects, either a conveyor system (Project C) or a
457
      fleet of forklift trucks (Project F) for moving materials. The firm's cost of capital is 12%. The cash flow
458
      timelines are shown below, along with the NPV and IRR for each project.
459
460
461              r=    11.5%
462
463   Figure 10-6 Analysis of Projects C and F (r = 11.5%)
464
465   Project C:
466
467   Year (t)           0           1           2           3           4           5           6
            A            B           C           D            E           F           G           H             I         J   K
468   CFt for C       #######     $8,000      $14,000      $13,000     $12,000     $11,000     $10,000
469
470                   NPVC =      $7,165                    IRRC =       17.5%
471
472   Project F:
473
474   Year (t)           0           1           2            3
475   CFt for F       #######     $7,000      $13,000      $12,000
476
477                   NPVF =      $5,391                    IRRF =       25.2%
478
479   Common Life Approach with F Repeated (Project FF):
480
481   Year (t)           0           1           2            3            4           5           6
482   CFt for F       #######     $7,000      $13,000     $12,000
483   CFt for F                                           ($20,000)     $7,000     $13,000     $12,000
484   CFt for FF      #######     $7,000      $13,000      ($8,000)     $7,000     $13,000     $12,000
485
486                   NPVFF =     $9,281                   IRRFF =       25.2%
487
488
489   On the basis of this extended analysis, it is clear that Project F is the better of the two investments (with
490   both the NPV and IRR methods).
491
492   Equivalent Annual Annuity (EAA) Approach
493
494   Here are the steps in the EAA approach.
495               1. Find the NPV of each project over its initial life (we already did this in our previous analysis).
496                  NPVC=          7,165
497                  NPVF=          5,391
498
499                2. Convert the NPV into an annuity payment with a life equal to the life of the project.
500                   EEAC=         1,718              Note: we used the Function Wizard for the PMT function.
501                   EEAF=         2,225
502
503   Project F has a higher EEA, so it is a better project.
504
505   ECONOMIC LIFE VS. PHYSICAL LIFE
506
507   Sometimes an asset has a physical life that is greater than its economic life. Consider the following asset
508   which has a physical life of three years. During its life, the asset will generate operating cash flows.
509   However, the project could be terminated and the asset sold at the end of any year. The following table
510   shows the operating cash flows and the salvage value for each year-- all values are shown on an after-tax
511   basis.
          A           B          C           D           E        F      G        H   I   J   K
512
                             Operating    Salvage
513                 Year       Cash        Value
514                  0         ($4,800)     $4,800
515                  1          $2,000      $3,000
516                  2          $2,000      $1,650
517                  3          $1,750          $0
518
519 The cost of
520

                                                      PV of            PV of
              3-Year NPV = Intial Cost       +                    +
                                                     Operating        Salvage
521                                                  Cash Flow         Value
522                       = ($4,800.00)      +        $4,785.88   +      $0.00
523            3-Year NPV =   ($14.12)
524 The asset has
525 a negative
526
527

                                                      PV of            PV of
              2-Year NPV = Intial Cost       +                    +
                                                     Operating        Salvage
528                                                  Cash Flow         Value
529                      = ($4,800.00)       +        $3,471.07   +   $1,363.64
530           2-Year NPV =     $34.71
531

                                                      PV of            PV of
              1-Year NPV = Intial Cost       +                    +
                                                     Operating        Salvage
532                                                  Cash Flow         Value
533                      = ($4,800.00)       +        $1,818.18   +   $2,727.27
534           1-Year NPV = ($254.55)
        L           M          N           O          P           Q
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 Notice that the NPV function isn't really a Net present value.
57 Instead, it is the present value of future cash flows. Thus, you
   specify only the future cash flows in the NPV function. To find the
   true NPV, you must add the time zero cash flow to the result of the
   NPV function.
   Notice that the NPV function isn't really a Net present value.
        L             present value of future O flows. P
   Instead, it is the M          N            cash      Thus, you Q
58 specify only the future cash flows in the NPV function. To find the
59 true NPV, you must add the time zero cash flow to the result of the
60 NPV function.
61
62
63
64
65
66
67
68
      L   M   N   O   P   Q
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 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
               L          M        N   O   P   Q
      131
Data Table used to make graph:
      132      Project NPVs
      133      S           L
      134     $78.82     $49.18
      135     $300.00    $400.00
      136     $180.42    $206.50
      137     $134.40    $134.40
      138      $78.82     $49.18
      139      $46.10      $0.00
      140       $0.00    -$68.02
      141      -$8.33    -$80.14
      142     -$83.72 -$187.50
      143 -$149.44 -$277.44
      144
      145
      146
      147
      148
      149
      150
      151
      152
      153
      154
      155
      156
      157
      158
      L   M   N   O   P   Q
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
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
      L   M   N   O   P   Q
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
      L   M   N   O   P   Q
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
      L   M   N   O   P   Q
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
SECTION 10.2
SOLUTIONS TO SELF-TEST

3 A project has the following expected cash flows: CF0 = -$500, CF1 = $200, CF2 = $200, and CF3 = $400. If the project
of capital is 9 percent, what is the NPV?

         WACC                      9%

                          Expected After-Tax
                          Net Cash Flows, CFt
        Year (t)
          0                       -$500
          1                        $200
          2                        $200
          3                        $400

         NPV =                  $160.70
d CF3 = $400. If the project cost
SECTION 10.3
SOLUTIONS TO SELF-TEST

3 A project has the following expected cash flows: CF0 = -$500, CF1 = $200, CF2 = $200, and CF3 = $400. What
is the IRR?



                       Expected After-
                        Tax Net Cash
      Year (t)
        0                   -$500
        1                    $200
        2                    $200
        3                    $400

       IRR =               24.1%
and CF3 = $400. What
SECTION 10.5
SOLUTIONS TO SELF-TEST

2 A project has the following cash flows: CF0 = −$1,100, CF1 = $2,100, CF2 = $2,100, and CF3 = −$3,600.
How many positive IRRs might this project have? If you set the starting trial value to 10 percent in either
your calculator of Excel, what is the IRR? If you set the starting trial value to 400%? What is the NPV of
the project with a very low cost of capital, such as r = 0%? Does this suggest that the project should or
should not be accepted?

                                          Project
                        Year (t)            CFs
                          0               -$1,100
                          1                $2,100
                          2                $2,100
                          3               -$3,600

IRR with starting trial at 10%:                 18.2%

IRR with starting trial at 300%:               106.7%

NPV with r = 0%:                             ($500.00)
 and CF3 = −$3,600.
 10 percent in either
What is the NPV of
e project should or
SECTION 10.6
SOLUTIONS TO SELF-TEST

3 A project has the following expected cash flows: CF0 = -$500, CF1 = $200, CF2 = $200, and CF3 = $400.
Using a 10 percent discount rate and reinvestment rate, what is the MIRR?

Discount rate = reinvestment rate =                10%

                     Expected After-Tax
                     Net Cash Flows, CFt
      Year (t)
        0                    -$500
        1                     $200
        2                     $200
        3                     $400

      MIRR =                 19.9%
and CF3 = $400.
SECTION 10.7
SOLUTIONS TO SELF-TEST

2 A project has the following expected cash flows: CF0 = -$500, CF1 = $200, CF2 = $200, and CF3 = $400. If the project
of capital is 9 percent, what is the PI?

      WACC                      9%

                      Expected After-Tax Net
                         Cash Flows, CFt
      Year (t)
        0                      -$500
        1                       $200
        2                       $200
        3                       $400

       PI =                     1.32
nd CF3 = $400. If the project cost
SECTION 10.8
SOLUTIONS TO SELF-TEST

3 A project has the following expected cash flows: CF0 = -$500, CF1 = $200, CF2 =
$200, and CF3 = $400. If the project's cost of capital is 9%, what are the project's
payback period and discounted payback period?


                       Year:         0          1         2          3
                      Cash flow:   (500)       200       200        400
                Cumulative CF:     (500)      (300)     (100)       300
        Percent of year required
                    for payback:              1.00      1.00       0.25

          Discounted Payback:      2.25


r=                9%


                     Year:           0         1          2         3
                    Cash flow:     (500)      200        200       400
        Discounted cash flow:      (500)     183.49     168.34    308.87
     Cumulative discounted CF:     (500)    (316.51)   (148.18)   160.70
       Percent of year required               1.00       1.00      0.48

          Discounted Payback:      2.48

								
To top