XIRR Calculator to Calculate IRR of Non-Periodic Cash Flows

XIRR Calculator Sign

This XIRR Calculator uses a trial and error method of determining the rate of return for a non-periodic schedule of investments and withdrawals -- which is similar to the XIRR function used in spreadsheet applications such as Excel™, OpenOffice Calc, GoogleDocs, etc.

Specifically, this calculator begins by assuming the annual rate of return is -100%, and then increases the rate of return by 1% until the combined future value of the cash flows is greater than zero.

From there the calculator keeps subtracting .0001% from the "guess" until the combined cash flows are within +/- $0.50 of zero.

Plus, unlike other online XIRR calculators, this calculator will even display a printable chart showing the future value of each cash flow -- which will help confirm or deny the validity of the calculated internal rate of return.

Finally, if you're not sure what is meant by "Internal Rate of Return" (IRR), please visit the "Learn" section on the IRR Calculator for an explanation.

Read more ...

Also on this page:

XIRR Calculator

Calculate internal rate of return for cash flows occurring on an irregular basis.

Learn More

Selected Data Record:

A Data Record is a set of calculator entries that are stored in your web browser's Local Storage. If a Data Record is currently selected in the "Data" tab, this line will list the name you gave to that data record. If no data record is selected, or you have no entries stored for this calculator, the line will display "None".

DataData recordData recordSelected data record: None
Enter Cash Flows

Enter Cash Flows:

To add a cash flow, Enter/select the date, amount, and direction and then tap the + button (may also appear as "Add" or "Add Cash Flow" depending on the width of your device).

To edit a cash flow, tap the radio button in the Edit column. This will load the cash flow into the form for editing. When you're satisfied with the changes you've made, tap the S button (may also appear as "Save" or "Save Changes" depending on the width of your device).

To delete a cash flow from the list, tap the radio button in the Edit column. This will load the cash flow into the form for deleting. Tap the button (may also appear as "Delete" depending on the width of your device).

To clear the form to create a new cash flow, tap the C button (may also appear as "Clr" or "Clear" depending on the width of your device).

Important! The entered cash flows are saved in your device's random memory (temporary storage), which means that if you surf to another calculator or close this browser window you will need to re-enter your cash flows.

Cash flow date:

Cash flow date:

Select the applicable month and day and enter the 4-digit year of the date the cash flow occurred on. You can enter the cash flows in any order as the calculator will sort the list of cash flows by date, from oldest to newest, and will assume the oldest was the initial investment.

#
Amount:Cash flow amt:Cash flow amount:Cash flow amount:

Cash flow amount:

Enter the amount of the cash flow. Enter as a positive dollar amount, but without the dollar sign and any commas. Use the next line to indicate whether or not the cash flow was an outflow (investment) or an inflow (return).

$
Direction:Direction:Cash flow direction:Cash flow direction:

Cash flow direction:

Select the cash flow direction. Select "Out" if the cash flow was an investment or expense. Select "In" if the cash flow was a receipt, as in a dividend or withdrawal.

Profit (-Loss):Profit (-Loss):Profit (-Loss):Profit (-Loss):

Profit (-Loss):

The is the sum of all withdrawals and the ending value minus the sum of all invested amounts.

# flows:# cash flows:Number of cash flows:Number of cash flows:

Number of cash flows:

This is the total number of entered cash flows that were included in the calculations.

# days:# of days:Number of days:Number of days:

Number of days:

This is the number of days between the date of the beginning value and the date of the ending value.

Balance:Calculated balance:Calculated balance:Calculated balance:

Calculated balance:

The closer this result is to zero, the more accurate the Internal Rate of Return result.

Iterations:# iterations:Number of iterations:Number of iterations:

Number of iterations:

This is the number of secondary iterations (tries) it took to find a rate of return that would support the entered schedule of investments and withdrawals. If this result is equal to 100,000 it means the XIRR Calculator reached its limit of tries before finding a solution within +/- $0.05 of a zero balance.

IRR:IRR:Internal Rate of Return:Internal Rate of Return:

Internal Rate of Return:

This is the estimated average annual rate of return of the entered cash flows.

If you would like to save the current entries to the secure online database, tap or click on the Data tab, select "New Data Record", give the data record a name, then tap or click the Save button. To save changes to previously saved entries, simply tap the Save button. Please select and "Clear" any data records you no longer need.

Help and Tools

Learn

What XIRR is and what makes it different from IRR.

What is XIRR?

XIRR stands for Extended Internal Rate of Return, which is a financial algorithm used to measure the rate of return on investments made into an investment at irregular intervals.

The XIRR function, which is common to most spreadsheet software programs, uses an iterative process to keep trying slightly higher and lower rates until it finds a rate that would yield the final value of the investment.

Since the number of iterations the XIRR function can use to find the rate of return is finite, the function displays an error if it reaches its maximum number of tries before finding a rate match. This is why the function also allows you to enter a guess, increasing the chance the function will find a match before it reaches its iteration limit.

XIRR vs IRR

The difference between IRR and XIRR is IRR measures rate of return on uneven cash flows made at regular intervals, whereas XIRR measures the return on even or uneven cash flows made at irregular intervals.

For example, if you are adding a varying amount to an investment on an every-month basis, you would use IRR to calculate the rate of return.

On the other hand, if you are adding a fixed or varying amount to an investment at odd intervals (May of this year, June of next year, etc.), you would use XIRR to calculate the rate of return.

How to calculate XIRR in Excel

To use the XIRR Function in Excel, you enter the dates of the cash flows in one column (ordered from oldest to latest), and the amounts in a second column ("-" indicates you deposited funds into the investment). Next, you insert the XIRR function (XIRR(values, dates, guess)) and provide the function with the value and date ranges, along with your best guess, like this:

AB
11/1/18-1000
21/1/19-1000
36/15/19-500
412/31/192700
5XIRR0.060180287
Formula in cell B5 =XIRR(B1:B4,A1:A4,0.08)

Note that it is best to use the date function to enter dates, rather than entering dates as text.

Adjust Calculator Width:

Move the slider to left and right to adjust the calculator width. Note that the Help and Tools panel will be hidden when the calculator is too wide to fit both on the screen. Moving the slider to the left will bring the instructions and tools panel back into view.

Also note that some calculators will reformat to accommodate the screen size as you make the calculator wider or narrower. If the calculator is narrow, columns of entry rows will be converted to a vertical entry form, whereas a wider calculator will display columns of entry rows, and the entry fields will be smaller in size ... since they will not need to be "thumb friendly".

Show/Hide Popup Keypads:

Select Show or Hide to show or hide the popup keypad icons located next to numeric entry fields. These are generally only needed for mobile devices that don't have decimal points in their numeric keypads. So if you are on a desktop, you may find the calculator to be more user-friendly and less cluttered without them.

Stick/Unstick Tools:

Select Stick or Unstick to stick or unstick the help and tools panel. Selecting "Stick" will keep the panel in view while scrolling the calculator vertically. If you find that annoying, select "Unstick" to keep the panel in a stationary position.

If the tools panel becomes "Unstuck" on its own, try clicking "Unstick" and then "Stick" to re-stick the panel.