Docstoc

Perils of the Internal Rate of Return

Document Sample
Perils of the Internal Rate of Return Powered By Docstoc
					Sage Note: NPV and IRR                                                                         Page 1




What’s it Worth?
The two most-used measures for evaluating an investment are the net present value
(NPV) and the internal rate of return (IRR).
It is often assumed that higher is better for both the net present value and the internal
rate of return. In particular, it is usually stated that investments with higher internal
rates of return are more profitable than investments with lower internal rates of return.
However, this is not necessarily so. In some situations, an investment with a lower
internal rate of return may be better, even judged on narrow financial grounds, than an
investment with a higher internal rate of return. This note explores why and when this
reversal takes place.
To review, both the net present value and the internal rate of return require the idea of
an income stream, so let's start there. An income stream is a series of amounts of
money. Each amount of money comes in or goes out at some specific time, either now
or in the future. The income stream represents the investment; the income stream is all
you need to know for financial evaluation purposes.
In real life, individuals, charitable institutions, and even for-profit businesses have
social or other goals when selecting investments. For businesses, the benefits of
community good will are no less real for being difficult to measure precisely. For
enterprises with social as well as financial goals, the measures discussed here are still
useful: They tell you how much it costs you to advance your social goals.
Here is an income stream example:

Year                                  0       1       2          3          4          5          6
Income amounts                   -$1000    $200    $200       $200       $200       $200       $200

Here we see seven points in time and, for each, a dollar inflow or outflow. At year 0
(now), the income amount is negative. Negative income is cost, or outgoing, or
investment. In this example, the negative income amount in year 0 represents the cost
of buying and installing the machine.
In the future, at years 1 through 6, there will be net income of $200 each year.
All of the amounts in the income stream are net income, meaning that each is income
minus outgoings, or revenue minus cost. In year 0, the cost exceeds the revenue by
$1000. In years 1 though 6, the revenue will exceed the cost by $200.
This investment evidently has no salvage value. That is, there is nothing that can be
sold in year 6, the last year. If there were, the amount that could be realized from the
sale would be added to the income amount for year 6.
For simplicity, all these examples have the incomes and outgoings at one-year
intervals. Real-life investments can have income and expenses at irregular times, but
the principles of evaluation are the same.
Now let's discuss our two measures in connection with this income stream:
Net Present Value
The net present value of an income stream is the sum of the present values of the
individual amounts in the income stream. Each future income amount in the stream is
discounted, meaning that it is divided by a number representing the cost of capital
from now (year 0) until the year when income is received or the outgoing is spent. For
an individual , the cost of capital can either be how much you would have earned
investing the money someplace else, or how much interest you would have had to pay

                                                   For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                         Page 2




if you borrowed money. For a business the cost of capital is generally measured as
the Weighted Average Cost of Capital, which is the weighted average cost of
shareholders equity and debt.
The word "net" in "net present value" indicates that our calculation
includes the initial costs as well as the subsequent profits. It also reminds
us that all the amounts in the income stream are net profits, revenues
minus cost. In other words, "net" means the same as "total" here.
The net present value of an investment tells you how this investment compares with
your cost of capital. A positive net present value means this investment return is
above your cost of capital. . A negative net present value means your return is below
your cost of capital. .
Consider again this income stream:

Year                                  0       1       2          3          4          5          6
Income amounts                   -$1000    $200    $200       $200       $200       $200       $200

Let's assume that the discount rate (the interest rate that you could earn elsewhere or
at which you could borrow) will not change over the life of the project. This makes
the calculation simpler. With this assumption, we can use the usual formula:
Present Value of any one income amount = (Income amount) / ( (1 + Discount
Rate) to the ath power)
a is the number of years into the future that the income amount will be received (or
spent, if the income amount is negative).
The net present value (NPV) of a whole income stream is the sum of these present
values of the individual amounts in the income stream. If we still assume that income
comes or goes in annual bursts and that the discount rate will be constant in the future,
then the NPV has this formula:

NPV = I0 + I1/(1+r) + I2/(1+r)2 + … + In/(1+r)n

