Compute IRR of Investment with Irregular Cash Flows

 Last reviewed: June 22, 1995
Article 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:

  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

KBCategory: kbusage
KBSubcategory:

Additional words: 2.00 2.01 2.10 2.20 2.21 3.00 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 22, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.