What is XIRR Function in Google Sheets?
The XIRR function determines the value of an investment or project feasibility when cash flows are not periodic. It helps you accurately model cash flows over variable time intervals, which is useful in practical cases and helps us understand the rate of return on an investment.
XIRR is used in financial modeling to evaluate investments. It can measure the profitability of investments with varying cash flows, and specific dates can be assigned to the cash flow. In the example below, we have an investment with the following cash flows. We use the XIRR function, as shown below, to calculate the internal rate of return for this investment.
We get the value of 17.30%, indicating that the investment has an internal rate of return of around 17% and is profitable.
Key Takeaways
- The XIRR is a Google Sheets function used for calculating the internal rate of return for cash flows that are not scheduled at regular intervals.
- It takes into account both the timing of the payments and the cash flows.
- The syntax of the XIRR function is:
=XIRR(values, dates, [guess])
values: the column containing the cash flow amounts
dates: the column containing the dates for each cash flow
guess: (optional) the internal rate of return. If omitted, the default guess of 0.1 (10%).
- It helps you accurately model cash flows over variable time intervals which helps us understand the rate of return on an investment.
Syntax
Before we dive deep into the topic, let us look at the XIRR formula in Google Sheets. The Google Sheets formula for XIRR is:
=XIRR(cashflow_amounts, dates,[rate_guess])
Arguments
- cashflow_amounts (mandatory) – This is an array of values representing the series of cash flows. Usually, a range of cells is given here.
- Dates (mandatory) – These dates correspond to the cash flows. The date of the initial investment must be the first entry here. The rest can be in any order.
- [rate_guess] (optional) –This is a guess or estimate of the IRR. If not provided, Excel takes the default value of 10%.
How to Use XIRR Function in Google Sheets?
To understand how to use XIRR to calculate the rate of returns on an investment, we must know how to enter the function and its parameters in Google Sheets. The XIRR in Google Sheets can be entered in two ways.
- Enter XIRR manually
- Access from the Google Menu bar
Entering XIRR Manually
Let us look at an example of entering XIRR in Google Sheets. Suppose we have an initial investment plan of $2000. We expect to get cash flows at the end of nine months, one and a half years, two years and three years.
Step 1: Arrange the data in a table, as shown below. The cash flow dates are in Column A, and the cash details are in Column B.
Step 2: Now, let us enter the XIRR formula. First, we enter =XIRR( in an empty cell where you want to find the rate of return). Then, we enter all the arguments.
First, we enter the cash flow amount, followed by the dates. Hence, we enter the ranges B2:B6 and A2:A6 as the first and second arguments. Close the braces.
- B2:B6 are the cash flows.
- A2:A6 are their corresponding dates.
Step 3: Press Enter. You get the XIRR value as follows.
Access From the Google Menu Bar
Choose the cell where you wish to enter the XIRR function.
Go to the “Insert” tab -> “Function” -> “Financial” -> “XIRR.”
Enter the required arguments within the parentheses and press Enter to get the result.
Examples
In this example, let us assume that an investor invested an initial amount of $2500 on January 1, 2022. He has further invested and has received some returns. The investment grew, and finally, he received a return of $4,000 on February 28, 2023. Let us find the internal rate of return for the cash flows given.
Example #1 – Calculating XIRR for a Simple Investment
In this example, let us assume that an investor has invested and initial amount of $2500 on January 1, 2022. He has since invested further and received some returns. The investment grows and finally he received a return of $4,000 on February 28, 2023. Let us find the internal rate of return for the cash flows given.
Step 1: Arrange the data in a table, as shown below.
Step 2: Now, let’s enter the XIRR formula.
=XIRR(B2:B5, A2:A5)
Explanation:
The table’s first minus sign indicates the initial investment of $2500. Subsequently, he again invested $1,700 and received a cash inflow of $1000 and a final inflow of $4000.
Thus, the outflow is represented by the minus sign, and the inflows are written as positive values.
Step 3: Press Enter. The result of the formula, which calculates the internal rate of return for the cash flows, is approximately 16%.
Example #2 – Calculating XIRR for Monthly Cash Flows
The XIRR in Google Sheets only accurately models cash flows over variable time intervals. If one has a set of regular monthly cash flows, there is a way to work around the function. For this, we use the EOMONTH function and the XIRR formula to calculate the internal rate of return for the investment. The EOMONTH function in Google Sheets finds the last day of the month when we input a date. By combining it with XIRR, one can calculate the XIRR for an investment with monthly cash flows.
Step 1: In the sheet, enter the details of the cash flow and their corresponding dates. Thus, the outflow is represented by the minus sign, and the inflows are written as a positive value.
Step 2: Enter the following formula in the cell where you wish to see the result.
=ARRAYFORMULA(XIRR(B1:B5,EOMONTH(A1:A5,0)))
This formula calculates the XIRR value for an investment with monthly cash flows over a one-year period. We use ARRAYFORMULA to apply the formula to the entire range.
Step 2: Press Enter to get the result for the monthly cash flows.
Example #3 – XIRR in XNPV
The XNPV formula in Google Sheets calculates the net present value of a set of cash flows, considering a fixed discount rate and the timing of each cash flow. When used in combination with XIRR, it helps you evaluate potential investments.
Suppose you are looking into the investment opportunity below and want to calculate its net present value and internal rate of return.
Step 1: We have arranged the data in a Google Sheet.
Step 2: We use the XNPV formula for the net present value of the future cash flows. Here, we want to use a 10% annual discount rate
=XNPV(0.1, B2:B6, A2:A6)
Press Enter. A negative NPV indicates that the project is not profitable.
Step 3: We can now find the discount rate to make the net present value zero. For this, we use the XIRR function in combination with XNPV as follows:
=XNPV(XIRR(B2:B6,A2:A6),B2:B6,A2:A6)
Press Enter. You get the XIRR inpercentage that represents the return on investment. You will get the result as shown.
Important Things to Note
- If the cash flow array does not contain at least one positive and one negative value, you get a #NAME error.
- The formula’s default value for the optional parameter “guess” is 0.1 (10%).
- If you compare the simple IRR with XIRR, XIRR is preferred. In contrast, IRR is used for calculating the internal rates of interest for periodic cash flows when a simple calculation is needed.
Frequently Asked Questions (FAQs)
You may get an error with the XIRR function for the following reasons.
#VALUE! error
1. When any of the parameters are non-numeric.
2. If any of the date parameters are not in a valid date format.
#NUM! error
1. Value and date ranges are of different length.
2. If the cash flow array does not contain a minimum of one positive and one negative value.
3. If the subsequent dates are earlier than the given first date.
To calculate the XIRR, your table should contain these two basic columns.
1. The cash flow amount which is the amount you either receive or pay out on a particular date.
2. The cash flow date: the date that the cash flow will occur
IRR and XIRR are used in financial modeling to evaluate investments.
The IRR is used to calculate cash flows at regular intervals. It provides an annualized rate of return. Since it can be used to compare the profitability of investments at standard time intervals with regular cash flow, which may not always be the case, it is considered less accurate.
XIRR can measure the profitability of investments with varying cash flows. Specific dates can be assigned to the cash flow. This scenario is realistic and makes it more accurate.
Download Template
This article must be helpful to understand the XIRR Function In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is XIRR Function In Google Sheets. We learn to use it to find the value of an investment with non-periodic cash flows. You can learn more from the following articles –
Leave a Reply