Compute IRR of Investment with Irregular Cash Flows

ID: Q50442

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, version 2.2, 3.0

SUMMARY

In Microsoft Excel, the IRR() function calculates the internal rate of return for an investment with the assumption that all of the cash flows occur at even intervals of time. However, some investments have cash flows that occur at uneven intervals. If you use the IRR() function on an investment that has uneven cash flows, Microsoft Excel assumes even intervals and gives an incorrect value.

MORE INFORMATION

To get an accurate internal rate of return for an investment that has uneven cash flow intervals, do the following:

Determine the smallest interval between cash flows.

Enter the cash flow (if any) that occurs at each of those intervals. If no cash flow occurs at a certain point in time, enter a 0 (zero).

Use the IRR() function to calculate the internal rate of return for those values.

At this point, the result is not the true annual rate of return; it is the IRR() for the given interval. To convert the result to the true annual internal rate of return, use the following formula


   =((1+rate)^n)-1

where n is the number of periods per year and rate is the value returned by the IRR() function.

Example

Assume an initial investment of $20,000, and cash flows of $5,000 after one year, $7,500 one year after that, and $10,000 six months after that.

Set up your worksheet as follows:


   A1|                   Month
   A2| -20000                0
   A3|      0                6
   A4|   5000               12
   A5|      0               18
   A6|   7500               24
   A7|  10000               30
   A8| =IRR(A2:A7)         ->The formula in A8 returns  .03003
   A9| =((1+A8)^2)-1       ->The formula in A9 returns  .060973

Additional query words:

Keywords :
Version :
Platform :
Issue type :