The I s are income amounts for each year. The subscripts (which are also the
exponents in the denominators) are the year numbers, starting with 0, which is this
year. The discount rate - assumed to be constant - is r. The number of years the
investment lasts is n. (This formula simplifies somewhat if the cashflows are all
identical.)
Three properties of the net present value of an income stream are:
    1. Higher income amounts make the net present value higher. Lower
    income amounts make the net present value lower.
Try it yourself.
Double-click on the table below, then Click on a box in the Income row. Edit the
number there, deleting or adding some digits. Then press Enter.




                                                   For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                         Page 3




Year                           0      1      2          3             4              5              6
Income                   -$1,000   $200   $200       $200          $200           $200           $200
amounts
Rate                         5%
Present value              -1000    190    181         173           165            157            149
Net Present
Value                        15

The Net Present Value box shows the net present value, which is the total of the
amounts in the boxes in the Present value row. (The total may be slightly off, due to
rounding.)
    2. If profits come sooner, the net present value is higher. If profits come
    later, the net present value is lower.
Try it yourself in the spreadsheet fragment above. You can see how moving the
amounts further out changes the net present value of the income stream.
    3. Changing the discount rate changes the net present value. For an
    investment with the common pattern of having costs early and profits
    later, a higher discount rate makes the net present value smaller.
Again, try it yourself. You can also change the income amounts, if you want.

To summarize what was just illustrated, the net present value is higher if the income
amounts are larger, or if they come sooner, or if the discount rate is lower. The net
present value is lower if the income amounts are smaller, or if they come later, or if
the discount rate is higher.
Internal Rate of Return
In the example we've been using, if you keep the income amounts at their original
values: -1000, 200, 200, 200, 200, 200, and 200, and set the discount rate to 0.0547,
the net present value becomes 0. This discount rate, 0.0547 or 5.47%, is the internal
rate of return for this investment - it is the discount rate that makes the net present
value equal to 0. You can try this in the spreadsheet fragment, by setting the discount
rate to 0.0547 or 5.47%.

If you now raise any of the income amounts in years 1 through 6 (feel free to edit an
income amount and see for yourself), you will need a higher discount rate to bring the
net present value back to 0. That would seem to imply that projects with higher
incomes have higher internal rates of return.
Similarly, if you lower any of the income amounts in years 1 through 6, then a lower
discount rate will be needed to bring the net present value back up to 0. That would
seem to imply that projects with lower incomes have lower internal rates of return.
These seeming implications are actually often true, if the projects being compared
have about the same “shape”, with the costs coming early and the benefits coming
late, and if the projects being compared switch from net outgoing to net income at
about the same time. Otherwise, though, the implications might not be true.
Before we go on to that, a little review:
Which of these measures (net present value and internal rate of return) requires you to
know the future income and outgoing amounts?
Which of the measures requires you to know what the discount rate will be in the
future?

                                                   For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                          Page 4




The internal rate of return does not require you to predict future discount rates. That
would seem to make the internal rate of return the more useful (or less uncertain)
measure. Sometimes, though, the internal rate of return can fool you.
Contradictory Results
A few years ago, the New England Journal of Medicine published a study that
evaluated various types of professional education as if they were financial
investments.
    The article is: Weeks, W.B., Wallace, A.E., Wallace, M.M., Welch, H.G., "A
    Comparison of the Educational Costs and Incomes of Physicians and Other
    Professionals," N Engl J Med, May 5, 1994, 330(18), pp. 1280-1286.
