The IRR function in Google Sheets is a crucial tool for investors and financial analysts. It enables users to assess the profitability of an investment by calculating the internal rate of return based on a series of cash flows occurring at regular intervals. This function is essential for making informed financial decisions and evaluations.

Syntax

IRR(cash_flow, [guess])

• cash_flow: An array or range of values representing cash inflows and outflows. Cash outflows (investments) should be represented as negative numbers, while cash inflows (returns) are represented as positive numbers.
• guess: An optional parameter that represents an estimate of what the internal rate of return should be. If omitted, Google Sheets assumes a default value of 0.1 (10%).

Example #1

=IRR(A1:A5)

This function calculates the IRR from cash flows listed in cells A1 through A5. For example, if A1 is -5000, A2 is 1200, A3 is 1900, A4 is 3000, and A5 is 4000, the IRR would yield approximately 14.87%.

Example #2

=IRR(A1:A5, 0.05)

This function similarly evaluates the IRR of cash flows in the range A1 to A5 but starts with a guess value of 5%. This assists Google Sheets in finding the correct rate if the cash flow series is complex or irregular, yielding about 14.45%.

Example #3

=IRR(A1:A5)

Using IRR on a set of cash flows where A1 is -1000, A2 is 450, A3 is 460, A4 is 470, and A5 is 480 will result in an IRR of approximately 4.86%.

Error handling

• NUM! This error occurs if the function cannot find a result for the internal rate of return. It may happen if the cash flow values do not produce a valid IRR.
• VALUE! This error indicates that the supplied cash flow range is not valid, possibly due to non-numeric values within the range.
• DIV/0! This occurs if all cash flows are zero or if the function is unable to compute the rate, which means no solutions can be derived from the provided values.

Conclusion

In summary, the IRR function in Google Sheets is an indispensable tool for evaluating the financial returns of investments. By understanding its syntax and applying it correctly, users can make informed decisions based on the calculated internal rate of return, ultimately leading to more strategic investment choices.