Microsoft KB Archive/50442

{|
 * width="100%"|

Compute IRR of Investment with Irregular Cash Flows

 * }

-

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:


 * 1) Determine the smallest interval between cash flows.
 * 2) 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).
 * 3) 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 :

Technology :