Arrow left
Back to product guides
Google Sheets Formulas

IRR Function in Google Sheets: Explained

In this article, you will learn how to use the IRR formula in Google Sheets.

What is the IRR function in Google Sheets?

The IRR function in Google Sheets is a financial function that calculates the internal rate of return for a series of cash flows. The internal rate of return is the discount rate at which the present value of a series of future cash flows equals the initial investment. In other words, it is the rate at which the net present value (NPV) of a series of cash flows equals zero. 

How to use the IRR function in Goole Sheets

To use the IRR function in Google Sheets, you need to provide a range of cells that contain the cash flows and an initial guess for the internal rate of return. The function will then iterate through different values for the internal rate of return until it finds a value that makes the NPV of the cash flows equal to zero.

 The syntax for the IRR function is as follows:

=IRR(values, [guess])

values is a range of cells that contain the cash flows for which you want to calculate the internal rate of return.

[guess] is an optional argument that specifies an initial guess for the internal rate of return. If this argument is not provided, Google Sheets will use a default value of 0.1.

Here is an example of how to use the IRR function in Google Sheets:

=IRR(D8:H8, 0.1)

In this example, the IRR function is used to calculate the internal rate of return for a series of cash flows in cells D8 through H8, with an initial guess for the internal rate of return of 0.1, which doesn’t affect the result in this case. The function returns the internal rate of return that makes the NPV of the cash flows equal to zero. See the picture below to understand the relationship between the IRR and NPV. When you calculate the value of future cash flows (D8:H8) with the discount rate of 17.80%, their present value is $0, as shown in the screenshot.

The relationship between the IRR and NPV formulas in Google Sheets

It is important to note that the IRR function in Google Sheets may sometimes return a unique solution, and it can sometimes return an error if it is unable to find a solution. In these cases, you may need to try using a different initial guess for the internal rate of return, or you may need to use another method for calculating the internal rate of return.

What is the difference between the IRR and XIRR functions in Google Sheets?

The IRR and XIRR functions in Google Sheets are used to calculate the internal rate of return for a series of cash flows. However, there is one key difference between these two functions: the IRR function assumes that the cash flows occur at regular intervals, while the XIRR function allows you to specify the dates on which the cash flows occur.

Analyze your live financial data in a snap

Are you learning this tip to visualize financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. You can access more than fifty financial templates here, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711