The idea was to see if doctors were overpaid, by considering primary and specialty
medical education as investments and comparing them with investing in education in
business, law, and dentistry. Adjustments were made for differences in average
working hours. The authors found that primary medicine was the poorest investment
of all of these. Specialty medicine did better, but was not out of line with the other
professions.
In the results was this oddity: by the criterion of the net present value of lifetime
educational costs and income benefits, specialist physicians tied for highest with
attorneys. Both were ahead of business school graduates. However, by the criterion of
the internal rate of return, specialty physicians, with a 21% average return, were well
behind the attorneys’ 25% average return, while the business school graduates’ 29%
average return was the highest of all. The present value and the internal rate of return
ranked the alternatives differently!
By the way, since this article's 1994 publication, managed care in the US has forced
specialty physician incomes down by perhaps one-third. This has sharply lowered the
investment value of a specialty medical education.
The NPV Curve
One way to understand how the net present value and the internal rate of return can
give seemingly different advice is to consider the net present value curve, or NPV
curve. The NPV curve shows the relationship between the discount rate and the net
present value for a range of discount rates. The present value at a given discount rate,
such as 5%, and the internal rate of return are each points on the NPV curve.
The NPV curve, the relationship between the discount rate and the net present value,
has the formula we saw already for the net present value, for annualized costs and
revenues and a constant discount rate. Each I is an income amount for a specific year.
The subscripts (which are also the exponents in the denominators) are the year
numbers, starting with 0, which is this year. The constant discount rate is r. The
number of years the investment lasts is n. In Weeks’ study of professionals’ incomes,
n was about 44, because costs and incomes were calculated from age 21 to age 65.
We’ll use an example with an n of 6, corresponding to our machine investment
example above. The NPV is a function of r. Graphed, it looks like this:




                                                    For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                                                          Page 5




                                                                   NPV Curve
                        $200

                        $150
    Net Present Value


                        $100

                         $50

                          $0

                         -$50

                        -$100

                        -$150
                                               0     0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09             0.1
                                                                    Discount Rate (r)



The curve shows the net present value for discount rates (r) from 0 to 0.1 (0% to
10%). The marked points are the two points we get from our measures. The left dot (a
diamond shape) shows the net present value at the discount rate of 0.05 (5%). The
right dot (a square outline) shows the internal rate of return, because it is where the
curve crosses the horizontal line indicating an NPV of 0. That right dot is between the
0.05 and 0.06 marks on the r axis, so the internal rate of return is between 0.05 and
0.06. (The actual internal rate of return is about 0.0547, as we saw earlier.)
Imagine we have another possible investment, which has cashflows of $220 at each of
years 1 to 6.
This investment is like the first, except that the net profit in years 1 through 6 is $220
per year, rather than $200. We say that this investment has a similar "shape" to the
first, because the costs and profits come at the same times. Also, the size of the initial
outlay is the same for both. The only difference is the amount of profit. Here’s a graph
with both investments on it:


                                                                        NPV Curves
                                               $300
                                               $250
                                               $200
                           Net Present Value




                                               $150
                                               $100
                                                   $50
                                                   $0
                                                -$50
                                               -$100
                                               -$150
                                                         0   0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09              0.1
                                                                            Discount Rate (r)


The pink curve (the uppermost curve) is the second investment. It is above and

                                                                                    For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                         Page 6




parallel to the first investment’s blue curve. The triangular dot shows the net present
value of the second investment at the discount rate of 0.05. The net present value
there is a little over $100. This is higher than the diamond-shaped dot for the first
curve, so the net present value (at r = 5%) of the pink-line investment is higher than
the net present value at r = 5% for the original investment.
The right dot (large square dot) shows where the second investment’s curve crosses
the NPV = 0 line. This is well to the right of the first investment’s internal rate of
return dot. The internal rate of return for the second investment is much higher
(further to the right).
In this example, our two measures, the net present value at r = 0.05 and the internal
rate of return, tell us the same thing. They both say the second investment is better. A
look at the graph above confirms that the second investment is better at all discount
rates, so it is fair to say that the second investment is unequivocally better than the
first.
Can You Do Both Investments?
Making an investment increases your wealth if its net present value is greater than 0 at
the discount rate relevant to you. If your discount rate is less than 5.47%, both NPV
curves are in positive territory, and you should do both, if you can.
Sometimes, though, the alternative investments are mutually exclusive. For example,
there may be two ways to build a dam across a particular river. You can do one or the
other, but not both. There may be several alternative ways to address a workplace
safety problem. There is no point to doing more than one if any one way solves the
problem. Deciding on a professional education involves somewhat mutually exclusive
choices. A few people do go to medical school and then law school, but the additional
return from the second degree is not the same as what someone going to law school
fresh out of college would expect.
If you can only make one investment, you should choose the one with the highest net
present value at the discount rate appropriate to you. A problem with that advice,
though, is that discount rates can change with general economic conditions. You are
therefore more confident about choosing one investment over another if your chosen
investment has a higher net present value over a broad range of possible discount
rates. In our example so far, the pink-line investment has a higher net present value at
all discount rates, so we would choose it with confidence. Regardless of what happens
in the future to discount rates, we'll be better off with the pink-line investment than
with the original investment. (We’ll leave the question of the uncertainty of the future
cashflows to later …)


