How to Use IRR Function in Google Sheets | Easy Guide

How to Use IRR Function in Google Sheets | Easy Guide
Reading Time: 3 minutes
Using the IRR Function in Google Sheets

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. 

IRR Formula | Use IRR Function in Google Sheets to calculate internal rate of return
IRR Formula | Calculate automatically using IRR Function In Google Sheets

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. 

Amount invested in a business project, over the previous four years
Fig 1: Data to calculate IRR using IRR function in Google Sheets

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. 

Typing in the IRR Function formula in the cell
Fig 2: Using IRR Function in Google Sheets

Step 3: Use the IRR formula 

Finally, to calculate the IRR value, type in the following formula:

=IRR(B3:B7)

Calculating the internal rate of return by applying the IRR Function
Fig 3: Type in the IRR formula in Google Sheets

As you can see in the figure below an IRR value of 30% is displayed. 

Internal Rate of Return for the corresponding investment is displayed
Fig 4: IRR Value is displayed on using IRR formula in Google Sheets

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

Calculate Simple Moving Average In Google Sheets

Create CandleStick Charts in Google Sheets