- The fastest way to use IRR function in Google Sheets: Calculating IRR for an Investment: Write the IRR function in a cell. The formula in cell D2 is =IRR(B2:B11, C2), where B2:B11 represents the cash flow values and C2 is the rate guess > Press Enter to calculate the IRR.
- Another good way to use IRR function in Google Sheets: Calculating IRR for a Business Cash Flow: Input the sample data, with months and corresponding cash flow values for a business > Apply the IRR function to the data > Pass the arguments to the function > Press Enter to calculate the IRR.

In this article, we will learn how to use **IRR** function in google sheets. **IRR** is an underrated function that is not very common but it’s very useful. **IRR** is used to calculate the internal rate of return. It stands for **I**nternal **R**ate of **R**eturn. We can calculate the rate of return for our investment or business. It can tell us the most likely results. The **IRR** function takes two arguments, the first one is the cash flow, it is mostly a range reference in which we have our cash flow; can be incoming cash or outgoing cash.

This means at least one value should be negative to show to cash outgoing. The second argument is the guess value, it’s an optional parameter and mostly skipped. the default value of [guess value] is 0.1 or 10%, but we mostly skip this value and use only 1 mandatory argument to get the most likely return percentage on our cash flow data. we will learn how to use this with real-world scenarios.

Table of Contents

## Use Case of IRR function in Google Sheets

In this section, we will see why we need to learn how to use **IRR** function in google sheets. Now **IRR** function is a unique function that works very accurately on most of the data sets. The possible error you may encounter while using the **IRR** function is a **#NUM!** error, where the error message is “In **IRR** evaluation, the value array must include positive and negative values”. This simply means that our data has a problem, we must have cash outgoing (negative values), and cash incoming (positive values) in our data set to use the function.

The function returns a percentage and simply tells us the internal return rate, we can easily evaluate our cash flow and manipulate our investment based on the result **IRR** is showing. This is a very useful function, we have some more similar functions that can be used to calculate the return rate, but have some limitations of course. So, therefore we use the **IRR** function for calculating the rate of return, I hope you have got an idea and now you have something in your mind to learn how to **IRR** function in google sheets.

## How to use IRR Function in Google Sheets

In this section, we will see step-by-step procedures to learn the **IRR** function in google sheets, and we will see and implement various examples to learn it.

### IRR Function in Google Sheets – Syntax

Here, firstly let’s see the syntax of the **IRR** function, to better understand it before we implement it.

**Syntax of IRR**

**=IRR (cashflow_values, [rate_guess])**

Here,

**cashflow_values**: it is an array or reference to a range of cells values containing cash flow values corresponding to the investment year month or any period (does not include in the function)**rate_guess**: It is an estimate for expected**IRR**, It is an optional parameter you can use a direct value in number, or you can pass a cell reference for it. This value is considered as a nearing likely value for the expected result. The default value is 0.1 or 10%

Since we have now got an understanding of the syntax let’s implement a basic example of the **IRR** function

### IRR Function in Google Sheets – Calculate IRR for an investment

This is the most common and probably the only use case of the **IRR** function, we can have various data sets, let’s simply see an example to understand how to use the **IRR** function in google sheets to calculate the Internal rate of return on investment we have made.

For this example, we need to have some sample data to perform the function

I have investment dummy data where I have some years and their corresponding cash flow values which I earned or invested written in an array column.

**Step 1**

Sample data

**Note**: Your cash flow values must have negative and positive numbers

**Step 2**

Write the **IRR** function in any cell

**Step 3**

Pass the first argument (cash flow array)

**Step 4**

Pass the second argument (if you have any guess value), or you can omit this parameter.

**Step 5**

Press Enter key and you have got the **IRR** value

This is how to use the **IRR** function in google sheets with a very basic application of it.

### IRR Function in Google Sheets – Calculate IRR for a Business Cash Flow

In this section, we will learn how to use **IRR** function in google sheets to find **IRR** for a business cash flow. In this example, we will have multiple negative and positive values, which will depict a business owner’s expenses and earnings in business over time.

In the first column, A has months, and corresponding to months, I have cash outgoing and incoming in a business

**Step 1**

Sample data

**Step 2**

Write the **IRR** function

**Step 3**

Pass the arguments

**Step 4**

Press Enter and you have got the result

