Microsoft Office Tutorials and References

In Depth Information

**Irregular Cash Flows**

Figure 12-17:
Accumulating balance approach for multiple IRRs.

The series of flows then becomes the change in the balances, rather than the original given cash

flows. The internal rate of return on these balanced-derived flows is zero, or very close to zero.

I’ve already taken into account all the financing and reinvesting necessary for the project, and the

resulting interest and return are shown in the flows. The Risk Rate Equivalent IRR may be

compared with a different rate such as the Risk Free Rate of Return (traditionally 90-day Treasury

bills) to determine the relative risk of the project.

But what does this all mean? If you pay 9% on negative balances, this project returns an 8.579%

rate to you on positive balances. The name “Risk Rate Equivalent IRR” refers to the fact that it

determines how the project compares with the return on money invested in a bank or 90-day

Treasury bills.

There is no requirement that the finance rate be fixed. A bank may do calculations in the same way

but fix the deposit rate and allow the Goal Seek feature to calculate the equivalent lending rate.

Irregular Cash Flows

All the functions discussed so far — NPV, IRR, and MIRR — deal with cash flows that are
regular.

That is, they occur monthly, quarterly, yearly, or at some other periodic interval. Excel provides

two functions for dealing with cash flows that don’t occur regularly: XNPV and XIRR.

Net present value

The syntax for XNPV is