Can NPV Curves Cross?
Yes, they can. If the NPV curves cross, then the choice of investment depends on the
discount rate.
To create an example, we'll change the original investment so that its profits come
much later. This increases the effect of the discount rate on the net present value.
Below are the two income streams, now. Also shown are their net present values at a
5% discount rate and their internal rates of return.




                                                   For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                                              Page 7




Year                                        0         1       2        3            4             5             6     NPV at Internal
                                                                                                                         0.05 rate of
                                                                                                                     discount  return
                                                                                                                          rate
Pink line                        -$1,000          $220     $220     $220       $220          $220          $220          $117   0.086
investment
Blue line                        -$1,000             $0      $0       $0          $0            $0      $1,550           $157   0.076
investment



The pink line investment has the higher internal rate of return, but the blue line
investment has the higher net present value at a 5% discount rate. Our two measures
are giving us opposite advice!


                                                           NPV Curves
                                $500

                                $400
            Net Present Value




                                $300

                                $200

                                $100

                                  $0

                                -$100

                                -$200
                                        0       0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09              0.1
                                                               Discount Rate (r)


The graph shows what’s going on, by showing the Net Present Value curves for both
investments for discount rates between 0% and 10%. The curves cross at a discount
rate of about 0.064, or 6.4%.
Now, to choose which investment we want to make, assuming we cannot make both,
we have to make a guess about what future discount rates will be. If we expect
discount rates to be less than 6.4%, where the curves cross, we choose the blue line
investment. For discount rates above 6.4%, but below 8.56% (the internal rate of
return of the pink line investment), we choose the pink line investment. At higher
discount rates than 8.56%, we don't do either, because the net present values are
negative.
If Costs Come Later Than Profits
If costs come later than profits, the NPV curve can tilt the other way, making it even
more problematic to use the internal rate of return to compare investments.
Costs can come later than profits if an investment creates environmental problems that
will have to watched or cleaned up later. Nuclear power plants are a good example:
After about 40 years of service (sometimes less than that), they become too
contaminated with radiation to continue in service. They must then be closed and

                                                                       For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                                         Page 8




either guarded where they are for thousands of years or dismantled and moved to a
disposal site. Forestry operations also often generate a large “clean-up cost”, and so
on.

Consider this income stream:
Year                             0                            1      2        3          4          5             6
Income amounts               -$200                         $200   $200     $200       $200       $200         -$900

I've reduced the initial cost, but added a big cost at the end. Let's see what difference
this makes in how the NPV changes when the discount rate changes. If we substitute
these values in the spreadsheet fragment above, with a starting discount rate 5%, the
net present value (NPV) is -$6. That's negative six dollars, so if your discount rate
really were 5%, you would not want to make this investment.
Try changing the discount rate, to 0.04 or 0.03. In the examples above, the NPV goes
up when the discount rate is lowered. Is that true for this project? Then try 0.06 or
0.07. What happens to the NPV?

(Keep the discount rates reasonably small, say between 0.00, which is 0%, and 0.3,
which is 30%.)
The relationship between the discount rate and the NPV is the reverse of what we see
with "normal" investments! With this kind of income stream, higher discount rates
make the net present value bigger, and lower discount rates make the net present
value smaller.

