On this page is an Irregular Internal Rate of Return calculator, usually known as an XIRR calculator. Enter a series of dates where cash flows move in or out of an investment plus the amounts, and the tool will tell you the annualized rate of return.
In the XIRR tool, enter a positive number when you draw money from an investment, and a negative number when you put money into the investment.
XIRR Calculator
What is the Irregular Internal Rate of Return or XIRR?
XIRR, or the Irregular Internal Rate of Return, is the discount rate where the net present value of all cash flows in an investment equal to zero. It shares that with the IRR, although the XIRR is more complicated to calculate because it assumes cash flows are irregular – that is, there isn't a fixed time of month or the year where you can expect to deposit or withdraw money.
XIRR and IRR are both useful real world tools for evaluating past investments, or, when modeling, comparing future investments. However, XIRR usually better matches real life – you can't always expect cash flows in a conveniently scheduled way.
Calculating XIRR in Microsoft Excel or Another Spreadsheet Program
To match the scenario presented by default in the tool, create two columns, Period and Amount. Under those two, paste the following values (you can paste the dates the tool gives you, this is an example):
| 7/8/16 | -100000 |
| 7/8/18 | 50000 |
| 7/8/19 | 30000 |
| 7/8/20 | -20000 |
| 7/8/21 | 130000 |
Finally, underneath the "Amount" column, type =XIRR(, then drag your cursor over all of the values in the "Amount" column. Enter the comma if it doesn't move you along, then drag your cursor over all of the dates.
When you hit return, you should match the XIRR of 18.21%.
XIRR Calculations and More
While XIRR and IRR are equivalent – you could match XIRR with IRR if you add the missing days with "0" for cash flows – XIRR usually models the real world better. When you are looking back in time, you can enter the actual date you received or deposited cash, finding an equivalent net present value.
Have fun with it? Here are some other tools you'll enjoy:
