Part II: Working with Formulas and Functions
FIGURE 15.22
The net present value of both positive and negative cash flows
In the first NPV example, the amount invested was not part of the calculation. We simply
took the result of the NPV function and compared it to the investment amount. In this
example, a portion of the investment is also in the future, so the invested amounts are
shown as negatives (cash outflows), and the eventual dividends are shown as positive
amounts (cash inflows).
Instead of comparing the result to an initial investment amount, this NPV calculation is
compared to zero. If the NPV is greater than zero, then the series of cash flows returns
something greater than 8%. If it’s less than zero, the return is less than 8%. Based on the
data in Figure 15.22, it’s a good investment.
Calculating an internal rate of return
In the previous example, we calculated the net present value of future expected cash flows
and compared it to our initial investment amount. Because the net present value was
greater than the initial investment, we knew that the rate of return would be greater than
our desired rate. But what is the actual rate of return?
Excel’s IRR function can be used to calculate the internal rate of return of future cash
flows (see Figure 15.23). IRR is closely related to NPV. IRR computes the rate of return that
causes the NPV of those same cash flows to be exactly zero.
For IRR, we have to structure our data a little differently. There has to be at least one
positive and one negative cash flow in the values range. If you have all positive values,
this means you invest nothing and receive only money. That would be a great investment
but not very realistic. Typically, the cash outflows are at the beginning of the investment
period, and the cash inflows are at the end. But it’s not always that way, as long as there is
at least one of each.