Here is the NPV graph:


                                                       NPV Curves
                                 $60
                                 $40
                                 $20
            Net Present Value




                                  $0
                                 -$20
                                 -$40
                                 -$60
                                 -$80
                                -$100
                                        0   0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09              0.1
                                                           Discount Rate (r)




The net present value at a 5% (0.05) discount rate is at -$6 on the net present value
scale, and where the curve crosses the discount rate axis, where the net present value
is $0, is the internal rate of return, 0.054 (5.4%).



                                                                   For business strategy updates … visit www.sqm.co.nz
        Sage Note: NPV and IRR                                                                                               Page 9




        Or, at least, it fits the standard definition of internal rate of return. However, unlike
        the usual situation, this project is profitable at interest rates above this IRR and
        unprofitable at interest rates below this IRR.
        Suppose we have an alternative project that also has this shape, with a big cost at the
        end, but slightly lower profits in the intermediate years. I’ll call the new alternative
        the "green line investment."

Year                                    0             1         2         3          4              5              6     NPV at        Internal
                                                                                                                            0.05        rate of
                                                                                                                        discount         return
                                                                                                                             rate
Red line         -$200                           $200        $200     $200      $200           $200          -$900             -$6      5.40%
investment
Green line       -$200                           $195        $195     $195      $195           $195          -$900           -$27       7.01%
investment



        The green line investment has a lower NPV than the red line investment at all
        discount rates, because it has lower profits in years 1 through 5, and the same costs in
        years 0 and 6. In particular, as the table above indicates, it has a lower NPV at the
        0.05 discount rate. The graph below shows the NPV curves for both investments, with
        the green line lying below the red line at all discount rates.


                                                                    NPV Curves
                                            $150.00

                                            $100.00
                    Net Present Value




                                             $50.00

                                              $0.00

                                            -$50.00

                                        -$100.00

                                        -$150.00
                                                      0   0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1
                                                                        Discount Rate (r)


        The green line investment is clearly inferior, but it has the higher internal rate of
        return. The green line investment's IRR is 0.07. The red line investment's is 0.054.
        Thus, for projects with big late costs, the better projects will have lower internal rates
        of return, the opposite of the rule for normal projects that have their costs early and
        their positive returns later.
        Now let’s discover something even more strange. Try the cashflows for the red line
        investment, and change the discount rate and see the effect on its value. In this case,
        though, take the discount rate over 0.3 (30%) and all the way up to 1.0 (100%). Those


                                                                                 For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                                           Page 10




rates are much higher than, hopefully, we will ever see, even in NZ, but they are
theoretically possible, and they show a strange phenomenon.
Try raising the discount rate to 0.3, and notice what happens to the net present value.
Then, raise the discount rate some more above that. In which direction does the NPV
move now?

See if you can find the second IRR, where the NPV is zero again!

Here's the NPV curve for the red line investment for discount rates from 0% to 100%.


                                                         NPV Curve
                                $150


                                $100
            Net Present Value




                                 $50


                                  $0


                                 -$50


                                -$100
                                        0   0.1   0.2   0.3    0.4 0.5 0.6 0.7            0.8     0.9      1
                                                              Discount Rate (r)


At discount rates below 0.054, the NPV is negative, and this investment is worse than
doing nothing.
At a discount rate of 0.054, the NPV is 0. The first IRR for this investment is 0.054.
If the discount rate rises above 0.054, the NPV turns positive, and this investment
switches to being profitable.
At a discount rate of 0.262 (26.2%), the NPV for this investment reaches its
maximum. If the discount rate rises further than that, the NPV falls.
The NPV reaches 0 again at a discount rate of 0.86. This is the second IRR for this
investment.
If the discount rate were to rise even more, above 0.86, the NPV turns negative again.
This investment re-switches to being unprofitable.
Lesson: The NPV curve gives better guidance than the IRR alone
The lesson we should get from this is that the internal rate of return, by itself, can fool
you. If the investments being considered have different shapes (that is, very different
timing of costs and benefits) or if the project has large late cleanup costs, then the
higher-IRR-is-better rule can steer you to the wrong investment. Ideally, you want the
NPV curve, if you want to evaluate an investment.
A second lesson
The NPV, and the IRR are summary statistics that describe a series of cashflows at
specific times. There is no allowance for uncertainty in either of these. Reality, of
course, is a little different: future discount rates are unknown, and the cashflows are


                                                                      For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                            Page 11




