The IRR() function in Google Sheets is used to find the internal rate of return on the investments.
The term internal rate of return is used in finance for the determination of the growth of the investment made. In this article, we will learn how to calculate the internal rate of return by using the IRR() function in Google Sheets.
How to use the IRR function in Google Sheets?
In Financial analysis, IRR plays a vital role by providing standard metrics for evaluating investment opportunities. Using the IRR() function in Google Sheets, financial analysts can easily demonstrate the profitability of various investment projects.
First, we will take a look at the syntax used for the IRR function in Google Sheets.
Syntax Used for IRR() Function in Google Sheets
We use the following syntax for the IRR() function in Google Sheets:
IRR(cashflow_amounts, [rate_guess])Where
cashflow_amounts is the argument used regarding the range of cells that contain the incoming and outgoing cash details. This must include at least one positive and one negative value.
rate_guess is an optional argument, the estimated calculation of the internal rate of interest, and it is 0.1 or 10% by default.
Example: Use of IRR() Function in Google Sheets
Consider the following example of a periodic cashflow over an investment, on which we will learn how to use IRR() function step by step:
Step 1: Click on an empty cell, then type =IRR and click the IRR() function from the recommendations or press Tab to accept:
Step 2: Select the data range as a cashflow argument. In our example, the cash flow range is from cell B2 to Cell B7. Click on the first cell in the cashflow data range and move the cursor down, up to the last cell in the data range:
Step 3: The second is the rate_guess argument, which is optional, and it is 0.1 or 10% by default. Type the custom value, otherwise press enter to go with the default. The final result will appear on the cell in which you apply the IRR() function, showing a percentage value as the internal rate of return on the investment for the given periodic cashflow:
Pro Tips:
Before applying the IRR() function to find the internal rate of return over a cashflow, we must keep in mind the following thing.
- Our cashflow data must be in periodic form.
- There must be a negative value in the cash flow, otherwise, you will get a #NUM! Error.
- Any logical value or text in the cashflow data will be ignored by the IRR() function.
- It is useless to apply the IRR() function when your expenses are greater than the income generated. This will result in negative cashflow and hence the negative value of the internal rate of return.
- It is also useless to apply the IRR() function if you have different types of investment with different rates.
Functions Similar to IRR() Function in Google Sheets
Below are similar functions that can be used to find the internal rate of return in Google Sheets:
| Function | Description |
|---|---|
| NPV | The NPV() function in Google Sheets calculates the net present value over a series of cashflow in an investment. |
| XIRR | The XIRR() function is used to find the internal rate of return over an irregular or non-periodic cashflow in an investment. |
| MIRR | The MIRR() function is used to find the modified internal rate of return in Google Sheets for the principal amount and the compound rate of return for the reinvested amount combined with the interest gained on the principal amount. |
Conclusion
The IRR() function in Google Sheets is used to find the internal rate of return over a periodic cashflow for an investment. Using the IRR() function in Google Sheets, financial analysts can easily demonstrate the profitability of various investment projects. Before applying the IRR() function, your cashflow data must be in periodic form, and it must contain at least one negative and one positive entry. It is useless to apply the IRR() function when your expenses are greater than the income generated.