By making some changes in our dataset, we can get the value of **IRR** more than 100, and also it can go below 1 which means a negative **IRR**. Both of these values are valid for **IRR** functions and also in the real world.

### IRR Function in Google Sheets – Calculate CAGR (**C**ompound **A**nnual **G**rowth **R**ate)

In this section, we will learn how to use the **IRR** function to find **CAGR**, CAGR stands for **C**ompound **A**nnual **G**rowth **R**ate.

The Compound annual growth rate is the rate of accumulated annual growth of a business or investment for the geometric progression ratio that provides a rate of return over the period mostly annual growth.

The **IRR** function is not originally designed for calculating compound annual growth rates, but we can make some changes in our data to find the value of **CAGR**.

**Note**: The starting value of the cash flow will be a negative number. This means that the first investment is made and it is a negative value because the cash is outgoing. The last value in the array will be a positive number, this means that in the last we have got the revenue. And, also all the intermediate values will be zero, which means no incoming, and no outgoing.

**Step 1**

The sample data

**Step 2**

Now, write the **IRR** function normally

**Step 3**

Pass the arguments

**Step 4**

Press Enter key and you have got the value of **CAGR**

**Note**: To validate the value calculated by the **IRR** function, we can use the original formula of **CAGR**, and let’s see if we get the same result.

We need to do some reverse engineering to verify whether the resulting value is correct or not.

The general formula to calculate **CAGR** is

=(End Value/Start Value)^(1/Periods) -1

Let’s apply this formula in google sheets to our original data.

**Step 5**

Write original **CAGR** formula

**Step 6**

First, divide the final value by the first value

**Step 7**

Set the power value equal to **1/period-1**

**Step 8**

Then again outside the bracket, add **-1**

**Step 9**

The overall formula is:

**Step 10**

Press Enter key

See, this is the value we got from the original **CAGR** formula, and it’s equal to the value we got from the **IRR** function.

This is hence proved, that **IRR** can also be used to find the **CAGR** value if the data of the cash flow is made like above.

I hope you have learned how to use the **IRR** function in Google sheets for various purposes.

## Download/Copy Google Sheets Workbook

**Notes**

- The value of
**IRR**can be negative as well - The value of
**IRR**can be greater than 100 as well - Mostly in 90% of cases, we ignore the second argument
- If the first value is positive, then there are higher chances of error, because the
**IRR**function looks for the first investment value which is going to be a negative value - If there are no negative values in the cash flow array, then you will get an
**#NUM!**error **CAGR’s**original formula is used in investment and business calculations. This is not available in google sheets originally, that’s why we used it like a direct formula.**CAGR**is calculated with both negative value and positive value kept as a positive value. Its the formula requirement

## Frequently Asked Questions

### How to use IRR Function in google sheets?

To calculate **IRR** (internal rate of return), we have a built-in function in google sheets named “**IRR**“, which returns a percentage value of the return rate on your investment. The important thing when calculating the **IRR** is to have some negative and positive values in your cash flow array. It will work accurately when the first big value is a negative number.

### Can the Google Finance Function in Google Sheets be Used to Calculate IRR?

Yes, the Google Finance function in Google Sheets can be used to calculate IRR. By using the IRR function within the google sheets finance function, users can easily determine the internal rate of return for a series of cash flows. This powerful tool simplifies financial analysis and allows for efficient decision-making.

### What is CAGR in google sheets?

CAGR is a business and investment term which is not available in google sheets as an internal function. Compound Annual Growth Rate is a calculation of the annual growth rate for a business of investment which can be calculated by using a mathematical formula. We used CAGR as an example to implement the **IRR** function. There is no function available for CAGR in google sheets.

**Conclusion**

Wrapping up how to use **IRR** function in google sheets. We have learned what is **IRR** function in google sheets. We have discussed the syntax and seen the basic usage of **IRR** in google sheets. We further learned about some practical examples by implementing the **IRR** function on hypothetical data of investment, and of a business cashflow, and got the rate of return using the **IRR** function. Lastly, we discussed another good example of Compound Annual Growth Rate using **IRR**, we changed our data and applied **IRR** to it to find the value of CAGR, and then to verify this value we used the original formula of CAGR to see if the **IRR** function is giving the valid result. So that’s all from how to use **IRR** function in google sheets.

I hope you find this article helpful, and that you have learned something new from it. I will see you soon with another helpful tutorial. Till then take care. Keep learning with Office Demy.