not quite as precisely defined as we pretend when using the NPV function, or the IRR
function in Excel. What can we do?
Usually, when we plan a project or investment, the predicted cashflows have a
“degree of certainty” attached to them – we can use these ideas (ball-park estimate,
rough estimate, budget estimate, firm estimate) to attach a likely standard deviation to
the “expected values” of the cashflows. We can then quite easily use Excel to
simulate what might happen, in say, 5000 replications of the project – calculate an
NPV for each, and inspect the NPV distribution as another way of assessing the likely
contribution of the project.

An example might help!

Consider the simple set of cashflows below:

(We won’t worry about turning them into monthly flows, or making them more
realistic in that sense, let’s just deal a little with the uncertainty for now.)

Year                 0           1        2        3           4              5
Income         -$1,000        $200     $400     $600        $500           $200
amounts

The NPV curve for this is shown below, with the interest rate varying from 0% to
30%. Clearly this is a worthwhile investment over a reasonable range of interest
rates. What if we now try to allow for the uncertainty in our estimated cashflows?
The investment, we will assume, is certain – it is known to be exactly $1,000 at time
zero. At the end of the first year, we are to receive $200 – how confident are we of
this?


                                       NPV Curve
                                     (0% to 30% rate)
    $1,000
      $800
      $600
      $400
      $200
          $0
     -$200
               0%        5%      10%      15%      20%             25%          30%           35%


Below is a table showing the annual estimated cashflows, and a qualitative description
of the “firmness” of the estimate, together with a common interpretation of these
qualitative descriptions.



                                                       For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                                 Page 12




Income         Strength of          Usual
amounts         estimate        interpretation

  -$1,000 certain              exact number
     $200 Definitive           ± 5%
     $400 Budget               ± 20%
     $600 Budget               ± 20%
     $500 Order of             ± 50%
             Magnitude
      $200   Order of          ± 50%
             Magnitude


If we wish to simulate this series of cashflows, incorporating this level of “likely
variation”, we have to assign standard deviations to each. We’ll assume the ± symbol
indicates an approximate 95% confidence interval, so that, for example, ± 5% on the
first cashflow, of $200, indicates that the interval from $190 to $210 will contain the “true”
cashflow 95% of the time, and will correspond to an interval that is four standard deviations
wide. Hence the associated standard deviation will be $5. Below is the table above,
extended to include these approximate standard deviations.

Income         Strength of          Usual      Width of 95%           Standard deviation
amounts         estimate        interpretation interval               (one quarter width)

  -$1,000 certain              exact number                       0                             0
     $200 Definitive           ± 5%              2 ×$10 = $20                                 $5
     $400 Budget               ± 20%             2 ×$80 = $160                              $40
     $600 Budget               ± 20%             2 ×$120 = $240                             $60
     $500 Order of             ± 50%             2 ×$250 = $500                            $125
             Magnitude
      $200   Order of          ± 50%             2 ×$100 = $200                             $50
             Magnitude

Now to generate a sample from a normal distribution with an average A and a
standard deviation S, use the Excel function =norminv() as follows:

=norminv(rand(), A, S)

Some recent versions of Excel have a fault in the random number generator, which
allows it to generate negative numbers occasionally – to avoid this, use abs(1-rand())
in place of rand() in the formula above. Below is a repeat of the spreadsheet segment,
with these approximate standard deviations inserted, and a series of random cashflows
generated according to the above scheme.

Year                           0            1           2            3             4             5
Income amounts           -$1,000         $200        $400         $600          $500          $200
Standard deviations                0         5         40           60            125            50
Simulated amounts            -$1,000       198        330          600            360           239
Interest rate                 6.50%
NPV                             $428




                                                            For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                          Page 13




