Excel 2019 Bible

Chapter 15: Using Formulas for Financial Analysis

15

Note that we have to include the initial investment for IRR to work. The first row was

added to show the initial $30,000 investment. The following IRR formula shows that the

investment return is 10.53%:

=IRR(C3:C10,0.08)

FIGURE 15.23

The internal rate of return of a series of future cash flows

The first argument for IRR is the range of cash flows. The second argument is a guess of

what is the internal rate of return. If you don’t supply a guess, Excel uses 10% as the guess.

IRR works by calculating the present value of each cash flow based on the guessed rate. If

the sum of those is greater than zero, it reduces the rate and tries again. Excel keeps iterat-

ing through rates and summing present values until the sum is zero. Once the present val-

ues sum to zero, it returns that rate.

Calculating nonperiodic future cash flows

For both the NPV function and the IRR function, the future cash flows are assumed to be

at regular intervals. That may not always be the case. For cash flows at irregular intervals,

Excel provides the XIRR function.

XIRR requires one more argument than IRR: dates. IRR doesn’t need to know the dates

because it assumes that the cash flows are the same distance apart. Whether they are one

day apart or one year, IRR doesn’t care. The rate it returns will be consistent with the cash

flows. That is, if the cash flows are annual, the rate will be an annual rate. If the cash flows

are quarterly, the rate will be quarterly.

XIRR has a related function for calculating the net present value of nonperiodic cash flows called XNPV. Like XIRR,
XNPV requires a matching range of dates.