XIRR Calculator - Irregular Internal Rate of Return

Written by:
PK

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/1850000
7/8/1930000
7/8/20-20000
7/8/21130000

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 Calculation in Excel

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:

      

PK

PK started DQYDJ in 2009 to research and discuss finance and investing and help answer financial questions. He's expanded DQYDJ to build visualizations, calculators, and interactive tools.

PK lives in New Hampshire with his wife, kids, and dog.

Don't Quit Your Day Job...

DQYDJ may be compensated by our partners if you make purchases through links. See our disclosures page. As an Amazon Associate we earn from qualifying purchases.
Sign Up For Emails
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram