Microsoft Office Tutorials and References

In Depth Information

**Using the IRR Function**

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 outlay to come first. For a loan analysis using IRR, the loan amount

will be positive (and come first), and the repayments 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 return.

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

xlsx
, which is available on the companion CD-ROM.

Rate of return

This example sets up a basic IRR calculation (see Figure 12-12). 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 appropriate interest conversion calculation; it also

affects the labels in row 5, which contain formulas that reference the text in cell D3.

Figure 12-12:
The IRR returns the rate based on the cash flow frequency and should be converted into an

annual rate.

Cell D20 contains this formula:

=IRR(D6:D18,–90%)