If you are trying to calculate the internal rate of return on an investment that has irregularly spaced cash flows, the XIRR function is exactly what you need.
This kind of calculation helps you determine the profitability of an investment when the cash flows are not regularly scheduled.
In this tutorial, I will show you how to use the XIRR function in Google Sheets.
The XIRR function is used to calculate the internal rate of a return on an investment that irregularly spaced cash flows.
The syntax of this function is:
XIRR(cashflow_amounts, cashflow_dates, [rate_guess])
- cashflow_amounts – an array or range that contains the income or payments of the investment. The range used for this argument must contain at least one negative and one positive number
- cashflow_dates – an array or range that contains the dates that the cashflow_amounts were paid
- rate_guess – this is an optional argument that will be set to .1 by default. This is an estimate of what the internal rate of return will be
Here are some important notes about using the XIRR function:
- The cashflow_dates should be at irregular intervals. This function only works for irregularly spaced cash flows
- For the cashflow_amounts argument, negative numbers indicate payments on the loan/investment, whereas positive numbers represent the income of the investment
Using the XIRR Function
Now let’s take a look at how to actually use this function in a spreadsheet.
In this example, I already have my required data entered into my sheet. I have a column for cash flows and a column representing the dates the amounts were paid:
Once you have that entered into your spreadsheet, you can begin setting up your formula.
Here are the steps:
1. First select the cell where you want the function to calculate and type the equals (=) sign on your keyboard. Type XIRR and press Tab to begin entering the formula. The first argument is the range that contains the cashflow_amounts. In my example this is A2:A7
2. The next argument of the function is the cashflow_dates. In my example, this is in cell range B2:B7
3. Add a closing parenthesis and press Enter and your formula will calculate to show you the internal rate of return
The XIRR function is a very efficient way to calculate the internal rate of return when the cashflows are irregular.
In this example, I have used cell references for the arguments of the function, however, you can also place the values into the function directly.
I find it much easier to use cell references though, so I would highly recommend doing it the way shown here.