What it does: Calculate the internal rate of return on an investment based on a series of periodic cash flows.
Syntax=IRR(cashflow_amounts, [rate_guess])cashflow_amounts – An array or range of cells containing the incoming and outgoing payments corresponding to the investment.
Make sure to specify at least one positive amount (income) and one negative amount (payment). rate_guess [Optional]- It estimates the internal rate of return. It is set to 0.1 or 10% by default.
Sample Usage=IRR(B2:B7)
//This returns the IRR value at the default rate of 0.1 based on the investment values in the range B2:B7.
Learn how to use the IRR function in Google Sheets.
What is IRR Function in Google Sheets?
The Internal Rate of Return or IRR function calculates the rate of return on a series of periodic investments. The IRR function is an extremely useful tool in financial analysis – among the most common and popularly used calculations.
The IRR function in Google Sheets proves to be pretty useful in situations where you want to calculate the profitability of any business project based on a series of cash flows. This formula allows you to devise the best course of action for your project beforehand and saves you a lot of manual effort in knowing your iRR.
Our guide below focuses on setting up the IRR formula in Google Sheets, but you may first want to be clear on the meaning and utility of Internal Rate of Return (IRR) also.
Syntax
IRR(cashflow_amounts, [rate_guess])
- cashflow_amounts (required) – This refers to an array or range of cells containing the incoming and outgoing payments corresponding to the investment. Make sure to specify at least one positive amount (income) and one negative amount (payment).
- rate_guess (optional)– It estimates the internal rate of return. It is set to 0.1 or 10% by default.
Using IRR Function
Step 1: Enter the data
The spreadsheet below shows the initial amount invested in a business project, as well as the related cash flows throughout the previous four years. Outgoing cash flows or payments have been represented by negative values, while incoming cash flows or income have been represented using positive values. Let’s suppose you want to estimate the profitability of this project, you can simply use the IRR function to calculate the same for you. Let’s see how in the following sections.
Step 2: Select a cell
Now let’s select a cell to display the IRR value. Start typing ‘=’ followed by the name of the function ‘IRR’ in the cell. As you type, Google Sheets will automatically suggest the required function. Choose this function.
Step 3: Use the IRR formula
Finally, to calculate the IRR value, type in the following formula:
=IRR(B3:B7)
As you can see in the figure below an IRR value of 30% is displayed.
Why am I getting #NUM! Error?
While calculating the IRR value one can sometimes get the #NUM! Error.
This error occurs due to the following reasons-
- At least one negative and one positive value is not entered.
- The IRR function iterates for at most 20 times to produce a pretty accurate result. In case the function is unable to find an optimal solution even after 20 iterations, it returns a #NUM! Error. In such a situation you can assign a different value to the rate_guess parameter.
Conclusion
In summary, IRR Function in Google Sheets makes it simple to calculate the internal rate of return for investments and is the best way for investors, financial analysts, accountants or even retail investors to track their portfolio performance.
If like us Google Sheets is where you do your business analysis, you may want to master it using our Definitive Guide for Google Sheets, created by an expert team to help solve common (and some not-so-common!) doubts.
Related popular articles