How to use IRR Function in Google Sheets [User Guide]

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 Internal Rate of Return. 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.

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,

1. 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)
2. 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 (Compound Annual Growth Rate)

In this section, we will learn how to use the IRR function to find CAGR, CAGR stands for Compound Annual Growth Rate.

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

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.