- 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 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.
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])
- 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.
Note: Your cash flow values must have negative and positive numbers
Write the IRR function in any cell
Pass the first argument (cash flow array)
Pass the second argument (if you have any guess value), or you can omit this parameter.
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
Write the IRR function
Pass the arguments
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.
The sample data
Now, write the IRR function normally
Pass the arguments
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.
Write original CAGR formula
First, divide the final value by the first value
Set the power value equal to 1/period-1
Then again outside the bracket, add -1
The overall formula is:
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
- 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.
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.