Microsoft KB Archive/50442
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
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.
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
where n is the number of periods per year and rate is the value returned by the IRR() function.
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:
Issue type :
Last Reviewed: March 15, 1999