Do you have an investment that has the following characteristics?
- Fluctuating interest rate
- Irregular investment intervals
- Irregular withdrawal intervals
If you do, my next question is, do you know how to determine the average annual return on your investment so you can compare the rate of return to other investments?
If you answered, "yes, by using the XIRR Function in a spreadsheet", then to you I say congratulations!
On the other hand, if the mere thought of trying to determine the rate of return on this type of an investment gives you an instant headache, then you're going to love the calculator on this page.
The XIRR Calculator on this page makes determining the rate of return a snap.
Simply enter the date and amount of the initial investment, then the dates and amounts of all subsequent investments and withdrawals, then click the "Calculate XIRR" button.
The 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 is within +/- $0.50 of zero.
Each time the calculator adjusts the guess and recalculates the combined future values of all cash flows, this is referred to as one iteration or one try. Believe it or not, the calculator will perform up to 100,000 of these iterations before it stops trying to find an acceptable solution.
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 IRR Calculator for an explanation.
With that, let's use the XIRR Calculator to calculate the average annual rate of return for cash flows occurring at irregular intervals.
Check Out My Other Super
To Help You To
Calculate Your ...
Month column: Enter the month of the year (1-12) the cash flow took place (no leading zero required for single digits). The Month field of the first line, plus at least one other line, must be completed.
Day column: Enter the day of the month (1-31) the cash flow took place (no leading zero required for single digits). The Day field of the first line, plus at least one other line, must be filled in.
Year column: Enter the 4-digit year (YYYY) the cash flow took place. The Year field of the first line, plus at least one other line, must be filled in.
Investments column: Enter the starting investment amount on the first line and then on any subsequent lines where additional amounts were invested (enter as positive values). Note that numbers entered in this column are usually entered as negative values when using an XIRR spreadsheet function, however this calculator converts the values to negative for you.
Withdrawals & End Value column: For cash flow dates where withdrawals occurred, enter the amounts in this column. Once you have entered all cash flows, use the next available line to enter the ending value (the amount you withdrew -- or could withdraw -- at the end of the investment period) along with the corresponding month, day, and year. You must have at least one value in this column for the IRR calculation to begin.
Profit (-Loss): The is the sum of all withdrawals and the ending value minus the sum of all invested amounts.
Number of cash flows: This is the total number of entered cash flows that were included in the calculations. If this number is less than the actual, check to see if all entered cash flows have the month, day, and year filled in, along with a value in one of the two right-hand columns.
Number of days: This is the number of days between the date of the beginning value and the date of the ending value.
Calculated balance: The closer this result is to zero, the more accurate the Internal Rate of Return result.
Number of iterations: This is the number of secondary iterations (tries) it took to find a rate of return that would support the entered cash flow schedule. If this result is equal to 100,000 it means the XIRR Calculator reached its limit of tries before finding a solution within +/- $0.50 of a zero balance.
Internal Rate of Return: This is the estimated average annual rate of return of the entered cash flows.