It’s hard to talk about investments and returns without visiting the topic of Internal Rate of Return, or IRR. Manually computing the IRR for a series of cash flows involves trial and error. You have to make multiple guesses till you reach an optimum result. This can be both complicated and time-consuming.
However, it’s quite quick and easy to compute the IRR in Google Sheets. In this tutorial we will show you how to use the IRR function to compute the Internal Rate of Return for a given series of cash flows.
What is IRR?
As mentioned before, IRR is an abbreviation for Internal Rate of Return. It gives the rate of return on a series of investments.This means that if you have a series of cash flows relating to a given project and you want to decide if the investment is going to be profitable then IRR can be a helpful indicator.
In this way the IRR function helps in financial analysis problems, giving us insights into investment plans, so we can quickly and easily choose the best course of action.
How to Calculate IRR in Google Sheets?
Calculation of IRR involves computing the interest rate corresponding to a zero Net Present Value (NPV). So to find out the IRR for a given investment, you need to guess different rates until you reach one that gives you a zero Net Present Value.
This trial and error requires a lot of time and patience, which can prove to be quite inefficient when done by a human.
However, with the IRR formula in Google Sheets, these computations take place within seconds. Let us see how to use the IRR function to compute the Internal Rate of Return for the following series of cash flows:
Notice that the dates for individual transactions are periodic.
Moreover, we can see that the cash flow for the first month is a negative number. Negative values represent money going out, or payments made, while positive values represent money coming in, or income generated.
Note: If your cash flow values are formatted in the ‘Financial’ or ‘Accounting’ format, then money going out is represented in brackets, rather than as a negative number.
Syntax for the IRR Function
The syntax for the IRR function in Google Sheets is as follows:
=IRR (cashflow_values, [rate_guess])
- cashflow_values is an array or reference to a range of cells containing cash flow corresponding to the investment
- rate_guess is an estimate for expected IRR. It is a value that you guess is close to the actual IRR. This parameter is optional.
Note: The default value for the rate_guess parameter is 0.1 or 10%.
What the IRR function will do in this case is use an iterative method to compute the optimum IRR value, starting with the guess value. It will keep iterating and making new guesses, all the while adjusting the IRR value till it gets an accurate result within the range of 0.00001%.
Finally, the function will return the Internal Rate of Returns corresponding to the given cash flow amounts (both negative and positive). The IRR returned will be a percentage value.
The IRR function iterates up to a maximum of 20 times. If at the end of 20 tries, the results don’t converge, the IRR function returns a #NUM! Error.
In this case, you can go ahead and try again with a different guess value in the second parameter of the formula.
Note: If the total income generated is less than the total expenditure, the IRR equation will return a negative value.
Using the IRR Function to the Given Problem
In the above example the IRR can be calculated using the following formula:
Here’s the result we get:
Points to Remember When Using IRR Formula
The IRR function can be really helpful as it takes away the hassle of guessing and adjusting values. However, there are a few points that you need to remember when applying the IRR function in Google Sheets:
- Make sure that your cash flow transactions include at least one negative and one positive value. Otherwise the function will return a #NUM! Error.
- The IRR function assumes that the cash flow values are sequential and periodic in nature.
- Any text or logical value in the IRR function inputs are ignored by the function.
In this tutorial, we covered the IRR function in Google Sheets, what it does, and how it can be used to compute the Internal Rate of Return for a project or investment, based on a series of cash flows. We hope you found our explanations useful and easy to follow.