How to Calculate IRR With Unequal Timing of Cash Flows
Original post by Sean Mullin of Demand Media
The internal rate of return (IRR) measures the overall profitability of an investment. Finance experts call this rate internal because it does not account for external factors such as risk and inflation. To create an IRR figure for an investment with uneven cash flows, use Microsoft Excel's XIRR function. Calculating the same figure by hand would require entering estimated rates into a complex formula until you discover a rate that brings the investment's net present value to 0. Solving the formula repeatedly by hand is time-consuming, whereas computers perform the same task easily.
Create two columns in Excel. Label the first column "values" and the second column "dates."
Enter the initial investment as the first value; the investment must be a negative number. Add the date of the investment in the corresponding row.
Add both the positive and negative expected cash flows in the values column. Type the date of each cash flow in the dates column.
Select a tab below your chart and type the following formula: "=XIRR(values,dates,guess)." Describe the columns and rows using their letter and number labels. For example, if you entered 10 cash flow values down the A column and their corresponding dates down the B column, you would type this formula: =XIRR(A1:A10,B1:B10). Enter an estimated return rate in decimal form, if you wish: =XIRR(A1:A10,B1:B10,0.14).
Click "Enter" to perform the calculation. Convert the resulting IRR decimal to percentage form.
- Economics Interactive Lecture: Internal Rate of Return
- Economics Interactive Tutorial: Perils of the Internal Rate of Return
About the Author
Sean Mullin has been creating content for providers such as eHow since 2007. He also worked in an online writing center for college students. In addition to writing, Sean has a Master of Arts in classics and teaches Greek and Latin part-time at the college level.
- Ryan McVay/Photodisc/Getty Images