Note that this segment is “live” – if you double-click on it, it will activate as an Excel
worksheet, and you can inspect and edit the contents of any of the cells.

We’ll now simulate this cashflow and NPV calculation 1000 times (5,000 is probably
more reasonable, but we can illustrate as easily with 1,000.)
We begin by creating a column consisting of the numbers 1,2, …, 1,000. (Use the fill
handle, or the Edit … Fill command.)
In the cell to the right and one above the ‘1’, enter a cell reference that points at the
result of the NPV calculation for the table above. Below is a spreadsheet fragment
(not live) showing the formula immediately before pressing the Enter key.




We can now use the Table command to fill the cells immediately to the right of the
sequence 1, 2, 3, … with repeat evaluations of the NPV with different random
cashflows each time.

Begin by highlighting the table area: the area from just above the 1 and to the left of
the formula that will be repeatedly evaluated. (Cell H211 in the fragment above.)
Highlight one cell to the right, and down as far as the bottom of the sequence of
numbers. In the case above, this will be the range H211: I1211. Now select the Data
… Table menu. The resulting dialog box looks like this:




The values in the left-most column of the selected area will be sequentially placed in
the Column input cell. Since this number doesn’t actually enter the calculation, it
doesn’t matter where you put it – select cell J211 and click on OK. The worksheet
will recalculate, and fill in the column on the right-hand side with the results of the
1000 simulated cashflows. Remember, each of these cashflows is consistent with
your estimate of how big a cashflow will be, and the likely uncertainty that may be
attached to it, so each is a “reasonable” future scenario under your assumptions. We

                                                     For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                                 Page 14




can now create a histogram of the resulting NPVs to get a better idea of the financial
risk associated with the project. The fragment below shows the sheet after creating
the Table. (Note, if you are doing this yourself, you should get different numbers to
these!)


Year                                 0          1            2            3              4              5
Income amounts                -$1,000      $200           $400        $600          $500           $200
Standard deviations                  0           5          40          60            125             50
Simulated amounts              -$1,000         198         435         703            394            250
Interest rate                   6.50%
NPV                               $640

                                  $640
                         1    775.6543
                         2    843.4251
                         3    390.9278
                         4     939.875
                         5    638.1962
                         6    425.9251
                         7    598.7618
                         8    544.8921
                         9    501.5244

(Note also that the NPVs are reported to several decimal places – in place of this
spurious accuracy and precision, you may wish to round the NPVs to the nearest
dollar, or even 10 dollars. You can do this most easily by using the round function in
cell I211 – instead of =I209, use =round(I209,0) to round to the nearest dollar, or
=round(I209, -1) to round to the nearest ten dollars.)

Below is a histogram of the NPV values that result from this simulation:


                                   Histogram of simulated NPVs
                    180
                    160
                    140
                    120
                    100
                     80
                     60
                     40
                     20
                      0
                                                                                 00
                          0

                                0

                                      0

                                           0

                                                 0

                                                      0

                                                             0

                                                                   0

                                                                           0
                         10

                              20

                                    30

                                          40

                                               50

                                                     60

                                                           70

                                                                 80

                                                                        90
                                                                               10




                                                     NPV




                                                            For business strategy updates … visit www.sqm.co.nz
Sage Note: NPV and IRR                                                                        Page 15




From the data we can also estimate such things as the probability that the project will
end up being worth less than some critical value, or, alternatively, greater than some
critical value. Here, for example, with these estimates, we can see that only about
6.5% of the cashflows have NPV less than $400, while about 8.5% are worth more
than $750. (At this interest rate!)

Final note, and a caution: The combination of the NPV curve and a simulation
based approach to evaluating the likely effect of future cashflows is likely to give you
a better handle on the likely risk associated with a project than the simple comparison
of NPVs or IRR values. The histogram above however, serves to remind us that the
random number generator in Excel isn’t too good! Nevertheless, it will give
enlightening results when used as we have used it here.




                                                   For business strategy updates … visit www.sqm.co.nz

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:23
posted:7/5/2012
language:English
pages:15