Part II: Working with Formulas and Functions
Figure 15.24 shows a schedule of nonperiodic cash flows. On some days, the investment
loses money and requires a cash injection. On other days, the investment makes money and
returns it to the investor. Over all the cash flows, the investor makes an annual return of
10.14%. The following formula uses XIRR to calculate the return:
=XIRR(C3:C17,B3:B17,0.08)
FIGURE 15.24
The internal rate of return of nonperiodic cash flows
Internally, XIRR works in much the same way as IRR. It calculates the present value of
each cash flow individually, iterating through rate guesses until the sum of the present
values is zero. It bases the present value calculations on the number of days between the
current cash flow and the one just previous in date order. Then it annualizes the rate of
return.
Performing financial forecasting
Forecasting refers to predicting values based on historical values. The values can be finan-
cial (for example, sales or income) or any other time-based data (for example, number of
employees).
Excel 2019 makes the forecasting process easier than ever.
