Microsoft Office Tutorials and References

In Depth Information

The range argument must contain values. Empty cells are not treated as zero. If the

range contains empty cells or text, the cells are ignored.

In most cases, the IRR can be calculated only by iteration. The guess argument, if supplied, acts as a “seed” for

the iteration process. It has been found that a guess of –90% will almost always produce an answer. Other

guesses, such as 0, usually (but not always) produce an answer.

An essential requirement of the IRR function is that there must be both negative and positive income flows: To

get a return, there must be an outlay, and there must be a payback. There is no essential requirement for the out-

lay to come first. For a loan analysis using IRR, the loan amount will be positive (and come first), and the re-

payments that follow will be negative.

The IRR is a very powerful tool, and its uses extend beyond simply calculating the return from an investment.

This function can be used in any situation in which you need to calculate a time- and data-weighted average re-

turn.

The examples in this section are in a workbook named internal rate of return.xlsx,

which is available at this book's website.

Rate of return

This example sets up a basic IRR calculation (see Figure 12-8). An important consideration when calculating

IRR is the payment frequency. If the cash flows are monthly, the IRR will be monthly. In general, you'll want to

convert the IRR to an annual rate. The example uses data validation in cell C3 to allow the user to select the

type of flow (annual, monthly, daily, and so on), which displays in cell D3. That choice determines the appropri-

ate interest conversion calculation; it also affects the labels in row 5, which contain formulas that reference the

text in cell D3.

Cell D20 contains this formula:

=IRR(D6:D18,–90%)

Cell D21 contains this formula:

=FV(D20,C3,0,–1)–1

The following formula, in cell D23, is a validity check:

=NPV(D20,D7:D18)+D6