Using the IRR Function

Excel's IRR function returns the discount rate that makes the net present value of an investment zero. In other words, the IRR function is a special-case NPV.

The syntax of the IRR function is

 IRR(range,guess) 

Caution 

The range argument must contain values. Empty cells are not treated as zero. If the range contains empty cells or text, the IRR function does not return an error. Rather, it will return an incorrect result.

In most cases, the IRR can only be calculated by iteration. The guess argument, if supplied, acts as a "seed" for the iteration process. It has been found that a guess of –0.9 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 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.

On the CD 

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,-0.9) 

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 

The IRR is the rate at which the discounting of the cash flow produces an NPV of zero. The formula in cell D23 uses the IRR in an NPV function applied to the same cash flow. The NPV discounting at the IRR (per quarter) is $0.00-so the calculation checks.

Geometric Growth Rates

You may have a need to calculate an average growth rate, or average rate of return. Because of compounding, a simple arithmetic average does not yield the correct answer. Even worse, if the flows are different, an arithmetic average will not take these variations into account.

A solution uses the IRR function to calculate a geometric average rate of return. This is simply a calculation that determines the single percentage rate per period that exactly replaces the varying ones.

This example (see Figure 12-13) shows the IRR function being used to calculate a geometric average return based upon index data (in column B). The calculations of the growth rate for each year are in column C. For example, the formula in cell C5 is

 =(B5/B4)-1 

Figure 12-13: Using the IRR function to calculate geometric average growth.

The remaining columns show the geometric average growth rate between different periods. The formulas in Row 10 use the IRR function to calculate the internal rate of return. For example, the formula in cell F10, which returns 5.241%, is

 =IRR(F4:F8,-0.9) 

In other words, the growth rates of 5.21%, 4.86%, and 5.66% are equivalent to a geometric average growth rate of 5.241%.

The IRR calculation takes into account the direction of flow and places a greater value on the larger flows.

Checking Results

Figure 12-14 shows a worksheet that demonstrates the relationship between IRR, NPV, and PV by verifying the results of some calculations. This verification is based on the definition of IRR: The rate at which the sum of positive and negative discounted flows is 0.

Figure 12-14: Checking IRR and NPV using sum of PV approach.

The net present value is calculated in cell B16:

 =NPV(D3,B7:B14)+B6 

The internal rate of return is calculated in cell B17:

 =IRR(B6:B14,-0.9) 

In column C, formulas calculate the present value. They use the IRR (calculated in cell B17) as the discount rate, and use the period number (in column A) for the nper. For example, the formula in cell C6 is

 =PV($B$17,A6,0,-B6) 

The sum of the values in column C is 0, which verifies that the IRR calculation is accurate.

The formulas in column D use the discount rate (in cell D3) to calculate the present values. For example, the formula in cell D6 is

 =PV($D$3,A6,0,-B6) 

The sum of the values in column D is equal to the net present value.

For serious applications of NPV and IRR functions, it is an excellent idea to use this type of cross-checking.